Translating a GUID to a text value, from an API response in a Power Automate Flow

Question

I'm using MS Automate to solve an integration challenge between two systems we use in our Project Management lifecycle. I have a custom connector written by the vendor of System A which allows me to create a Flow in MS Automate which is triggered when a record is Created or Updated.

So far, so good. However, the method in the connector provided by System A returns the new or updated record containing a number of fields which contain value GUIDs as the fields are 'choice' type fields e.g. Department, Status etc. What I end up with is a record where Status = "XXXXXX-000000-00000-00000" etc. The vendor also provides a restful API endpoint which I can query, which returns a JSON collection of fields, which include a 'Choices' section for each field of this type which is a standard JSON which looks like:

    {
      "Id": "156e6c29-24b3-4413-af91-80a62a04d443",
      "Order": 110,
      "InternalName": "PrjStatus",
      "DisplayName": "Status",
      "ColumnType": 5,
      "ColumnAggregate": 0,
      "Choices": {
                 "69659014-be4d-eb11-bf94-00155df8457c": "(0) Not Set",
                 "c30c50e5-51cf-ea11-bfd3-00155de84703": "(1) On Track",
                 "c40c50e5-51cf-ea11-bfd3-00155de84703": "(2) At Risk",
                 "c50c50e5-51cf-ea11-bfd3-00155de84703": "(3) Off Track",
                 "6a659014-be4d-eb11-bf94-00155df8457c": "(4) Not Tracked"
    },

Technical problem:

What I have is the GUID of the choice (not the field). I need to take the GUID, in this case "6a659014-be4d-eb11-bf94-00155df8457c" and translate it into "(4) Not Tracked" and store this in a variable to write to a SharePoint list. I need to do this for about 30 fields which are similar in the record.

I've created the flow and the connector has given me the record with a list of fields, some of which contain value GUIDs. I know which fields these are and I have the Display Names of these fields.

I have added a HTTP call to the provided API endpoint (lets call it GetFields), which has returned a 200 response, the body of the response containing a JSON collection of the 50 or so fields in System A.

I can't work out how to parse the body of the response for the GUID I have for each field value and ensure I have the right corresponding text value, so I can then write it to a field variable, and then create a SharePoint record, all wrapped up in an MS Automate flow.

Answer 1

I hope I've understood you correctly but from what I can work out, you want to dynamically select the value of the choice from the GUID you've been provided (by whatever means).

I created a small flow to prove the concept. Firstly, these two steps setup the scenario, the first being the GUID you want to extract the choice value for and the second being the JSON object itself ...

The third step will take the value from the first variable and use it dynamically in an expression to extract that key from the JSON and return the value.

This is the expression ...

variables('JSON')?['Choices'][variables('Choice ID')]

You an see I'm just using the variable in the path expression over the top of the JSON object to extract the key I want.

This is the end result ...