ForAll in Patch with Lookup - PowerApps attempts to create new record instead of patching

[aaronhowe1]

I have a SharePoint list for expense claims, populated through a form that users enter directly into and which captures line item expenses (with an ID from the SP list ID column) and a claim number, which is generated from within the app on the fly.  The claim number is then used in a cascading gallery so that an approver can see a list of claims, and then by selecting an item from the gallery can see the expense line items within it in a second gallery.

When they approve the claim from an approval button, PowerApps uses the patch function to patch in their user ID and date of approval - this works fine; however when a claim has more than one line item only the first expense entry is patched and subsequent ones ignored - I understand why this is as it's only ever seeking the first record found.  The problem I am having though is that when I follow other examples to enter a ForAll statement into the patch to collect all matching expense entries under a single claim number, still only the first record is patched.

The data schema:

SP List: 'Staff Expenses'

'Claim Number' is the SP list Title field

Gallery1: The master list of claims

Gallery2: The sub-gallery with expense item details

Patch code without ForAll:

If(
    ApproverLevel = "2",
    Patch(
        'Staff Expenses',
        LookUp(
            'Staff Expenses',
            'Claim Number' = ThisItem.Title
        ),
        {
            'Approver 2': User().Email,
            'Approval 2 Date': Today()
        }
    ),
    ApproverLevel = "3",
    Patch(
        'Staff Expenses',
        LookUp(
            'Staff Expenses',
            'Claim Number' = ThisItem.Title
        ),
        {
            'Approver 3': User().Email,
            'Approval 3 Date': Today()
        }
    ),
    Patch(
        'Staff Expenses',
        LookUp(
            'Staff Expenses',
            'Claim Number' = ThisItem.Title
        ),
        {
            'Approver 1': User().Email,
            'Approval 1 Date': Today()
        }
    )
)

How/where do I enter the ForAll function to pick up all expense line items under the same claim number?  I'm afraid I can't find the thread I was following before to recount what went wrong.  The revised code I tried under both Gallery1 and Gallery2 (same result both times):

If(
    ApproverLevel = "2",
    ForAll(
        Gallery1.AllItems,
        Patch(
            'Staff Expenses',
            LookUp(
                'Staff Expenses',
                'Claim Number' = ThisItem.Title
            ),
            {
                'Approver 2': User().Email,
                'Approval 2 Date': Today()
            }
        )
    ),
    ApproverLevel = "3",
    ForAll(
        Gallery1.AllItems,
        Patch(
            'Staff Expenses',
            LookUp(
                'Staff Expenses',
                'Claim Number' = ThisItem.Title
            ),
            {
                'Approver 3': User().Email,
                'Approval 3 Date': Today()
            }
        )
    ),
    ForAll(
        Gallery1.AllItems,
        Patch(
            'Staff Expenses',
            LookUp(
                'Staff Expenses',
                'Claim Number' = ThisItem.Title
            ),
            {
                'Approver 3': User().Email,
                'Approval 3 Date': Today()
            }
        )
    )
)

[WarrenBelz]

Hi @aaronhowe1 ,

Try this format

Patch(
   'Staff Expenses',
   ForAll(
      Gallery1.AllItems As _Data,
      With(
         {
            _ID:
            LookUp(
               'Staff Expenses',
               'Claim Number' = _Data.Title
            ).ID
         },
         Switch(
	        ApproverLevel,
            "1",
            {
               ID: _ID, 
               'Approver 1': User().Email,
               'Approval 1 Date': Today()
            },
		    "2",
            {
               ID: _ID,
               'Approver 2': User().Email,
               'Approval 2 Date': Today()
            },
            "3",
            {
               ID: _ID,
               'Approver 3': User().Email,
               'Approval 3 Date': Today()
            }
         )
      )
   )
)

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

[aaronhowe1]

Thank you @WarrenBelz, I tried it but it didn't patch any records unfortunately; both single-entry claims and multi-entry claims.  There were no errors and the monitor function didn't return anything so I think the problem might be the way I'm presenting the data.

