Help needed with data comparison

[DCHammer]

I can't figure out the best (or any, actually) way to do the comparison I need. This really isn't a PowerApps question, other than the fact I'm doing it in PowerApps.

App is essentially an approval process. The request needs to be approved by the current director and proposed director before it moves anywhere else in the process.

Directors save their approval in "Approvals" list. The request has a current and proposed director.

What I need to do is check if both of them have created an approval.

I know I can do a lookup to get both the current and proposed directors. I know I can also do a lookup to see if there is an approval record for each but I don't understand how to do it in one step.

I've done something similar in the past but I didn't have to look for specific users, I used CountRows to determine if the number of approved records match the number of approvers required for a given approval level.

Is it possible to Filter the approval records based on the request ID and use something like a Contains clause to test if that user exists in a field in the returned filter set?

[Giraldoj · Accepted solution]

Hi @DCHammer 

To check if both the current and proposed directors have created an approval in PowerApps, you can use the Filter function to get the relevant approval records and then use CountIf to ensure both directors have created an approval.

You can use the following code in the Items property of a gallery or the Text property of a label to display the approval status:

ClearCollect(
    colFilteredApprovals,
    Filter(Approvals, RequestID = ThisItem.RequestID)
);

If(
    CountIf(colFilteredApprovals, DirectorEmail = ThisItem.CurrentDirectorEmail && ApprovalStatus = "Approved") > 0 &&
    CountIf(colFilteredApprovals, DirectorEmail = ThisItem.ProposedDirectorEmail && ApprovalStatus = "Approved") > 0,
    "Both Approved",
    "Pending Approvals"
)

If my response resolved your issue, please feel free to mark it as the solution by clicking accept it as a solution. 

If you liked my solution, please give it a thumbs up. 
This helps others find the answer more easily.

Connect with me: LinkedIn 

[DCHammer]

Thank you. That's going to work. I appreciate your help.

[DCHammer]

I'm sure you don't care but I often use this as a personal repository for my own records.

Here is what your suggestion turned into:

ClearCollect(
    colFilteredApprovals,
    Filter('Account Move - Approvals', RequestID = varItem.ID)
);
If(
    CountIf(colFilteredApprovals, Approver.Email = varItem.Current_Director.Email && Status.Value = "Recommended") > 0 &&
    CountIf(colFilteredApprovals, Approver.Email = varItem.Proposed_Director.Email && Status.Value = "Recommended") > 0,
    Patch(
    'Account Move - Requests',
    LookUp(
        'Account Move - Requests',
        ID = varItem.ID
    ),
    {
        Directors_Approve: true
    }
)

Rather than having to worry about replicating this everywhere, it sits on the Approval creation submission. Every time an 'approval' is saved, this check occurs and sets another boolean on the record that I can use everywhere else.

The workflow doesn't allow for changing your mind so I don't have to worry about an approval being removed once given.