Using upsert in power fx with multiple records

There's a lot of good content to be found out there regarding Power FX and the different functions it has, but one I struggled to find (blame my lack of google-fu) was how to do upsert requests with multiple records.

tl;dr

For those who don't need/want any more context, here's an example on patching new and updated records using a defined collection of contacts:

ClearCollect(myContacts,

Contacts@{

    'Last Name': "Wick",

    'First Name': "John",

    Contact: GUID("4d751116-985d-4fa2-ba96-cdf70511de13")

},

Contacts@{

    'Last Name': "John",

    'First Name': "Little",

    Contact: Blank()

});

Patch(Contacts, myContacts);

 

And here is one generating the records to be updated in the patch statement:

ClearCollect(myContacts,

    { GivenName: "Michael", Surname: "Bolton", Id: "a46a84cf-df57-48de-a418-a7b937a91363"},

    { GivenName: "George", Surname: "Michael", Id: Blank()}

);

Patch(Contacts,

    ForAll(myContacts,

        Contacts@{

            'First Name': ThisRecord.GivenName,

            'Last Name': ThisRecord.Surname,

            Contact: If(IsBlank(ThisRecord.Id), Blank(), GUID(ThisRecord.Id))

        }

    )

)

 

 Context/explanation

Say we have a requirement where it should be able to add and edit multiple contacts related to a customer from the same screen. What is often done (based on my googling) in this scenario is create one collection for new records and one for updated, then loop over them performing a patch for each of them, effectively something like this:

ForAll(

    newContacts,

    With(

        { CompanyId: ThisRecord.Company, FirstName: ThisRecord.GivenName, LastName: ThisRecord.Surname},

        Patch(Contacts, Defaults(Contacts), { 'First Name': FirstName, 'Last Name': LastName, 'Company Name': LookUp(Accounts, Account = GUID(CompanyId)))

    )

);

ForAll(

    updatedContacts,

    With(

        { CompanyId: ThisRecord.Company, Id: GUID(ThisRecord.Id), FirstName: ThisRecord.GivenName, LastName: ThisRecord.Surname},

        Patch(Contacts, Defaults(Contacts), { Contact: Id, 'First Name': FirstName, 'Last Name': LastName, 'Company Name': LookUp(Accounts, Account = GUID(CompanyId)))

    )

);

Now this approach definitely works, but there are several things that could be optimized both for performance and for readability. So let's go through each step on how to make it perform better and reduce the formula size by half.

Patch inside ForAll = suboptimal

Using a patch inside a ForAll is a very suboptimal approach. This will send a patch request for each record you are creating or updating, meaning you get X amount of overhead and X amount of latency, where X is the number of records. The Patch function supports a collection to be sent, executing multiple actions in a single request. That means X is 1, no matter how many records you're handling. The result should look something like this:

Patch(

    Contacts,

    ForAll(

    newContacts,

    With(

        { CompanyId: ThisRecord.Company, FirstName: ThisRecord.GivenName, LastName: ThisRecord.Surname},

        { 'First Name': FirstName, 'Last Name': LastName, 'Company Name': LookUp(Accounts, Account = GUID(CompanyId)))

    )

)

Note! This change means we no longer need the With function, as we won't have issues with ThisRecord references inside a lookup field.

Using Lookup for parent company

Lookup is not an expensive operation, but in this case you're running it for each record. Caching will help but you run the risk of doing X lookups for each operation. There are two ways you can resolve it for this use, one is to store the lookup in a variable, since you are adding and updating contacts for 1 company. The other is to generate your own reference, like so:

{ 'First Name': FirstName, 'Last Name': LastName, 'Company Name': Accounts@{Account: GUID(CompanyId)}})

Using separate collections and patches for updated and new records

Patch supports upsert, also known as "update or insert", which means it will try to update if it exists, or create it if it doesn't. This allows you to put both new and updated records in the same request. For new records you have 2 choices for how you want to handle the record id, one is passing a blank value which will make the system generate an Id for you, or you can create a new Guid and pass that. Either is fine, but if you pre-generate you can chain requests without checking the return value for records created.

If we apply all the modifications, including the removal of the lookup record, we are left with this (added more line breaks for readability:

Patch( // outer Patch, to reduce number of requests

    Contacts, // Table to update

    ForAll( // loop over collection

    myContacts, // collection name

        Contacts@{ // set record type, otherwise treated as one object

            Contact: If(IsBlank(ThisRecord.Id), Blank(), GUID(ThisRecord.Id)), // if id is missing set blank for system generated

            'First Name': ThisRecord.GivenName,

            'Last Name': ThisRecord.Surname,

            'Company Name': Accounts@{Account: GUID(ThisRecord.Company)}} // generate reference without lookup

    )

)

 

Hope it was useful!