Join 3 SharePoint lists and filter with 3 multiselect columns in power apps

(0) Share

Report

Posted on by Nazia786

Hi,

I need help to build the query items for filtering the gallery (Ship activity).

I have 3 SharePoint lists (Ship Information, Test Activity List, Ship Activity)

VslType , TOScenario, Changes are single select choice column in Ship information.

VslType , TOScenario, Changes are Multi select choice column in Test Activity List.

In power apps, when Ship is selected from master gallery. I need to filter the ship activity List (Child list) which contains ShipID from Ship information list and Acivitylist ID from Test Activity list.

filter ShipActivity list condition is like.. when ship selected from ship information (ShipID), its vsltype must match with vsltype in Test Activity list AND (match TOscenario from ship information to TOscenario from Test Activity list OR (match Changes column from ship information to Changes column from Test Activity list)

Get Activity names

IF ( VslType AND (TOScenario OR Changes))

Kindly help to build the filter query for Ship activity. Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

VishalJhaveri 1,167 Moderator on at

Like (0)

Report

Solved: Join two collections - Power Platform Community (microsoft.com)
You must try this out. It will definitely help you out.

Please mark my answer as solution, if it helps you out.

Was this reply helpful? Yes No
Nazia786 31 on at

Like (0)

Report

Hi @v-xiaochen-msft Can you please help me on this.

Was this reply helpful? Yes No
WarrenBelz 153,059 Most Valuable Professional on at

Like (0)

Report

Hi @Nazia786 ,

How are you selecting the relevant values, what fields from which list are you selecting and what are you expecting in the displayed result ?

Was this reply helpful? Yes No
Nazia786 31 on at

Like (0)

Report

Hi @WarrenBelz
Thank you for your reply.
We have 150 activities(rows) in Test Activity list
when we create a new ship in Ship information list. we assign activities to ship Id and insert Activity names (based on ship vsltype,TO scenario, Changes) and ShipID in Ship activity list

When Ship is selected in master gallery. I need to display the data relevant to that ship from Ship activity list in child gallery
Pick up the activity names those relevant to selected ship's {vsltype AND (TOscenario OR changes)} from Test Activity list (Activity list ID)
IF possible,
First, I need to filter the test activity list (ID's) based on selected ship's {(vsltype AND (TOscenario OR changes)}
and then select the activity names based on ShipID and Activitylist ID's..

Please advise will it be easy if I have vslType, TOscenario and changes columns added in Ship acitvity list (Childlist) so that I can filter only one table based on ship selected.

Was this reply helpful? Yes No
WarrenBelz 153,059 Most Valuable Professional on at

Like (0)

Report
Hi @Nazia786 ,

The fundamental issue you have is that SharePoint is not a Relational Database (this is not SQL) and does not accept Delegation on "server side" joins. This will probably get you something like what you are after, but is not Delegable

AddColumns( Filter( TestActivityList, ActivityListID = GalleryName.Selected.ID ), ActivityName, Lookup( ShipActiviry, ShipID = ID ).ActivityName, Status, Lookup( ShipActiviry, ShipID = ID ).Status )

When using SharePoint, it is good to consider the "one degree of separation" principle and not use "look through" queries even it this means "denormalizing" your data.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No