Answered

Power apps Filter used with StartsWith and logical test issue

(0) Share

Report

Posted on by Erec

I have an issue with the following items statement:

If
(
IsBlank(TextSearchBox1.Text),
SortByColumns(Filter(lsNearMissData, ReportedBy= "Joe Bloggs"),"NearMissDate",If(SortDescending1, Ascending, Descending)
),
SortByColumns(Filter(lsNearMissData,StartsWith(Region.Value, TextSearchBox1.Text) && ReportedBy="Joe Bloggs"
||
StartsWith(ContractNoDeptCode, TextSearchBox1.Text) && ReportedBy="Joe Bloggs"),"NearMissDate",If(SortDescending1,Ascending, Descending))
)

This works when the TextSearchBox1 is empty. However, when filtering it does not return the correct data set, it returns fewer records.

The Region.Value cannot be delegated and the ContractNoDeptCode can be delegated. I have tried this statement in varying combinations and I have also removed the region.value section so the whole statement is valid for delegation and the results are the same. Is there something that I am missing in the construct of the statement?

The recordset being returned will be less than 500.

The data souce is a SharePoint list.

All fields being queried are of text value and the region field is a lookup field.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Verified answer

Eickhel 374 Most Valuable Professional on at

Like (0)

Report
Hey,

I had the same issue regarding a lookup field so my approach to query it was adding a column and also use Collections to avoid the delegation issue. This works ok if you're not querying a huge amount of records.
ClearCollect(NearMissData, Filter(AddColumns(lsNearMissData, "RegionValue", RegionValue.Value ), StartsWith(Region.Value, TextSearchBox1.Text) && ReportedBy="Joe Bloggs"))
I think this might work for you.

You could also keep your logic and simply collect the data in memory to avoid delegation:
ClearCollect(NearMissData, Filter(lsNearMissData, ReportedBy= "Joe Bloggs"))

And simply replace lsNearMissData with NearMissData ( and the ReportedBy filter since it's already being filtered in the collection )

Was this reply helpful? Yes No
Erec 23 on at

Like (0)

Report

Thanks Eickhel,

I have used your approach to overcome the issue and it is now working. I went with the second option as my region field is a lookup and returns too many records to allow it work.

I am using large datasets and have to rethink my field types to overcome the delegation issue as I have noticed that most fields cannot use delegation (lookups/dates to name a few). But that is a separate issue 🙂

Was this reply helpful? Yes No
iAm_ManCat 18,228 Most Valuable Professional on at

Like (1)

Report

Hi @Erec and @Eickhel,

If you want to avoid collections (and you definitely do if you're going to hit thousands of items which then have to be pulled down every time a refresh is done), you can create a slightly altered solution.

Your problem in the code you posted is that all your other items are just 'columns' whereas one of your items tries to reference the .Value of the column, which sharepoint is not able to delegate as a task on behalf of PowerApps (at time of writing).

If(
IsBlank(TextSearchBox1.Text),
SortByColumns(
Filter(
lsNearMissData,
ReportedBy= "Joe Bloggs"
),
"NearMissDate",
If(SortDescending1, Ascending, Descending)
),
SortByColumns(
Filter(
lsNearMissData,
StartsWith(Region.Value, TextSearchBox1.Text) && ReportedBy="Joe Bloggs"
||
StartsWith(ContractNoDeptCode, TextSearchBox1.Text) && ReportedBy="Joe Bloggs"
),
"NearMissDate",
If(SortDescending1,Ascending, Descending)
)
)

So solution would be to:

Create a new Text column in SharePoint, Call it RegionActualValue,

Refresh the data source in your app, add that column as a field within the gallery, set it's visible setting to false (its just a data-holder so that we can search in the gallery)

In any of your edit/new screens have your app fill the Default value of the Textbox within that RegionActualValue Card with Text(Region.Value) during every edit/new item (or go into SharePoint and retrospectively copy/paste the text value for all historic items).

Once those fields are filled, you can change your statement to:

If(
IsBlank(TextSearchBox1.Text),
SortByColumns(
Filter(
lsNearMissData,
ReportedBy= "Joe Bloggs"
),
"NearMissDate",
If(SortDescending1, Ascending, Descending)
),
SortByColumns(
Filter(
lsNearMissData,
StartsWith(RegionActualValue, TextSearchBox1.Text) && ReportedBy="Joe Bloggs"
||
StartsWith(ContractNoDeptCode, TextSearchBox1.Text) && ReportedBy="Joe Bloggs"
),
"NearMissDate",
If(SortDescending1,Ascending, Descending)
)
)

Which should then be Delegable,

Could you let me know if you get any further delegation errors from it and if so, then where it has underlined?

Cheers,
ManCat

Was this reply helpful? Yes No
Eickhel 374 Most Valuable Professional on at

Like (0)

Report

Sure, this might work as well... maybe RegionActualValue could be a calculated field ( =Region ) to avoid the copying and pasting.

It might worth a test to see if it works.

Thanks for sharing.

Was this reply helpful? Yes No
iAm_ManCat 18,228 Most Valuable Professional on at

Like (1)

Report

I just tested with Calculated Fields - these are not delegable!

I created a Calculated Field, ContractName2Test, set its value to =[Contract]
Values all filled in correctly.

Refreshed App data, edited the working delegated Filter from ContractNameValue to ContractName2Test and immediately it warned about delegation issues.

You could use a flow to fill the text columns with data from the person data in another field if you are not up for manual editing.

Was this reply helpful? Yes No
Eickhel 374 Most Valuable Professional on at

Like (1)

Report

Yes, I usually do it though Flow but the calculated filed got me wondering.

Thanks for testing it though.

Was this reply helpful? Yes No