Filter multiple SharePoint lists using Power Automate canvas app

[johnjohn123]

I am working on a work order system using SharePoint online & Power Apps canvas. Where we need to build the SharePoint lists in a way to secure them. So all the fields that follow same permission settings need to be added inside a separate SharePoint list. Now we have those lists:-

1) WorkOrderMain, with these fields:-

  - ID

  - Title

  - Description

  - Owner

2) WorkerOrder, with these fields

  - ID

  - Title

  - Status (Open, closed, In Progress)

3) Relation, with these fields:-

 -   WorkOrderMainID (store the Id of the related WorkOrderMain list item)

 -   WorkerOrderID (store the Id of the related WorkerOrder list item)

 -   AssignedTo

And we have these permission settings:-

1) The WorkOrderMain list can only be managed by the Project Managers.

2) The related WorkerOrder item can only be managed by the related AssingedTo user

3) The Relation list can only be managed by the service account and viewable by all users.

now this structure will work and will allow us to secure the SharePoint lists. but now let say i want to build a screen inside our Power App Canvas to get the WorkerOrders and by able to filter them by the AssignedTo AND/OR  WorkOrderMain.Owenr AND/OR WorkOrderMain.Title..

so not sure how we can achieve this? as the gallery or the list need to filter data from the 3 lists..

Thanks

[poweractivate]

Try having two Galleries, one for WorkOrderMain, one for WorkerOrder. For now to keep it simple let's keep WorkOrderMain as the primary gallery and WorkerOrder as the secondary Gallery:

Gallery for WorkOrderMain

1. Main Gallery (WorkOrderMain Items): This gallery will display items from the WorkOrderMain list. No need for any special formula here; the Items property of this gallery should be simply set to WorkOrderMain.

Gallery for WorkerOrder

2. Detail Gallery (WorkerOrder Items): This gallery will display items from the WorkerOrder list that are related to the selected item in the WorkOrderMain gallery.

Items Property for the Detail Gallery:

ForAll(
    Filter(
        Relation,
        WorkOrderMainID = GalleryWorkOrderMain.Selected.ID
    ),
    LookUp(
        WorkerOrder,
        ID = WorkerOrderID
    )
)

To test, select an item from the primary Gallery, see if the related WorkerOrder records for that selected WorkOrderMain show up in the secondary Gallery.

See if this helps as starting point @johnjohn123 

[johnjohn123]

@poweractivate thanks a lot for the reply. but still how i can filter the WorkerOrder by:-

AssignedTo AND/OR WorkOrderMain.Owenr AND/OR WorkOrderMain.Title..

??

Thanks

[poweractivate]

Ah I see, I should imagine you want to eliminate the first gallery essentially in my example to not select just one WorkOrderMain, and use only the 2nd Gallery in my example as if it were just one, having 3 configurable things to Filter by, Owner of the WorkOrderMain, Title of the WorkOrderMain, and AssignedTo of the Relation.

Do I understand correctly?

If so ,that's much more complex to think through but I may have a succinct formula for you -  Try it like this below in the Items property of only a single Gallery:

With(
    {wFilteredWorkOrderMain: Filter(
        WorkOrderMain,
        Owner = "YourDesiredOwner" || Title = "YourDesiredTitle"
    )},
    ForAll(
        wFilteredWorkOrderMain,
        ForAll(
            Filter(
                Relation,
                WorkOrderMainID = wFilteredWorkOrderMain[@ID] && AssignedTo = "YourDesiredAssignedTo"
            ),
            LookUp(
                WorkerOrder,
                ID = WorkerOrderID
            )
        )
    )
)

In the above,

- For Owner replace YourDesiredOwner with your appropriate value.

   - if Owner is not a Text type, replace it as appropriate.

- If || (OR) is inappropriate, use && (AND) instead of it in this part:

Owner = "YourDesiredOwner" && Title = "YourDesiredTitle"

- For Title replace YourDesiredTitle with your appropriate value.
- For AssignedTo replace YourDesiredAssignedTo with the value appropriate for you. 

See if this helps @johnjohn123 

[johnjohn123]

@poweractivate

thanks for the reply.. but if i am correct this will be able to get only 2000 items.. i mean the gallery will not be able to paginate over all the records, since the collection of items inside

wFilteredWorkOrderMain

can only handle 2,000 items.. am i correct? 

[johnjohn123]

@poweractivate any advice on my above comment? Thanks