Looping through a SP column and check for each value

[Anonymous]

Hi All

I have ran into a problem inside my app and have been looking for a solution for ages.

The situation is the following:

I have a parent list with all relevant data that sends out requests, then I have a child list that captures everybody that has already responded to that parent record with same request ID.

I have made 2 collections:

Collection 1: All Parent list records that have the status "Pending"

Collection 2:  Columns from my child list that are "Conflict Reference"(Text Column, This references to the ID column in the parent list) and "Approver Email" (Everybody from a security group that has already approved the parent record)

Now I only need to show the Parent list records in my gallery(pending status), from whom the ID = the child list "conflict reference" column and the logged in user's email isn't listed in the "Approver Email" column for that specific Reference ID.

I have a formula that works for the whole "Approver Email" column, but I need to separate it for each unique "Reference ID".

I'm sorry if my explanation is not so clear.

Thank you in advance for your help

Kind regards

[WarrenBelz]

@Anonymous ,

I cannot test this, but try

With(
   {
      wAll:
      Filter(
         'Conflict Checks List', 
         'Conflict Approval Status' = "Pending"
      ),
      wResponse:
      'Conflict Response List', 
   }
   Filter(
      wResponse, 
      Conflict_x0020_Reference_x0020_I in wAll.ID &&
      !(User().Email in ApproverEmail)
   )
)

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.

Visit my blog Practical Power Apps

[Anonymous]

Hi @WarrenBelz 

When I use this one, I get the following output in my gallery

So I get all 'Conflict Responses' where the user().email is not present in.

Basically, the 'Conflict Reference ID's that have the same value need to be grouped together like in the picture below, and then, I need to check if the User().email is in the 'ApproverEmail' column of the Conflict references that are grouped together.

When this is evaluated, I need to output the 'Conflict Check list' data in my gallery where the ID = the evaluated 'Conflict Reference ID's where the user().email was not present in the grouped values

Thank you in advance  & kind regards

[WarrenBelz]

@Anonymous ,

I am going to raise the white flag here shortly -  I need to output the 'Conflict Check list' data in my gallery where the ID = the evaluated 'Conflict Reference ID's where the user().email was not present in the grouped values

That is what I thought you were getting originally - the grouped values are not relevant as you are looking at all records.

[Anonymous]

Hi @WarrenBelz 

I would like to thank you for trying to help me tho.

I will break my head some more over this one 🙂

Kind regards!

[Anonymous]

Hi @WarrenBelz 

I would like to thank you for trying to help me tho.

I will break my head some more over this one 🙂

Kind regards!

[Anonymous]

Hi @WarrenBelz 

I made it work! I did the following:

ClearCollect(colAllPendingConflictChecks, Filter('Conflict Checks List', ConflictApprovalStatus = "Pending"));


ClearCollect(colGroupedResponsesbyID, GroupBy('Conflict Response List', "Conflict_x0020_Reference_x0020_I", "DATA"));

ClearCollect(colgroupedIdEmails, Filter(colGroupedResponsesbyID, !(User().Email in DATA.'Approver Email')));

ClearCollect(colGallPending, Filter(colAllPendingConflictChecks, ID in colgroupedIdEmails.Conflict_x0020_Reference_x0020_I))

Where my gallery has the item property of 'colGallPending'.

Do you think trying to implement all of these 'ClearCollects' in a With() function will increase app performance?

At the moment all of these ClearCollects are on the Screen 'OnVisible'

Thank you again for the help in my forum post, learned a lot!

Kind regards

[WarrenBelz · Accepted solution]

@Anonymous ,

Hi @Anonymous ,

I am glad you got it - I simply could not visualise what you needed from your description. If you want to save three redundant collections (and it should work faster)

With(
   {
      wAllPendingConflictChecks: 
      Filter(
         'Conflict Checks List', 
         ConflictApprovalStatus = "Pending"
      ),
      wGroupedIdEmails:
      With(
         {
            wGroupedResponsesbyID:
            GroupBy(
               'Conflict Response List', 
               "Conflict_x0020_Reference_x0020_I", 
               "DATA"
            )
         }, 
         Filter(
            wGroupedResponsesbyID, 
           !(User().Email in DATA.'Approver Email')
         )
      )
   },
   ClearCollect(
      colGallPending, 
      Filter(
         wAllPendingConflictChecks, 
         ID in wGroupedIdEmails.Conflict_x0020_Reference_x0020_I
      )
   )
)

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.

Visit my blog Practical Power Apps

[Anonymous]

@WarrenBelz 

Thank you for your help on this topic! 🙂

Kind regards