How to Filter a list with having other SPO list with Many to Many relationship affecting it in my Canvas PowerApp gallery?

[SueA]

Hi:

I Have the following Lists:

• Reviewers List (Columns: ID, ReviewerName (Single Line of Text), Reviewer (People Picker), Role (Single Line of Text)
• Bureau List (ID, BureauName (Single Line of Text)
• BureauReviewer List (Columns: ID, BureauID, ReviewerID)

For each Bureau there are eight Reviewers and they are cross referenced in BureauReviewer List.

For each new Document for a Bureau, I am trying to populate these eight reviewers in a Gallery titled galRelatedReviewers.

What I have done so far:

1. Placing Reviewers(list) for Items property, shows all the reviewers in the galRelatedReviewers Gallery. (I am unable to filter it.)
2. Created another Gallery titled galBureauReviewers and the following code filters all the eight reviewers' IDs and pertinent Bureau IDs:   Filter(BureauReviewer,BureauID=drpBureauCS.Selected.ID).  I am selecting the Bureau from a dropdown in the beginning.

What do I need:

1. How do I populate galRelatedReviewers with all eight related Reviewers' Names (for that Bureau) and the pertinent Role when Reviewers.ID = BureauReviewers.ReviewerID?

Any suggestion is appreciated.

Thanks,
SueA.

[SueA]

@WarrenBelz 

Just wondering....Would you by any chance have a bit time to help me with the above issue?

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-Filter-a-list-with-having-other-SPO-l...

Thank you for your consideration...

[WarrenBelz · Accepted solution]

Hi @SueA ,

Firstly, my overall advice is that if you are using SharePoint, you need to stop thinking "SQL structure" as SharePoint is not a relational database (meaning "one degree of separation is always best as you are only going to cause yourself unwanted and unnecessary Delegation issues). Yours is easy to fix however - just get rid of the Bureau list altogether and simply put the BureauName instead of BureauID in the BureauReviewer list.

You would then have in your dropdown Items (you could also maintain the Bureau List and use this for the drop-down Items using the name)

Sort(
   Distinct(
      BureauReviewer,
      BureauName
   ),
   Result
)

and your Galley Items

With(
   {
      wName:
      Filter(
         BureauReviewer,
         BureauName = YourDDName.Selected.Result
      )
   },
   Ungroup(
      ForAll(
         wName As aName,
         Filter(
            Reviewers,
            ID = aName.ReviewerID
         )
      ),
      "Value"
   )
)

however if you want to maintain this structure (and for this example it is possible), then your drop-down Items could be simply

Sort(
   Bureau,
   BureauName
)

and your Gallery Items

With(
   {
      wName:
      Filter(
         BureauReviewer,
         BureauID = YourDDName.Selected.ID
      )
   },
   Ungroup(
      ForAll(
         wName As aName,
         Filter(
            Reviewers,
            ID = aName.ReviewerID
         )
      ),
      "Value"
   )
)

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

[SueA]

Hi;  @WarrenBelz 

This worked perfectly! Thank you kindly for your timely help and the guidance. I do carry DBA mindset as in my prior life I was a DBA. Its becoming a burden now though! Having complex backend structure of this project and tough requirements and this being my second App ever, I am truly a novice. Thus, I created all the required SP lists and similarly SQL tables in Management Studio and made sure I am able pull information out using simple SQL statements as needed before starting to create the Canvas App. Apparently, I have made it more complex than need to be. I am working step by step to denormalize it! Your guidance certainly helping me a lot.

Truly appreciate it.
SueA.

[WarrenBelz]

Hi @SueA ,

I am glad you used the word denormalize as I do this for many reasons including the depth of query users can obtain from a single list in the SharePoint interface. This blog of mine may be of interest to you.

[SueA]

Hi;@WarrenBelz 

I read through a few articles in your BLOG.  Lots of Tips and Tricks and very useful knowledge. Thank you for pointing this treasure to me.

I thank you for your help above in pulling the eight reviewers out of SP List that related to a specific Bureau in the above thread. But I need to send them an Approval Request which goes around in the same order that the list is populated and making sure that everyone responds before the Review cycle is considered complete. Your tips regarding alternate shading and assigning Row No to a Gallery, I thought would work.

By selecting the entire Gallery, for TemplateFill Property, I posted below code for alternate shading:

If( ThisItem.IsSelected, LightGreen, If( Mod( ThisItem.RowNo, 2 ) = 0, White, LightBlue ) )

However, I am getting an error stating that Function Mod has some invalid arguments. What am I doing wrong?

Ref: https://www.practicalpowerapps.com/useful-tips/

Then, for auto numbering, I do not have a search box to filter or sort functionality that exists. Simply a small Gallery of eight rows. How do I use your code to auto number and later use that newly assigned number in the FLOW to assign the Review Task, send reminder etc., and then count and when all is done mark the task as complete and for which property in PowerApp? 

Can you help?

Thank you,
SueA.

[WarrenBelz · Accepted solution]

Hi @SueA ,

I assume you have applied the RowNo field from the first part of the blog ? Code works fine if you have

[SueA]

@WarrenBelz 

Oh, I see that RowNo is defined in the earlier script. My bad... Sorry about missing that aspect. I will try with both the scripts. Thank you.

[SueA]

@WarrenBelz 

You have helped me in the past. I was hoping if you could possibly spare some time to help me with this new issue listed below, please?

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-Filter-multiple-SP-Lists-to-retrieve-...

Thank you for your consideration.

Regards,
SueA.