Logic Apps upsert logic!

Should we create a record or update it? What constraints define if a record already exists? These are typical questions we need to ask ourselves when analyzing a new interface. This blog post focuses on how we can deal with such update / insert (upsert) decisions in Logic Apps. Three popular Logic Apps connectors are investigated: the Common Data Service, File System and SQL Server connector.

Common Data Service Connector

This blog post of Tim Dutcher, Solutions Architect, was the trigger for writing about this subject. It describes a way to determine whether a record already exists in CDS, by using the “Get Record” action and deciding based on the returned HTTP code. I like the approach, but it has a downside that it’s not 100% bullet proof. An HTTP code different than 200, doesn’t always mean you received a 404 Not Found.

My suggested approach is to use the “Get List of Records” action, while defining an ODATA filter query (e.g. FullName eq ‘@{triggerBody()[‘AcountName’]}’). In the condition, check if the result array of the query is empty or not: @equals(empty(body(‘Get_list_of_records’)[‘value’]), false). Based on the outcome of the condition, update or create a record.

File System Connector

The “Create file” action has no option to overwrite a file if it exists already. In such a scenario, the exception “A file with name ‘Test.txt’ already exists in the path ‘Out’ your file system, use the update operation if you want to replace it” is thrown.

To overcome this, we can use a similar approach as described above. Because the “List files in folder” action does not offer a filter option, we need to do this with an explicit filter action. Afterwards, we can check again if the resulting array is empty or not: @equals(empty(body(‘Filter_array’)), false). Based on the outcome of the condition, update or create the file.

You can also achieve this in a quick and dirty way. It’s not bullet proof, not clean, but perfect to use in case you want to create fast demos or test cases. The idea is to try first the “Create file” action and configure the next “Update file” action to run only if the previous action failed. Use it at your own risk 🙂

SQL Server Connector

A similar approach with the “Get rows” actions could also do the job here. However, if you manage the SQL database yourself, I suggest to create a stored procedure. This stored procedure can take care of the IF-ELSE decision server side, which makes it idempotent.

   @AccountName VARCHAR(50), 
   @AccountEmail VARCHAR(256)
   IF EXISTS (SELECT * FROM Upsert WHERE AccountName = @AccountName) 
      UPDATE Upsert SET AccountEmail = @AccountEmail WHERE AccountName = @AccountName 
      INSERT INTO Upsert VALUES (@AccountName, @AccountEmail) 

This results in an easier, cheaper and a less chatty solution.


Create/update decisions are closely related to idempotent receivers. Real idempotent endpoints deal with this logic server side. Unfortunately, there are not many of those endpoints out there. If you manage the endpoints yourself, you are in charge to make them idempotent!

In case the Logic App needs to make the IF-ELSE decision, you get chattier integrations. To avoid reconfiguring such decisions over and over again, it’s advised to make a generic Logic App that does it for you and consume it as a nested workflow. I would love to see this out-of-the-box in many connectors.

Thanks for reading!

One thought on “Logic Apps upsert logic!

  1. Pingback: Microsoft Integration Weekly Update: Aug 14 | Hooking Stuffs Together

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s