Patching a collection to sql, appending rows, I need to update

Question

I have a SQL Server table csv_data_tbl, and a collection colCommon which is collected at the beginning from the same table.

A gallery allows users to modify the collection using input text fields. I want to patch this modified collection to same SQL Server table.

I am using a button with following onSelect code:

Patch(CSV_DATA_TBL, colCommon)

But it is appending the records to the SQL Server table, so I get duplicate records with user modified data.

Is there any better solution there to update the SQL Server table from my collection?

Thanks

score 0 · Answer 1 · answered Sep 09 '21 at 00:31

You can use a ForAll function which will loop through all of the items in your collection, and send the updates to the database using the Patch function. For example, if your table csv_data_tbl has a primary key column called 'ID', and the user can update the columns 'Field1' and 'Field2', you can use an expression like the following:

ForAll(
    colCommon As coll,
    Patch(
        CSV_DATA_TBL,
        { ID: coll.ID }, // (*)
        {
            Field1: colCommon.Field1,
            Field2: colCommon.Field2
        }))

(*) Typically we would need a full record to be updated as the second parameter of the Patch function - but in the case of a SQL Server table, all we need is the primary key, and it will be able to match it. If you were to retrieve the entire record, the second parameter would be something like LookUp(CSV_DATA_TBL, ID = coll.ID).

Thank you So much @carlosfigueira, your code helped me. – Chetan Wabhale Dec 02 '21 at 11:25 — Chetan Wabhale, Dec 02 '21 at 11:25

Patching a collection to sql, appending rows, I need to update

1 Answers1