Merge multiple records in one Collection

[J0el]

Hi there,

I have multiple screens, each screen containing a form with various columns from the same SharePoint List.

Currently, these are all patched together and submitted at the end.

I want to provide offline functionality, by saving each form screen's values into a collection. However, when progressing to the next screen, a new entry is added into the collection, even though they have different column names. In the simple example below, hopefully you can see what I mean. I want to move everything into one record.

The reason is so that the user is able to retrieve the data they've submitted so far, after spending say 20 minutes filling it in and then potentially losing connection.

Thank you in advance!

[BCLS776]

Are you using two separate Collect() statements to build these records? Collect() will always create at least one new record, so we need to consider another option:

1. Wait until all the info from Screen 2 is filled out and then Collect() the whole works at once
2. Collect() the info from Screen 1, and then use a Patch() to update the incomplete columns from Screen 2

If you share some more about how your app functions, we may be able to share some other ideas too.

Hope that helps,

Bryan

[v-bofeng-msft]

Hi @J0el :

You could use Patch() to merge two records:

Patch(Record1,Record2)

I think this link will help you a lot:

Merge records

Best Regards,

bof

[J0el]

Hi there,

Thank you for your reply. I need option 2 of your suggestion. However, there are 4 input screens in total. So will need to patch accordingly. The problem with this is, when the user hits the back button, then clicks next; it will submit again as a new record in the Collection.

Currently the app functions as follows:

-User fills in 4 screen form, which comprises of four form components, one for each screen. These are all patched at the end, and submitted to a SharePoint List. This form is filled in hundreds of times a day at many different sites (I have clean-up routines in place). Some sites have poor connectivity, and can sometimes mean a user has lost the data before they've had chance to submit. So I wanted to use Collect and a Gallery to be able to restore a part-filled form, then submit at a later time, when connection is restored.

[BCLS776 · Accepted solution]

While @v-bofeng-msft has given you a good way to merge two specific records, I think you are after the ability to update a specific record whenever you need. The version of Patch() you want to use in this case is:

Patch( DataSource, BaseRecord, ChangeRecord1)

DataSource is straightforward - reference your collection

ChangeRecord1 will be whatever update you wish to make to that record, and you can change as few or as many fields as you like

BaseRecord is a little trickier - we need to identify the specific row you want to update. You can do this with a Last(myCollection), however, you must be 100% sure you want to update the last row in the collection whenever you reference it that way. The better reference is to use a LookUp() on a unique ID column that will find your desired record.

Example:

Patch(myCollection, LookUp(myCollection, ID = 12345),
    { 
        Description: "Complete",
        Comments: "Wonderful"
    }
)
        

This Patch() finds the record in the collection with ID = 12345 and then updates only two columns, the Description and Comments columns.

An important note: Patch() won't create columns in your collection and will throw an error when you try to do so. I suggest you use Collect() to create a record(s) with all the columns you might need, and then Patch() afterwards to update specific records. If you think about how your user navigates the screens, it may not matter much if your Patch() is executed multiple times -- it will simply update the same record with the same updates.

Hope that helps,

Bryan