Multi filter gallery including a filter by rep name in a related gallery by ID

(0) Share

Report

Posted on by JD_26

102

I have a Master_List table and Master_Profile table , which are matched by the AccountID field.

I currently have this code working for filters in text boxes , just for the Master_List Table

SortByColumns(Filter(Master_List,StartsWith(Title,search_carrier_code.Text)
,(StartsWith(field_1,search_carrier_name.Text))
,(StartsWith(field_4,search_region.Text))
,(StartsWith(field_5,search_country.Text)))
,"field_1",Ascending)

What I need to do is include another filter called rep name , which is from the Master_Profile table. This table has multiple matching rows all linked to the Master_List by Account ID. I want to add another textbox called rep , which when the user types in the box , it will filter the Master_List , where all of the ID's in the Master_profile Table that matches the rep name , will be be selected in the Master_List

Categories:

Building Power Apps

All responses (4)

Answers (0)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @JD_26 ,

Could you please share more details about your scenario? Is the AccountID column a LookUp type column?

I assume the AccountID column is Text column, then the logic would be simple, with the rep name text box, you can filter the profile list and get result table using below formula:

Filter(Master_Profile, 'Rep Name' = RepSearchBox.Text).AccountID

Once getting the AccountID table, you can filter the Master list use the in operator, so the final formula will be:

SortByColumns( Filter( Master_List, StartsWith(Title,search_carrier_code.Text), StartsWith(field_1,search_carrier_name.Text), StartsWith(field_4,search_region.Text), StartsWith(field_5,search_country.Text), ID in Filter(Master_Profile, 'Rep Name' = RepSearchBox.Text).AccountID ), "field_1", Ascending )

Best regards,
JD_26 102 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks for looking at this for me , this line comes up with an error
ID in Filter(Master_Profile, 'Rep Name' = RepSearchBox.Text).AccountID
v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @JD_26 ,

What error did you get?

Try below instead:

With( {varFilter:Filter(Master_Profile, 'Rep Name' = RepSearchBox.Text).AccountID}, SortByColumns( Filter( Master_List, StartsWith(Title,search_carrier_code.Text), StartsWith(field_1,search_carrier_name.Text), StartsWith(field_4,search_region.Text), StartsWith(field_5,search_country.Text) ID in varFilter ), "field_1", Ascending ) )

If the error still occurs, please provide some screenshot.

Best regards,
JD_26 102 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks , I amended it slightly , but I do get a delegation warning , I have my limit set to 2000 rows , but there are 5000 rows in my data , so when using this filter , I appear to get some rows missing , is this fixable , my working code is
If(IsBlank(search_rep_text.Text),
SortByColumns(Filter(Master_List,
StartsWith(Title,search_carrier_code.Text),
StartsWith(field_1,search_carrier_name.Text),
StartsWith(field_4,search_region.Text),
StartsWith(field_5,search_country.Text)), "field_1", Ascending ),
With(
{varFilter:Filter(Master_Profiles, field_10 = search_rep_text.Text).field_16},
SortByColumns(
Filter(
Master_List,
StartsWith(Title,search_carrier_code.Text),
StartsWith(field_1,search_carrier_name.Text),
StartsWith(field_4,search_region.Text),
StartsWith(field_5,search_country.Text),
field_2 in varFilter
),
"field_1",
Ascending
)
))