Patch multiline column with collection

[DDM]

Hey Community, 

I'm having a bit of an issue when using Patch to update my SharePoint list.  I submit form which creates the item in my list. Then I  patch two multiline columns (see code snippet below) however when I patch, the columns with the code below  it creates the item but then creates a number of subsequent rows in my SharePoint list (depending on the number of records in my collection).

If I change Defaults for Last I only have one row however the column is only updated with the last record added to the collection.

Can anyone help please?   Many Thanks DDM

ForAll(
colMaterialNumber,
Patch(
'Freight Requests',
Defaults('Freight Requests'),
{SAPMaterialNumber: SAPMaterialNumber}
)
);
ForAll(
colMaterialDescription,
Patch(
'Freight Requests',
Defaults('Freight Requests'),
{SAPMaterialDescription: SAPMaterialDescription}
)
);

[RandyHayes]

@DDM 

First, your ForAll's are all backward.  They are not For Loops like in a development language - which PowerApps is not.

But, what is it that you are trying to do?  What is the multiline column and what is in the collection and where did it come from?

In general, if you are using a form and these multi-line columns are part of the form, then you should not be using a Patch statement, it should all be in the form to submit.

Please clarify the above.

[DDM]

@RandyHayes  Hi Randy, thanks for your response.

Ok so I have two dropdowns that are fed from one datasource. The datasource has two columns. Material number and material description. When i select the material number the corresponding material description is selected. This datasource is used to feed the two dropdowns and is not updated. 

I realise that I could use a choice column but how then would I ensure that when selecting the material number i get the corresponding material description.

SAP Material Number

colSAPMaterialData.SAPMaterialNumber

SAP Material Description 

Filter(colSAPMaterialData, SAPMaterialNumber = txtSAPMaterialNumber.Selected.SAPMaterialNumber)

Using filter I return only the Material Description that corresponds to the Material Number.

For 90% of cases I only need to add one product and in this case the Patch would work (see snippet below)

Patch(
    'Freight Requests',
    Last('Freight Requests'),
    {
        SAPMaterialNumber: gblSAPMaterialNumber,
        SAPMaterialDescription: gblSAPMaterialDescription
    }
);

This works for single products however I realised there are rare cases where I need to add more than one product so I created a collection which is where I hit the brick wall. I am struggling to "loop" through the collection and add all records to the multiline column.  If my approach is wrong I am all ears after all, I am here to learn.

Hope that makes sense, or at least gives a little more meat to the bones.

Thanks

[DDM]

Still banging my head against the wall with this one.

Why can I not patch the two columns (multiline) with the contents of my collection ?

Patch(
        'Freight Requests',
        Last('Freight Requests'),
        {
            SAPMaterialNumber: colMaterialNumber,
            SAPMaterialDescription: colMaterialDescription
        }
);

I am presented with the following error.

Can anyone point me in the right direction ..... Many Thanks

[v-yujincui-msft · Accepted solution]

Hi @DDM ,

It is due to data type mismatch. Collection is a table, we could not save table to a text field.

Maybe you could try the following formula:

Patch(
        'Freight Requests',
        Form1.LastSubmit,
        {
            SAPMaterialNumber: Concat(colMaterialNumber,Value,";"),
            SAPMaterialDescription: Concat(colMaterialDescription,Value,";")
        }
);

Best Regards,

Charlie Choi

[DDM]

Hi Charlie,

Awesome!!

Had to make a minor change because of my columns in my collection but that was the solution. A super, if somewhat frustrating learning exercise.

Patch(
        'Freight Requests',
        Form1.LastSubmit,
        {
            SAPMaterialNumber: Concat(colMaterialNumber,SAPMaterialNumber,";"),
            SAPMaterialDescription: Concat(colMaterialDescription,SAPMaterialDescription,";")
        }
)

THANK YOU!!