Filter on different datasource and Lookup field

[mkAveraHealth]

I have a Gallery that is currently setup with the following Sort/Filter in the Items:
Sort(
Filter(
'Request Assessments',
Status.Value = "Needs Assessment" || Status.Value = "In Progress" || Status.Value = Blank()
),
'Assessor Dept'.Value,
Ascending
)

In that gallery, I have a field called "Assmnt Dt" that does a lookup to another data source to give me an Assessment Date (LookUp('Intake Requests',ThisItem.'Ref ID'=ID).'Assessment Meeting Date').

I would like the gallery to filter out any records that the "Assmnt Dt" field is blank.

Datasource is SharePoint.

[WarrenBelz]

Hi @mkAveraHealth ,

Try this format

With(
   {
      wData:
      Filter(
         'Request Assessments',
         Status.Value = "Needs Assessment" || 
         Status.Value = "In Progress" || 
         Status.Value = Blank()
      )
   },
   Sort(
      Filter(
         AddColumns(
            wData,
            "Department",
            'Assessor Dept'.Value,
            "AssessDate",
            LookUp(
               'Intake Requests',
               'Ref ID' = ID
            ).'Assessment Meeting Date'
         ),
         !IsBlank(AssessDate)
      ),
      Department
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Drrickryp]

@

[Drrickryp]

@mkAveraHealth ,

Try,   

With({_items:AddColumns('Request Assessments',"AMD",
                        LookUp(
                               'Intake Requests',ID = 'Request Assessments'[@'Ref ID'],'Assessment Meeting Date')
      },
  Sort(
      Filter(
          _items, Or(Status.Value = "Needs Assessment",
                  Status.Value = "In Progress",
                  Status.Value = Blank()
                  ) && !IsBlank(AMD)
        ), 'Assessor Dept'.Value,Ascending
  )
)

[mkAveraHealth]

It worked except it now created an error on my AssessmentForm... "Invalid formula. Expected a value compatible with 'DataSource'. Location AssessmentForm.Item. 

I have a variable record setup called Item = varAssessmentItem

[WarrenBelz · Accepted solution]

@mkAveraHealth ,

Not sure who you are responding to here, but on the Item of your Assessment form, you now need

LookUp(
   'Request Assessments',
   ID = YourGalleryName.Selected.ID
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[Drrickryp]

@WarrenBelz 

I'll step out.

[mkAveraHealth]

Perfect!!! Thank you so much!!!!

[mkAveraHealth]

I noticed one other thing is not working...

When I now select an Assessment record it does not bring me to the correct record. I'm assuming it has to do with the "Set (varAssessmentItem, ThisItem)" . 

[WarrenBelz]

Hi @mkAveraHealth ,

Exactly, but it is more on the "other end" - any record you need to display is simply 

LookUp(
   'Request Assessments',
   ID = varAssessmentItem.ID
)

This is a far better structure as it is looking directly at the data source and will update without needing to update the Variable (even if other data changes in the record, the ID does not and any control resetting will simply display the current state of the record).

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps