- 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.