Patching a collection using a ForAll() Function

[FrankKelp]

Hi all, 

Hoping someone can point me in the right direction here with a small issue regarding patching an entire collection to Dataverse. 

I have a multi select gallery were the user can select more than one item. As such I am attempting to patch an entire collection to a dataverse table. 

The issue I am having is with my second ForAll() that performs the actual patch. 

I collect all items in the gallery in the first ForAll(), but when I patch to the datasource, what I end up with is three duplicate values. 

For context, my testing was performed with 3 records in the collection, but instead of creating three unque rows, I get 3 duplicate rows of the first record in the collection. Hope that makes sense. 

Has anyone ran into the same issue before, if so, any tips?

My code: 

//Create transfer record
Set(
    varTransferRunOutput,
    Patch(
        Transfers,
        Defaults(Transfers),
        {
            Destination: LookUp(
                Sites,
                Name = First(
                    Split(
                        varInternalScan,
                        ","
                    )
                ).Result
            ),
            'Provided By': LookUp(
                Accounts,
                Account = GUID("ee2cce64-7459-eb11-a812-00224840faa6")
            ),
            Account: LookUp(
                Accounts,
                Account = cmbInternalAccount.Selected.Account
            ),
            Route: 'Route (Transfers)'.Internal,
            Date: Now()
        }
    )
);
//Create collection for all gallery items
ForAll(
    galInternalItem.AllItems,
    Collect(
        colInternalItemRun,
        {
            Transfer: LookUp(
                Transfers,
                Transfer = varTransferRunOutput.Transfer
            ),
            Account: cmbInternalAccount.Selected.Account,
            Item: GUID(lblInternalItemGUID.Text),
            Count: Value(txtInternalItemTransferQty.Text),
            'Source Location': lblSourceLocationGUID.Text,
            Status: 'Status (Transfer Items)'.Active
        }
    )
);
//Patch all collection records to Transfer Items table
ForAll(
    colInternalItemRun,
    Patch(
        'Transfer Items',
        {
            Transfer: LookUp(
                Transfers,
                Transfer = varTransferRunOutput.Transfer
            ),
            Account: LookUp(
                Accounts,
                Account = cmbInternalAccount.Selected.Account
            ),
            Item: LookUp(
                Devices,
                Device = GUID(lblInternalItemGUID.Text)
            ),
            Count: Value(txtInternalItemTransferQty.Text),
            'Source Location': LookUp(
                Sites,
                Site = GUID(lblSourceLocationGUID.Text)
            )
        }
    )
);

Three duplicate values from Patch statement: 

Many thanks! 

Frank

[TheRobRush]

Patching will work much better if you do it in the opposite order,

ClearCollect(
     CollectionName(
                    ForAll(GalleryItems to collect,
                          {Structure:Item})));

Patch(SourceToPatchTo, 
     ForAll(CollectionYouMade, CanBeJustThisRecordIfYouStructureABoveCollectForAllSameAsThisSourceIsLaidOutOtherwise  {ColumNames:ThisRecord.Data}))

ForAll does not behave exactly same as we'd be used to in programming, but more liek returns a list of items then you work with it. If you put it at top then you repeat the same list over and over. It works in some situations, but more often than not you will get unexpected resutls

[FrankKelp]

Hi @TheRobRush ,

Many thanks for your reply, it was very helpful. 

A situation I am now running into is that it doesn't accept 

ThisRecord.Item

Or

ThisRecord.Account

The error message I receive is that I  "Cannot use a non-record value in this context" - When I remove the item & account column there error disappears. 

Both columns are lookup columns in the patch destination table. 

What is the best way to navigate this? 

I tried two different approaches: 

1. 
Patch(
    'Transfer Items',
    ForAll(
        colInternalItemTransfer,
        {
            Transfer: ThisRecord.Transfer,
            Count: ThisRecord.Count,
            Status: ThisRecord.Status,
            Item: ThisRecord.Item,
            Account: ThisRecord.Account,
            'Source Location': ThisRecord.'Source Location'
            )
        }
    )
);

2. 
Patch(
    'Transfer Items',
    ForAll(
        colInternalItemTransfer,
        {
            Transfer: ThisRecord.Transfer,
            Count: ThisRecord.Count,
            Status: ThisRecord.Status,
            Item: LookUp(
                Devices,
                Device = GUID(lblInternalItemGUID.Text)
            ),
            Account: LookUp(
                Accounts,
                Account = ThisRecord.Account
            ),
            'Source Location': LookUp(
                Sites,
                Site = ThisRecord.Site
            )
        }
    )
);

