Using only the first record of a result set in Logic Apps

Thrives

160

Like ( 0 )

Report

Today a client requested me to provide an endpoint they could trigger to create Trial Keys in their CRM from an external website. They pass in a product identifier (Product ID) and expect the GUID of the Trial Key as a response.

My initial thought was “No Brainer”. I’d create a Logic App (not Flow, see here why) with 4 steps:

HTTP Request – accepting the Product ID.
Dynamics 365 – Get Record, specifying the Product ID as an alternate key.
Dynamics 365 – Create Record, creating the Trial Key record.
HTTP Response – returning the GUID of the record.

Unfortunately I got stuck on the second step…

Getting a record through an Alternate Key

In CRM I defined the Product ID (productnumber) field as an Alternate Key.
Doing this, I thought I could simply use the Get record step as shown below.

Nope, doesn’t work. The Get record step really expects an identifier, so that’s not going to work.
I replaced the Get record step with List records as we have more control over our query.

Brilliant, this works like a charm!
Next obstacle, here we come…

Using only the first record of a result set in Logic Apps

Because the previous step returns an array of records, when using any of the properties returned by the step, Logic Apps will automatically create a For Each container. This is a quite handy feature of Logic Apps… if that’s what you’re looking for obviously.

This has the downside that we can’t immediately return the GUID of the newly created record since For each loops don’t allow HTTP Response steps in them. In our case we’re sure we will always have only 1 record from the list, so how do we avoid this For each loop? It’s quite simple actually.

Because the List records returns an array of objects it is possible to loop over the output. I hear you thinking “This also means it should be possible to get the first item using a ‘[0]’-ish syntax, right”? Spot on. That’s exactly what we’ll do. Before referencing the dynamic values from your List records, hit the Code View () button in the Logic Apps designer. Locate your Create a new record step and add the following.

So basically you just need to map the fields that you wish to use from the first record of the result set manually. The syntax used for this is as follows:

@{body('List_Records')['value'][0]['productid']}

[0]: this is the magic. Here’s where we tell the Logic App to only use the first record of the array.
List_Records: replace with the name of your List records step.
productid: replace with the name of your custom field.

This results in a nice and simple Logic App without the unwanted for each loop.
Finally we can add the HTTP Response step referencing the GUID of the newly created record.

In the end, this is how our Logic App looks.