Logic Apps upsert logic!

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.

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

Conclusion

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!
Toon