Thanks Again!

Frank

[TheRobRush]

Error in the image you shared is due to that trying to patch in a table as opposed to a record. If that column is a text columns, boolean, etc anythign that should only have one piece of data in it, you need to filter that table result down to a record, or concat it. If that column is somethign like a choice column and you want to store everything in the returned table into it you will need to structure that data properly. CHoice columns are {Value:Info}, {Value:Info}, {Value:Info} a table column and the choice in it is always stored in a column titled value. It is best to structure that data properly in advance, but if that is not possible you should probably beable to do some sort of forall there, So Like for example Location: ForAll(referencetable As structure, {Value:structure.ColumnYouWant, or whatever it suggests you place behind that})

[FrankKelp]

Hi @TheRobRush , 

So I thought I had my collection in the proper structure now, but maybe I am still not getting it. 

It was this: 

I managed to get it to just show the individual value: 

However, still receiving errors that it cannot access a non-record value in this context. 

I tried concat with transfer field to see if it worked, like so: 

ForAll(
    galInternalItem.AllItems,
    Collect(
        colInternalItemTransfer,
        {
            Transfer: Concat(
                Filter(
                    Transfers,
                    Transfer = varTransferGUID.Transfer
                ),
                Transfer
            ),
            Account: cmbInternalAccount.Selected.Account,
            Item: GUID(lblInternalItemGUID.Text),
            Count: Value(txtInternalItemTransferQty.Text),
            'Source Location': lblSourceLocationGUID.Text,
            Status: 'Status (Transfer Items)'.Active
        }
    )
);

Still won't accept it. 

I don't understand that if the raw values for each row are in the collection why isn't the patch statement accepting it? 

Patch(
    'Transfer Items',
    ForAll(
        colInternalItemTransfer,
        {
            Transfer: ThisRecord.Transfer,
            Account: ThisRecord.Account,
            Item: ThisRecord.Item,
            Count: ThisRecord.Count,
            'Source Location': ThisRecord.'Source Location'
        }
    )
);

Many thanks again, 

Frank. 

[TheRobRush]

Can you send a screenshot of the patch code, as written above, and the error it is showing you?

And also, in the meantime, make a gallery. press the button that does the collect of your data (also before you test this add a Clear(colInternalItemTransfer); before it so that collection doesn't have any leftovers. Then make that gallery's items colInternalItemTransfer and see if it gives you any errors too.

[APowerAppNewb]

Hey RobRush, I am running into a similar problem as the original OP. I have a SharePoint Form that I am customizing in Power Apps. The particular challenge I am running into is trying to add multiple people to a multiperson column. I tried using the default update, but got an error message: "Network error when using Patch function: The requested operation is invalid". From there, I tried to set up a collection to catch the users being added to the combo box as follows:

ClearCollect(
    collectionRL,
    ForAll(
        DataCardValue11.SelectedItems,
        {
            '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
            Department: "",
            Claims: "i:0#.f|membership|" & Email,
            DisplayName: DisplayName,
            Email: 'Email',
            JobTitle: "",
            Picture: ""
        }
    )
)

From there, I tried to use a ForAll in the Update of the Data Card:

ForAll(
    collectionRL,
    {
        '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
        Department: "",
        Claims: "i:0#.f|membership|" & Email,
        DisplayName: DisplayName,
        Email: Email,
        JobTitle: "",
        Picture: ""
    }
)

I've confirmed that the collection is generating a list of information with multiple records. Neither of these solutions are working. For your final recommendation of using the Patch approach, where would I position that? In the OnSuccess option for the SharePoint form? And if it goes there, what would the final code look like? The multi-person column is called RLs.

[TheRobRush · Accepted solution]

@APowerAppNewb not usre what is going on with yours, I created a form in powerapps for a sp list, and also just made a custom spform from the list settings also via powerapps, and in both instances the default form datacard settings for a multipeople picker column patched in people I selected as expected

Not sure why you would need to add /patch/ to the SharePoint form it generates as this form is already setup to add the correct data types to your column, is this to pull the employee additions from somewhere else? I'd tend to keep that to powerapps and not the custom sharepoint form screen myself  (because I don't use sharepoint forms and  they behave differently sometimes from powerapps)