man is trying to roll a large rock uphill
Common Data Service, Flow, Uncategorized

How to get it right: “List records” just-first-record lookup value expression for CDS current connector

Sometimes I use my own blog to find some information which I am keeping forgetting.

I am always forgetting how to do THIS so I keep it here.

Problem: from the list of items retrieved via List records action for CDS current connector i would like to pick up the first item and get the value of its lookup field.

In my case I retrieve Work Orders and I am looking for the OOB Service Account field (msdyn_serviceaccount).

Unfortunately, the result of the action in the All Runs history looks like this.

Result of the List records action for WOs

Which is extremely useful! NOT

I added another action for the debugging purpose:

Use Compose to inspect Output from WO retrieve action

And I am using Compose here to inspect the Output from Retrieve WOs action.

Compose for Output from WOs retrieve history
{
"@odata.context": "https://sas-sandbox.crm6.dynamics.com/api/data/v9.0/$metadata#msdyn_workorders(msdyn_name,msdyn_workorderid,_msdyn_serviceaccount_value,msdyn_serviceaccount,msdyn_serviceaccount())",
"value": [
{
"@odata.type": "#Microsoft.Dynamics.CRM.msdyn_workorder",
"@odata.id": "https://sas-sandbox.crm6.dynamics.com/api/data/v9.0/msdyn_workorders(368b9357-b743-e911-a978-000d3ae0bc37)",
"@odata.etag": "W/\"941825332\"",
"@odata.editLink": "msdyn_workorders(368b9357-b743-e911-a978-000d3ae0bc37)",
"msdyn_name": "WO-1106064",
"msdyn_workorderid@odata.type": "#Guid",
"msdyn_workorderid": "368b9357-b743-e911-a978-000d3ae0bc37",
"_msdyn_serviceaccount_value@odata.type": "#Guid",
"_msdyn_serviceaccount_value": "5b8c464c-a743-e911-a978-000d3ae0f2e2",
"msdyn_serviceaccount@odata.associationLink": "https://sas-sandbox.crm6.dynamics.com/api/data/v9.0/msdyn_workorders(368b9357-b743-e911-a978-000d3ae0bc37)/msdyn_serviceaccount/$ref",
"msdyn_serviceaccount@odata.navigationLink": "https://sas-sandbox.crm6.dynamics.com/api/data/v9.0/msdyn_workorders(368b9357-b743-e911-a978-000d3ae0bc37)/msdyn_serviceaccount"
},
{
"@odata.type": "#Microsoft.Dynamics.CRM.msdyn_workorder",
"@odata.id": "https://xxxsandbox.crm6.dynamics.com/api/data/v9.0/msdyn_workorders(318f4f3a-b443-e911-a972-000d3ae0f413)",
"@odata.etag": "W/\"941825383\"",
"@odata.editLink": "msdyn_workorders(318f4f3a-b443-e911-a972-000d3ae0f413)",
"msdyn_name": "WO-1106061",
"msdyn_workorderid@odata.type": "#Guid",
"msdyn_workorderid": "318f4f3a-b443-e911-a972-000d3ae0f413",
"_msdyn_serviceaccount_value@odata.type": "#Guid",
"_msdyn_serviceaccount_value": "3a3c2a89-c9e6-e711-8138-e0071b67ecd1",
"msdyn_serviceaccount@odata.associationLink": "https://sas-sandbox.crm6.dynamics.com/api/data/v9.0/msdyn_workorders(318f4f3a-b443-e911-a972-000d3ae0f413)/msdyn_serviceaccount/$ref",
"msdyn_serviceaccount@odata.navigationLink": "https://sas-sandbox.crm6.dynamics.com/api/data/v9.0/msdyn_workorders(318f4f3a-b443-e911-a972-000d3ae0f413)/msdyn_serviceaccount"
}
]
}

It’s a fun expression time! We have to refer to the action “Retrieve WOs” output body first.

body('Retrieve_WOs')

And all the retrieved items will be inside “value”: []

It’s going to be the array of items:

body('Retrieve_WOs')?['value'])

And we pick up the first one:

first(body('Retrieve_WOs')?['value'])

And then it’s “msdyn_serviceaccount_value” . Watch out the value bit.

first(body('Retrieve_WOs')?['value'])?['_msdyn_serviceaccount_value']

It’s kind of silly that I still struggle with it. I found a very good post here:

https://grootcrm.net/tip-microsoft-flow-retreive-just-one-record-from-cds/

which describes exactly the same expression and I managed to make mistakes while copying it then modifying. Probably because of the combination of [] and ().

Now I have the place in my blog where I keep it permanently. No more struggle.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s