cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Erec
Frequent Visitor

Power apps Filter used with StartsWith and logical test issue

Hi

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Eickhel
MVP

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 )

 

View solution in original post

6 REPLIES 6
Eickhel
MVP

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 )

 

View solution in original post

Erec
Frequent Visitor

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 🙂

iAm_ManCat
Super User II
Super User II

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

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


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.

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.

 

 

 

delegation fail1.png

 

 

 

 

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.

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


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

 

Thanks for testing it though.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (30,709)