Filter a Gallery by ClearCollect gathered text

(0) Share

Report

Posted on by TH-23071335-0

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (1)

v-xida-msft on at

Like (0)

Report
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,

Was this reply helpful? Yes No
TH-23071335-0 16 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Verified answer

v-xida-msft on at

Like (0)

Report
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,

Was this reply helpful? Yes No