In one example, claim number 2 contains three line items, which have a SP list ID of 161, 163 and 164.  The lookup function in the code you provided works (in a text label it only returns the first result obviously but it's 161 so it's definitely returning the right reference); so I can see that logically it must be checking all matching IDs 161=161 and so on, and patching them... but it doesn't.

So this tells me that perhaps the problem is that I'm calling the result from Gallery1, since that would have the master "wrapper" records rather than the line items in it... could that be it?  Perhaps I need to explain the flow better:

• User enters a new expense claim in a form, enters three distinct entries for different values/suppliers etc and once finished (by closing the form) they are given a sequential claim number based on the last committed record in the SP list - let's say it's claim number 6.  The expense entries contained within it are line numbers (SP list IDs) 600, 601 and 602 though the user never sees those.
• A manager comes to approve the entries and makes a selection from Gallery1 which is filtered by their approval level/line management and whether the claim has already been approved (Y/N calculated field in SP).  For appearances and ease of use the Gallery1 items are selected through GroupBy and pull the fields:
• 'Claim Number',
• 'Approver 1',
• 'Approver 2',
• 'Approver 3',
• 'Staff Name',
• ID

• In order to see the expense line items and check that they are valid, the approver makes a selection in Gallery1 which is shown in Gallery2 (populated by Gallery1.Selected.ID).
• Provided all of the expense line items are okay, the approver presses the approved button in Gallery1 and the patch should update entries 600, 601 and 602 in the SP list with the approver's name and current date.

[WarrenBelz]

Hi @aaronhowe1 ,
What to you mean by multi-entry claims ? Also what type of fields are Approver1/2/3 ? I also assume that 

LookUp(
   'Staff Expenses',
   'Claim Number' = Title
)

 Where Title is the Gallery field identifies the correct record to update in 'Staff Expenses'

[aaronhowe1]

Sorry @WarrenBelz, I realise I'm being very unclear.  I'd post the app itself but as it has connections and personal data in it I can't.

When I say multi-entry claims I mean they have a grouped claim number (1, 2, 3, 4, 5 etc) and within that there may be a single expense entry or there may be a dozen - each expense line entry is a line in the SP list:

Approver 1 2 and 3 are linked emails from the users taking the approval route through the app (there are separate screens for entering claims and approving claims).

And the Title field is where I think I'm confusing things - in the screenshot above "Claim Number" is the SP list Title field (I know this is poor data management, it wasn't planned very well) - so that refers to the overall claim number.  You can see from the screenshot that claim 2 (total value £50) has three expense lines with IDs 161, 163 and 164 at £10, £20 and £20 respectively.

[WarrenBelz]

Hi @aaronhowe1 ,

To clarify the gallery structure, would the items 600, 601 and 602 each be separate records in the gallery ? I al also assuming that Approver Level is set by the logged-in user's profile.

[aaronhowe1]

@WarrenBelz 

Correct, separate items in Gallery2/SP list but "joined" by their shared claim number whcich is generated in-app.  And yes the approver is always the logged in profile.

[WarrenBelz]

@aaronhowe1 ,

Another thought (I am fast running out of them as the code works here on a test list) - is ApprovalLevel a number or string ?

[aaronhowe1]

It's a number (2 or 3) in the SP list behind.  It's just to allow for authorisations at certain values so though it's used on the app start it isn't used on that Gallery1 field and I more than likely will remove it.  Originally I planned to show the level 1/2/3 (if applicable) authorisations in the gallery lines but I think doing so will cause the current grouping to fail so I will likely remove the field.

[WarrenBelz]

@aaronhowe1 ,

That was probably the #1 question I should have asked - does this work ?

Patch(
   'Staff Expenses',
   ForAll(
      Gallery1.AllItems As _Data,
      With(
         {
            _ID:
            LookUp(
               'Staff Expenses',
               'Claim Number' = _Data.Title
            ).ID
         },
         Switch(
            ApproverLevel,
            1,
            {
               ID: _ID, 
               'Approver 1': User().Email,
               'Approval 1 Date': Today()
            },
            2,
            {
               ID: _ID,
               'Approver 2': User().Email,
               'Approval 2 Date': Today()
            },
            3,
            {
               ID: _ID,
               'Approver 3': User().Email,
               'Approval 3 Date': Today()
            }
         )
      )
   )
)