Blog

Data - How to update a record if it exists, or insert a new record if it doesn't exist

There's sometimes the requirement to create a data entry screen that either updates a record if it exists, or to insert a new record it doesn't exist. This post walks through how we can carry out this task.

To reduce unnecessary duplication in databases, there is sometimes a requirement to 'upsert' a record. That is, to update a record in a data source if it exists, or to insert it if it doesn't.

To demonstrate, let's take the example of a SharePoint list of clients (in practice, this could be any data source). The field that we want to use as a unique identifier is the email address.

On the data entry screen, a user would enter the details for a client, such as name and address. If the email address for the client already exists in the data source, the requirement is to update the current record that already exists. If not, the requirement is to insert the record.

The strategy to perform this task

The strategy that we'll use is to call the patch function. This function accepts three arguments - a data source, the record to add/update, and the data to add.

We'll conditionally amend the second argument to refer to a pre-existing record if it exists, or a new record if it doesn't.

The documentation provides more details on how the patch function works:
https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-patch

Creating the data entry screen

To create our data entry screen, we first add an edit form to a screen. The important step is to set the data source of the form. For this example, we set the data source to the 'client' SharePoint list. The second key step is to set the 'default mode' of the form to 'new'.


Next, we can click the 'edit fields' link from the properties pane to add the required fields to data entry form.

Finally, we can add a button to the screen and add the following formula to the OnSelect property to carry out the upsert operation. 

Patch(Client, 
      Coalesce(LookUp(Client, 
                      Email=DataCardValue33.Text),Defaults(Client)
      ),
      frmUpsertClient.Updates
)
The call to patch includes the following 3 arguments:

  • The data source (Client in this example).
  • The record to add or update. Here, we specify the record by attempting to look up a source record by email address. In this example, DataCardValue33 is the name of the text input control on the email card. The outer call to Coalesce tests the result of the lookup. If this is null, the Coalesce function returns a new record by calling the Defaults function.
  • The fields to add or update. Here, we specify the record data to add or update by referencing the updates property of the form. The form name in this example is frmUpsertClient.
At this stage we can run our app and enter the details of a client. When we click the button, the formula will update the existing record if a matching record exists with the same email address, or it will add a new record if a matching record doesn't exist.

Conclusion

Occasionally, there is a need to create a data entry screen that either updates a record if it exists, or to insert a record it doesn't exist. This post described how to carry out this task by calling the patch function, and conditionally specifying a record to update/ or to add.
Previous
Next
Related posts