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

Filter a Gallery by ClearCollect gathered text

I currently have a clearcollect that runs on app start:

ClearCollect(UserGroups,{UserEmail:"first.last@domain",SecurityGroup:"Super",Location:"Lacrosse"}

 

I want to filter a gallery such that the gallery only displays records when the record location column matches the user's collected location. 

I have tried variations on Filter(listname , tablecolumn=usergroups.location) without success.

I suspect that while I'm asking for help on this, it may just be better to also not have an enormous clearcollect of users, but instead reference a separate list of users with location assignment in that list. I would appreciate help on that filter syntax as well.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter a Gallery by ClearCollect gathered text

Hi @DCpower ,

Could you please share a bit more about the issue that you mentioned?

Could you please share more details about the CurrentUser variable that you mentioned within your Patch formula?

Further, do you want to use a separated SP list to list the user reocrds instead of Collection?

 

Based on the needs that you mentioned, please consider modify your Filter formula within the Items property of the Gallery as below:

Filter(
tablename, StartsWith(WOCustomer,T_obb_CustFilterInput.Text) || T_obb_CustFilterInput.Text="", WOAssignedTo=Dr_Tech_1.Selected.Value || Not(T_techfilterengage.Value), WOStatus=Dr_StatusFilter.Selected.Value || Not(T_statusfilterengage.Value),
Not(WOStatus="Closed"),
WOBranch in 'Seperated List'.Location /* <-- Add formula here */
)

In addition, if you want to change the reference of the current user's location to the same list, please consider modify your Patch formula as below:

Patch(
tablename,
Defaults(tablename),
{
WONumber:Max(Max(tablename,WONumber)+1,Max(tablename2,WVNumber)+1), WOCustomer:T_CustInput.Text, WOPO:T_PO.Text, WODateInSmiley Very Happy_DateIn.SelectedDate, WOAssignedToSmiley Very Happyr_AssignedTech.Selected.Value, WOStatusSmiley Very Happyr_Status.Selected.Value, WOContact:T_ContactInput.Text, WODateCompleted:Blank(), WODateInvoiced:Blank( ), WOBranch: LookUp('Separated List', UserEmail = User().Email, Location), /* <-- Modify formula here */ WOWorkTypeSmiley Very Happyr_WorkType.Selected.Value, WOInstructions:T_NotesInput.Text
}
)

In addition, you could also consider store the current login user email into a variable, then reference the variable within your Patch formula:

Set the OnStart property of the App control to following:

Set(CurrentUserEmail, User().Email)

Modify your Patch formula as below:

Patch(
     tablename,
     Defaults(tablename),
     {
        WONumber:Max(Max(tablename,WONumber)+1,Max(tablename2,WVNumber)+1),
        WOCustomer:T_CustInput.Text,
        WOPO:T_PO.Text,
        WODateInSmiley Very Happy_DateIn.SelectedDate,
        WOAssignedToSmiley Very Happyr_AssignedTech.Selected.Value,
        WOStatusSmiley Very Happyr_Status.Selected.Value,
        WOContact:T_ContactInput.Text,
        WODateCompleted:Blank(),
        WODateInvoiced:Blank( ),
        WOBranch: LookUp('Separated List', UserEmail = CurrentUserEmail, Location),  /* <-- Modify formula here */
        WOWorkTypeSmiley Very Happyr_WorkType.Selected.Value,
        WOInstructions:T_NotesInput.Text
      }
)

Please take a try with above formula, check if the issue is solved.

 

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
Community Support Team
Community Support Team

Re: Filter a Gallery by ClearCollect gathered text

HI @DCpower ,

Do you want to filter your Gallery Items based on collected location within UsersGroups collection?

 

Based on the formula that you mentioned, I think there is something wrong with it. The tablecolumn within your Filter function is a Text type columm, but the result the Usergroups.Location formula returned is a Table value, we could not compare a text value type column with a Table value using "=" operator directly.

 

I have made a test on my side, please consider modify your formula as below:

Filter(
listname ,
tablecolumn in UserGroups.Location
)

Please take a try with above formula, then check if the issue is solved.

 

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.
DCpower
Level: Powered On

Re: Filter a Gallery by ClearCollect gathered text

Thank you! I was looking for that type of help.  As my app keeps getting larger, I think I may want to switch from using a clearcollect command with a list of names in it, to using a list of users. That way it's easier to edit later.

So here's the details of my situation. I'm using a Patch command to add records to a table:

 

Patch(tablename,Defaults(tablename),{WONumber:Max(Max(tablename,WONumber)+1,Max(tablename2,WVNumber)+1),
WOCustomer:T_CustInput.Text,
WOPO:T_PO.Text,
WODateIn:D_DateIn.SelectedDate,
WOAssignedTo:Dr_AssignedTech.Selected.Value,
WOStatus:Dr_Status.Selected.Value,
WOContact:T_ContactInput.Text,
WODateCompleted:Blank(),
WODateInvoiced:Blank( ),
WOBranch:LookUp(UserGroups,UserEmail=CurrentUser,Location),  //broken??
WOWorkType:Dr_WorkType.Selected.Value,
WOInstructions:T_NotesInput.Text});
Navigate(OpsBigBoard)

 

And a gallery to display the information with this formula:

 

Filter(tablename,
Or(StartsWith(WOCustomer,T_obb_CustFilterInput.Text),T_obb_CustFilterInput.Text=""),
Or(WOAssignedTo=Dr_Tech_1.Selected.Value,Not(T_techfilterengage.Value)),
Or(WOStatus=Dr_StatusFilter.Selected.Value,Not(T_statusfilterengage.Value))//,
//Not(WOStatus="Closed")

I'd like to add a line in the gallery filter that references a separate list of users so that it only displays the records that have column WOBranch values that match the values in that separate list where I've listed users and given them a location value. 

And also, the current way I'm trying to patch the current user's location will need to be changed to reference the same list, since it's currently referencing the clear collect.

 


Does that make sense?

Community Support Team
Community Support Team

Re: Filter a Gallery by ClearCollect gathered text

Hi @DCpower ,

Could you please share a bit more about the issue that you mentioned?

Could you please share more details about the CurrentUser variable that you mentioned within your Patch formula?

Further, do you want to use a separated SP list to list the user reocrds instead of Collection?

 

Based on the needs that you mentioned, please consider modify your Filter formula within the Items property of the Gallery as below:

Filter(
tablename, StartsWith(WOCustomer,T_obb_CustFilterInput.Text) || T_obb_CustFilterInput.Text="", WOAssignedTo=Dr_Tech_1.Selected.Value || Not(T_techfilterengage.Value), WOStatus=Dr_StatusFilter.Selected.Value || Not(T_statusfilterengage.Value),
Not(WOStatus="Closed"),
WOBranch in 'Seperated List'.Location /* <-- Add formula here */
)

In addition, if you want to change the reference of the current user's location to the same list, please consider modify your Patch formula as below:

Patch(
tablename,
Defaults(tablename),
{
WONumber:Max(Max(tablename,WONumber)+1,Max(tablename2,WVNumber)+1), WOCustomer:T_CustInput.Text, WOPO:T_PO.Text, WODateInSmiley Very Happy_DateIn.SelectedDate, WOAssignedToSmiley Very Happyr_AssignedTech.Selected.Value, WOStatusSmiley Very Happyr_Status.Selected.Value, WOContact:T_ContactInput.Text, WODateCompleted:Blank(), WODateInvoiced:Blank( ), WOBranch: LookUp('Separated List', UserEmail = User().Email, Location), /* <-- Modify formula here */ WOWorkTypeSmiley Very Happyr_WorkType.Selected.Value, WOInstructions:T_NotesInput.Text
}
)

In addition, you could also consider store the current login user email into a variable, then reference the variable within your Patch formula:

Set the OnStart property of the App control to following:

Set(CurrentUserEmail, User().Email)

Modify your Patch formula as below:

Patch(
     tablename,
     Defaults(tablename),
     {
        WONumber:Max(Max(tablename,WONumber)+1,Max(tablename2,WVNumber)+1),
        WOCustomer:T_CustInput.Text,
        WOPO:T_PO.Text,
        WODateInSmiley Very Happy_DateIn.SelectedDate,
        WOAssignedToSmiley Very Happyr_AssignedTech.Selected.Value,
        WOStatusSmiley Very Happyr_Status.Selected.Value,
        WOContact:T_ContactInput.Text,
        WODateCompleted:Blank(),
        WODateInvoiced:Blank( ),
        WOBranch: LookUp('Separated List', UserEmail = CurrentUserEmail, Location),  /* <-- Modify formula here */
        WOWorkTypeSmiley Very Happyr_WorkType.Selected.Value,
        WOInstructions:T_NotesInput.Text
      }
)

Please take a try with above formula, check if the issue is solved.

 

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

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 (Last 30 Days)
Users online (6,575)