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.
Which is extremely useful! NOT
I added another action for the debugging purpose:
And I am using Compose here to inspect the Output from Retrieve WOs action.
{
"@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.
Something Awesome About ... Power Platform and BizApps..Dev and Ops 
Hi Olena, nice article. 🙂
Am just wondering why you wouldn’t use the ‘Top Count’ field in the list records action? Set that value to be 1, and it will only return 1 result anyway.
You can then use the ‘order By’ field to set the order of results returned, to get the oldest, newset, largest, smallest etc if you need specifically to choose the most recent record created etc.
LikeLike
Hi Matt,
Thank you for your comment.
I do use top 1 )) You still have to deal with the list with just one item in the JSON result.
Something like : [
{
“Items”: “test”
}
]
Which automatically make it use Apply to Each to access the item properties which is annoying as hell.
In my old Flows I returned one item them used Apply To Each and didn’t bother. But it looks much cleaner this way. This is how we would do it in the code for RetrieveMultiple. We would retrieve something we know is the only item them take [0] form the list.
LikeLike