cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rodieremix
Level: Powered On

Filter based on variable causing issues

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter based on variable causing issues

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, 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
AndySid
Level 8

Re: Filter based on variable causing issues

Hi, try .Text after HRG=UserEmailVar

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

Community Support Team
Community Support Team

Re: Filter based on variable causing issues

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, 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

rodieremix
Level: Powered On

Re: Filter based on variable causing issues

Hi @v-xida-msft,

 

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 183 members 4,305 guests
Please welcome our newest community members: