Dynamics 365 for Operations and Logic Apps - How to get one record if you do not know the unique key
nspiteri
545
Like
Share
ReportLogic Apps and Dynamics 365 for Operations
Logic Apps have introduced a new API for Dynamics 365 for Operations. This is very handy and offers you functionality to do several data operations within Ax with little coding requirements:
Excluding 'Execute action' and 'Get List of Entities', all the remaining functions use Data Entities (which are exhaustively described in: https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/data-entities/build-consuming-data-entities)
One common task you will want to do is to read a record. For this, Microsoft has provided 2 methods: Get records and Get a record.
Get a record is very straightforward - it takes 3 parameters: the D365 instance, the Entity and the Unique Identifier
Get Records
If you do not have a unique key for the record or you want to fetch multiple records, you will be using the Get Records function.
In this case, by default you select just the instance and entity name. However there are several other parameters in the Advanced Options:
These are all based on the uri conventions for calling oData (http://www.odata.org/documentation/odata-version-3-0/url-conventions/) however in this case the api fills the query strings for you.
For instance:
If you want to query sales orders with for customer 'DE-001' you would type:
OrderingCustomerAccountNumber eq 'DE-001'
as opposed to
$filter=OrderingCustomerAccountNumber eq 'DE-001'
OrderingCustomerAccountNumber eq '<variable>'
as opposed to something like
"OrderingCustomerAccountNumber eq '" + <variable> + "'"
Getting one record without knowing the unique key
I have had a case recently where I was developing an interface between one system and D365 for order processing. In this case we have matched the customer id in one system with the other using the External Codes functionality in Ax.
I then developed a logic app to flow the orders from the source to D365.
In the execution of the Logic App I had to match the external codes to fetch the customer Id from Ax. I started by creating a data entity with External Code Values and External Codes and published it as oData in my Ax instance.
My next step was to read the data entity. As I had no primary key I could not use Get A Record so I had to use Get Records.
I created my function as follows (note that I'm using Top Count 1 to only fetch one record):
This will still however result in an array - when you are only concerned with the first value
"body": {
"@odata.context": "https://dynamicsax-logic-cp-northeurope.logic-ase-northeurope.p.azurewebsites.net/$metadata#datasets('norbertdevd38395065d5b705ddevaos.cloudax.dynamics.com')/tables('ExternalCodeValues')/items",
"value": [
{
"@odata.etag": "W/\"JzEwMjcxMDE1NDcsNjg3MTk0Nzk3Mzkn\"",
"ItemInternalId": "17ca8961-0a5c-462e-adb8-21cf5527bf0b",
"ExternalCodeDescription": "",
"ValueAlias": "ABC",
"Value": "ABC",
"ExternalCode": "FP code",
"dataAreaId": "gbsi",
"VendorAccount": "",
"Company": "gbsi",
"CustomerAccount": "GB_SI_003"
}
]
}
The following function will return the CustomerAccount for the first value in the array (arrays start at 0)
body('Get_External_Code_Value_for_Customer')['value'][0]['CustomerAccount']
But what this will give us an error (cannot be evaluated because array index '0' cannot be selected from empty array) if nothing is returned i.e.
"body": {
"@odata.context": "https://dynamicsax-logic-cp-northeurope.logic-ase-northeurope.p.azurewebsites.net/$metadata#datasets('norbertdevd38395065d5b705ddevaos.cloudax.dynamics.com')/tables('ExternalCodeValues')/items",
"value": []
}
This can be handled by using the ? operator which allows referencing a null property without creating a runtime error. So we adjust our function to say:
body('Get_External_Code_Value_for_Customer')['value']?[0]?['CustomerAccount']
If we want to create a condition flow in a LogicApp however we cannot use a null value, we need to use an empty string (''). Luckily the function 'coalesce' resolves this issue by returning the first non-null object from a list of arguments. So we again change our function to:
coalesce(body('Get_External_Code_Value_for_Customer')['value']?[0]?['CustomerAccount'],'')
Further reading on the topic of functions can be found at:
https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language
*This post is locked for comments