Introduction

This blog explains how to use Expand Query (N:1 Relationship) in the Common Data Service List Records Connector.

Steps to be followed:

1. 1. Initialize Variable of type string.
   2. Using Expand in list RecordsExpand Query: cf_Project($select=cf_projectname,cf_projectabbreviation)
      Here cf_Project is the Schema Name of Project Lookup field on location entity. Pass the field names of the project entity that  you want. Here I have passed cf_projectname and cf_projectabbreviation.
   3. Result of list record:
      You can see we get Project value in the below format to use this we have to parse the JSON.

      "cf_Project": "{\r\n \"cf_projectname\": \"Client Commercial\",\r\n \"cf_projectabbreviation\": \"CC\",\r\n \"cf_pmtrackerid\": \"3b576605-9c82-e911-a839-000d3a07f695\"\r\n}"

   4. Retrieving Project Values.
      • Use “Apply to each” to get the value of the Project.
      • Pass “Value” of “List Records” from Dynamic Content in “Select an output from previous steps”
      • Now Set the Value in the “Project” variable which we had initialized earlier.
      • Go To Expression and type:
        items(‘Apply_to_each’)[‘cf_Project’]
        Note: Here “cf_Project” is what we had retrieved in the output of ‘list records’. Make sure you pass this value correctly.
        “cf_Project“: “{\r\n \”cf_projectname\”: \”Client Commercial\”,\r\n \”cf_projectabbreviation\”: \”CC\”,\r\n \”cf_pmtrackerid\”: \”3b576605-9c82-e911-a839-000d3a07f695\”\r\n}”
      • Result of Project Variable:
   5. Parsing JSON.
      • Use “Parse JSON” action
      • Add “Project Variable” in Content from “Dynamic Content”
      • Click on “Generate from sample” to generate JSON Schema
      • Enter sample JSON payload and click on done.

        {
        "cf_projectname": "Test Project",
        "cf_projectabbreviation": "TR",
        "cf_pmtrackerid": "3b576605-9c82-e911-a839-000d3a07f695"
        }

        

      • It will generate a JSON Schema automatically.
   6. Using Project Values.
      • After parsing Json successfully you can use the values of the project entity field.
      • You can use “Compose” to get and check the value.
      • Enter “cf_projectname” in “Compose” inputs.
      • OutputEntire FLOW:
        NOTE:  You can add multiple expand queries in List Record:Query: cf_Project($select=cf_projectname,cf_projectabbreviation),cf_ParentLocationId($select=cf_name)