Answered

Filter Gallery from SharePoint List, Person column and Choice column syntax not working

(0) Share

Report

Posted on by allan_t

310

I've almost got this gallery filter to work, however I'm struggling with the syntax for a couple column types to avoid delegation issues.

The SharePoint list is called WorkRequest and the column types I want to filter the gallery on are:

Created, type date

Open_Closed, type text

LeadAssigned, type person (multiple permitted)

ServiceAreas, type choice (multiple permitted)

The controls on my Power App screen are called:

DateFrom (date picker)

DateTo (date picker)

cmbStatus (combo box to select Open or Closed status)

cmbServiceArea (combo box to select 1 Service area)

cmbLeadAssigned (combo box to select 1 Lead Assigned)

For the gallery items property, so far I've got the first 2 filters (Created, Open_Closed) to work using this formula:

Filter(WorkRequest, Created>=DateFrom.SelectedDate && Created<=DateTo.SelectedDate && StartsWith(Open_Closed,cmbStatus.Selected.Value))

When I try adding another StartsWith filter criteria for the ServiceArea or LeadAssigned criteria, I'm getting red errors in the syntax, plus I'm getting a delegation warning for the StartsWith function (which I thought was delegable to SharePoint). I don't know what I'm doing wrong.

For example...

Any help would be much appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

WarrenBelz 153,117 Most Valuable Professional on at

Like (1)

Report

Hi @allan_t ,

StartsWith is not Delegable on complex field types such a Choice. You also need to use DisplayName on the Person column references.

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

Was this reply helpful? Yes No
Verified answer

Ami K 15,679 Super User 2024 Season 1 on at

Like (1)

Report
@allan_t - I understand you're trying to avoid delegation using the StartsWith function, but I do not see the purpose of using the StartsWith on multi select field in the first place. It is not delegable with a complex field types, and it would only look at the beginning of the text.

You will need to use non-delegable in or Search function to look anywhere within text strings.

If you can be certain your Date Filter and StartsWith function on the Status text field will return less than 2000 rows, you can use non-delegable functions within that smaller subset of data. Consider revising your expression to:

With( { _data: Filter( WorkRequest, Created >= DateFrom.SelectedDate && Created <= DateTo.SelectedDate, Len(cmbStatus.Selected.Value) = 0 || StartsWith( Open_Closed, cmbStatus.Selected.Value ) ) }, Filter( _data, Len(cmbLeadAssigned.Selected.Email) = 0 || cmbLeadAssigned.Selected.Email exactin Concat( LeadAssigned, ThisRecord.Email & "," ), Len(cmbServiceArea.Selected.Value) = 0 || cmbServiceArea.Selected.Value exactin Concat( ServiceAreas, ThisRecord.Value & "," ) ) )

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

Was this reply helpful? Yes No