Olena's Dynamics Blog (post-AI)

Writing as a human

recent posts

about

#womenintech #mvp #businessapplications #dynamics365 #dynamics365developer #powerplatform #solutionsarchitect #businessapplications #netdveloper #azuredeveloper #lovewhatido #supportwomenintech #blogger #diversity #microsoftdeveloper
How to get it right: “List records” just-first-record lookup value expression for CDS current connector

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.

Posted in , ,

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

  1. Matt Piper Avatar
    Matt Piper

    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.

    Like

    Reply
    1. Olena Grischenko Avatar
      Olena Grischenko

      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.

      Like

      Reply

Leave a comment