Saturday, June 3, 2017

Checking for existence of record in Azure Logic Apps

I recently had the need to store data into an Microsoft Common Data Service (CDS) database, and using a Logic App seemed to fit the need in this case.

Project requirements:

- Run the integration (data copy) once per day
- Query Dynamics 365 (CRM) for Project records
- Insert or update into the Common Data Service database, to the Project entity

I'm probably missing something obvious, but that last step was more difficult to figure out than I thought. I created a "For each" to loop through the CRM records and then wanted to query the CDS entity to determine whether or not the record already exists. If the record exists, update it, if it doesn't exist, create it.

To query the CDS entity to determine whether the source CRM record exists, I used the CDS "Retrieve record" action. When running the Logic App, though, when it encountered the condition where a CDS record didn't exist the Logic App stopped processing and set the instance as "Failed".  Not finding a record really shouldn't be considered a failed job, but that's the way Logic Apps handles this condition.

The way Scribe Online handles this type of query is to set a property (e.g., RecordsMatched) to indicate whether or not the record exists. You can use the property's value in IF..THEN formulas to change the runtime processing path.

In Logic Apps, I couldn't find a simple way to set a condition based on whether or not it found the CDS record. The solution I came up with was to check the HTTP status of the query to the CDS. A response of 200 means that the record exists and a 404 means the record does not exist.

For the "IF" statement in the Logic App (after querying for the CDS record), I switched to "Code view" in the Logic Apps Designer and set the expression to the following:

"expression": "@not(equals(outputs('Attempt_to_retrieve_Project_CDS_record')['statusCode'], 200))",

Here's how that change looks in the Designer view:



From there, I added an action to insert the CDS record if it doesn't exist or update it if it does exist.

This solution was confusing because I couldn't find any examples or documentation from Microsoft on how to deal with this common scenario. I found an article on dealing with "exceptions" using the runAfter setting for an action, but checking for a record and not finding it really isn't an exception, it's a simple operation that should, in my opinion, be easier to handle in Logic Apps. Maybe it is and I just missed it. If so, please let me know.

2 comments:

  1. Toon Vanhoutte (Twitter: @ToonVanhoutte) contacted me and said that it's possible to check for the existence of a record in Logic Apps using the "Get List of Records" (set the filter criteria to the record you're looking for) and then with a Condition block with a custom expression like this:

    @equals(empty(body('Get_list_of_records',)['value']), false)

    This is not too much different than the example where I checked for status code 200 or 404, but it's a different approach that makes a bit more sense.

    I still think that Logic Apps should provide a way to do this from the designer since it's a very common scenario and the tool, along with Microsoft Flow, is targeted mostly at those who don't write code and might get lost in the complexity of the underlying workflow definition for an app.

    ReplyDelete
  2. hey, performance wise, i think "get a record" is better than "get records" because when "get records" is used, it is trying to whole table, while "get a record" just query using the primary key.

    ReplyDelete