Filter based on variable causing issues

[rodieremix]

Hi,

I have a gallery that I am trying to filter based on a variable. The formula for my gallery's Items is:

SortByColumns(Filter('EEO 2018 Data', HRG=UserEmailVar Or AREAMANAGER=UserEmailVar Or ALTERNATEREP=UserEmailVar), "ACKNOWLEDGED",Ascending)

The UserEmailVar is set with the OnStart control. It is set to:

Set(UserEmailVar,User().Email)

I have three fields and I need the gallery to be filtered if the user's email matches any of those fields. I tend to use the variable instead of User().Email directly as it generally seems to improve performance and speed. However, because of my filter issue I am using User().Email directly in place of UserEmailVar which leads to slower updates which is causing confusion among my users.  When I use the UserEmailVar, it seems to work every other time. Half the time the items show correctly, the other half it seems like it is trying to show all items. I will say that I am working with a list with over 500 items (about 650) but I increased the limit in the app to 2,000 so I would think it should still work correctly. Other formulas that use the UserEmailVar work perfectly, it is only the Filter I seem to be struggling with. I appreciate any suggestions. Thanks!

[AndySid]

Hi, try .Text after HRG=UserEmailVar

Example: SortByColumns(Filter(appContracts, ContractNo = xfilterContract.Text),"CategoryID")

[v-xida-msft · Accepted solution]

Hi @rodieremix,

I have made a test on my side, and don't have the issue that you mentioned.

On your side, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following (Type following formula within OnVisible property of the first screen instead of OnStart property😞

Set(UserEmailVar, User().Email)

Set the Items property of the Gallery to following:

SortByColumns(
         Filter('EEO 2018 Data', HRG = UserEmailVar || AREAMANAGER = UserEmailVar || ALTERNATEREP = UserEmailVar), 
         "ACKNOWLEDGED",
         Ascending
)

Or

SortByColumns(
         Filter('EEO 2018 Data', UserEmailVar in HRG || UserEmailVar in AREAMANAGER || UserEmailVar in ALTERNATEREP), 
         "ACKNOWLEDGED",
         Ascending
)

In addition, you could also consider take a try to save your SP list records into a Collection within your app, then use the Collection as a data source within your app. Please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following:

Set(UserEmailVar, User().Email);
ClearCollect(RecordsCollection, 'EEO 2018 Data')

Set the Items property of the Gallery to following:

SortByColumns(
         Filter(RecordsCollection, HRG = UserEmailVar || AREAMANAGER = UserEmailVar || ALTERNATEREP = UserEmailVar), 
         "ACKNOWLEDGED",
         Ascending
)

Or

SortByColumns(
         Filter(RecordsCollection, UserEmailVar in HRG || UserEmailVar in AREAMANAGER || UserEmailVar in ALTERNATEREP), 
         "ACKNOWLEDGED",
         Ascending
)

Please take a try with above solution I provided, then check if the issue is solved.

Note: Please consider take a try to change the "Data row limit for non-delegable queries" option into 2000 within the Advanced settings of App settings of your app.

Best regards, 

[rodieremix]

Hi @v-xida-msft,

Thanks for your help! The Collection workaround fixed my issue. Appreciate it!