Filter gallery using multiple switch case and conditions

(0) Share

Report

Posted on by Nazia786

Hi,

I need help to create filter query for below scenario.

I have master SP list (Master List {Title, SayYES, Main Type (Choice), Scenario (Choice), Change Type (Choice)}) and

child list (Activity list {Activity, Sort Order, Dry, chemical, Gas, Existing, New, Change Condition, Takeover, Owner Change, Name Change, Class change, Game change}). both lists connected with column ID - MID

Master list choice column items will be the child list (Number Column) fields. I need to filter the child list gallery (Activity list) based on master record item and those are 1's in child list columns. Please help with the filter query for activity list gallery.

All the highlighted column names as Number Type columns with 1 ,0 as values

On master gallery selected:

Set(VarcolName,Upper(LookUp([@'Master List'], MainType = ThisItem.'Main Type').MainType)),
ClearCollect( colData, Filter([@'Activity list '], MID=ThisItem.ID)),

Items filter for Activity list gallery

SortByColumns(
Switch (VarcolName,"GAS",
Filter (Coldata, GAS = 1),
"DRY", Filter (Coldata, DRY = 1),
"TANKER", Filter (Coldata, TANKER = 1)
), "Sort Order" ,SortOrder.Ascending)

I need to add one more switch case to Filter Scenario and Change type. to show the activities names in gallery those columns have "1's"

How can I do. Kindly advise. Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Nazia786 ,

I don't recommend you do this because the branches will reach 48. (3*4*4)

Please try to optimize the structure of your second table.

Sample:

Then try these formulas

Set(VarcolName,Upper(LookUp([@'Master List'], MainType = ThisItem.'Main Type').MainType)); ClearCollect( colData, Filter([@'Activity list '], MID=ThisItem.ID)); Set(VarcolName2,Upper(LookUp([@'Master List'], Scenario= ThisItem.Scenario).Scenario)); Set(VarcolName3,Upper(LookUp([@'Master List'], 'Change Type'= ThisItem.'Change Type').'Change Type'));
SortByColumns(Filter (Coldata, VarcolName in MainType && VarcolName2 in Scenario && VarcolName3 in 'Change Type' ), "Sort Order" ,SortOrder.Ascending)

Best Regards,

Wearsky

Was this reply helpful? Yes No
Nazia786 31 on at

Like (0)

Report
Copy link

Link copied!

Hi@v-xiaochen-msft
Thank you for your reply..
Share point lists already exists with 2k records. it's difficult to change the structure of table now.
Main type and Scenario are single select choice columns in master list.
if the record selected in master gallery Main type = "DRY" , Scenario = "New"
I need to filter the red highlighted activities.

Was this reply helpful? Yes No
v-xiaochen-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Nazia786 ,

I'm afraid this is difficult to achieve due to limitations of your table structure.

Best Regards,

Wearsky

Was this reply helpful? Yes No
Nazia786 31 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xiaochen-msft
Please kindly advise, if I change the table structure. then I need to make the Main type, Scenario, Change type columns to Multiselect Choice columns. then how can I change the below query to filter my gallery activity names.

SortByColumns(Filter (Coldata, VarcolName in MainType && VarcolName2 in Scenario && VarcolName3 in 'Change Type' ), "Sort Order" ,SortOrder.Ascending)

Was this reply helpful? Yes No
Verified answer

v-xiaochen-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @Nazia786 ,

I think it should be

SortByColumns(Filter (Coldata, VarcolName in MainType.Value || VarcolName2 in Scenario.Value || VarcolName3 in 'Change Type'.Value ), "Sort Order" ,SortOrder.Ascending)

Best Regards,

Wearsky

Was this reply helpful? Yes No
Nazia786 31 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xiaochen-msft
your proposed solution is working for me as of now. Thank you very much for that.
SortByColumns(Filter (Coldata, VarcolName in MainType.Value || VarcolName2 in Scenario.Value || VarcolName3 in 'Change Type'.Value ), "Sort Order" ,SortOrder.Ascending)

Please advise on my 2 questions,
1. Maintype, Scenario, Changetype are multi select choice columns. will it work out with large data filtering? delegation issue might occur right?

2. if VarcolName3 is not just one value. if we have, multiple values in this variable like table. then how to filter
one multiselect column IN another multiselect column?

Was this reply helpful? Yes No