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

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

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

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.
Highlighted
Frequent Visitor

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?

Highlighted
Community Support
Community Support

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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,388)