Sometimes it’s hard to come up with a catchy, succinct title for a post…

Segments in the ‘real-time marketing’ segment builder of Customer Insights - Journeys (Dynamics 365 Marketing) are great for executing seriously impressive and complex data queries for Leads or Contacts in Dynamics 365, as well as all the associated interaction data too. It’s not so great at allowing you to interrogate that data to verify your segment and also to understand finer details about the segmented audience.

In a previous post I showed you how to List Segment Members from Customer Insights - Journeys in Power Automate and return the contact/lead GUIDs.

This post goes a step further to turn the GUIDs into data from the contact/lead which can then be used in lots of other useful ways such as building a beautiful custom page to browse and search segment members or export segment members (blog post pending!).

Alternative title: how to query Dataverse with an array of GUIDs

This method will work in any other context where you are trying to query data in Dataverse using an array of GUIDs. It’s not very sexy to look at/visualise, but its seriously impressive!

It starts from a custom page in a model-driven app

The context here in this blog post using a custom page but you can start this from wherever suits you.

Start with a ‘When Power Apps calls a flow (V2)‘ trigger

There are three text inputs that you need to add:
**VirtSegGUID** - the *msdynmkt_virtualsegmentid* column from the *msdynmkt_virtualsegment* table
**UserAzureGUID** - the calling users Azure/Entra ID
**Table** - the table name of the segment members which is *lead* or *contact*

Find the calling user in Dynamics 365

I don’t care much about the row that gets returned, the only reason for this action is to obtain the environment URL to use in the next step. It’s guaranteed that the user will have access to read their own user record so it’s reasonably bomb proof. Add a ‘Dataverse - List Rows’ action.
**Action name:** GetUser
Table name: Users
**Select columns**: systemuserid
**Filter rows**: azureactivedirectoryobjectid eq '[UserAzureGUID]' and azureactivedirectoryobjectid ne null
**Row count**: 1

Retrieve segment member GUIDs

We can query the segment to return the GUIDs of all the segment members using a HTTP Request. Add a ‘HTTP with Microsoft Entra ID (preauthorized) - Invoke an HTTP request’ action.
**Action Name**: GetSegmentMembers
**Method**: POST

Url of request:

https://@{urihost(body('GetUser')?['@odata.context'])}/api/data/v9.0/msdynmkt_MembersList

Body of the request:
{

"SegmentId": "@{triggerBody()['text']}"

}

If you haven’t used this connector before you will need to create the connection, using your dynamics 365 base URL in both boxes like this.

Initialize an array variable

We will use this to capture the data to return at the end of the process. Add a ‘Variable - Initialize variable’ action:
**Name**: MemberArray
**Type**: Array

Define your data query

These two compose steps are used to define the contact or lead specific data elements that you want to retrieve. Note that if its a data item that has the same name on the contact or lead you don’t need to add it here as we will insert it only once further down in the flow. So any unique fields to lead or contact that you want to return need to go here in FetchXML data query format. The queries below use only out of the box fields so this should help to get you started.

Add a ‘Data Operations - Compose’ action:
Action Name: LeadAttributes
Inputs:

<attribute name="subject" />
<attribute name="companyname" />
<attribute name="parentaccountid" />
<attribute name="campaignid" />
<attribute name="leadsourcecode" />
<attribute name="industrycode" />
<attribute name="statuscode" />
<link-entity name="account" from="accountid" to="parentaccountid" link-type="outer" alias="ParentAccount">
<attribute name="address1_country" />
<attribute name="transactioncurrencyid" />
<attribute name="industrycode" />
</link-entity>

Add another ‘Data Operations - Compose’ action:
Action Name: ContactAttributes
Inputs:

<attribute name="parentcustomerid" />
<attribute name="originatingleadid" />
<attribute name="preferredcontactmethodcode" />
<link-entity name="account" from="accountid" to="parentcustomerid" link-type="outer" alias="ParentAccount">
<attribute name="address1_country" />
<attribute name="transactioncurrencyid" />
<attribute name="industrycode" />
</link-entity>

Chunk it

I loathe to have to use the function chunk() because it causes some seriously visceral reactions in my mind associated to this (for reference see ‘blow chunks’). Anyway, full credits to George Doubinski for working this bit out, and using chunk()…

The reason for chunking here is because we are going to dynamically create a FetchXML query to retrieve the rows from Dataverse using the operator ‘in’ on contactid or leadid. There is a limit to the character length of a FetchXML query of 2 KB aka 32,768 characters so the chunk method ensure we don’t blow that limit. Based on your query as to other data attributes being requests you may need to adjust this limit to be bigger/smaller.

Add a ‘Control - Apply to each’ action.
Action Name: ChunkIt
Select an output from previous steps:

chunk(json(body('GetSegmentMembers')?['ResultText'])?['members'],400)

Retrieve member data using the fanciest FetchXML query you have ever seen

By using dynamic inputs we can make the action work for both lead and contact in a single swoop without any branching. We use the table name from the flow inputs, attributes from the compose steps above and ‘chunks’ of GUIDs from the segment membership HTTP request. Inside the Apply to each action, add an action ‘Dataverse - List rows’:

Action Name: ListRows
Table name:

concat(triggerBody()['text_2'],'s')

Fetch Xml Query:

<fetch>
      <entity name="@{triggerBody()['text_2']}">
<attribute name="firstname" />
<attribute name="lastname" />
<attribute name="emailaddress1" />
<attribute name="fullname" />
<attribute name="jobtitle" />
<attribute name="ownerid" />
<attribute name="msdynmkt_marketingformid" />

        <filter type="or">
            <condition attribute="@{triggerBody()['text_2']}id" operator="in" >
                 <value>{@{join(item(), '}</value><value>{')}}</value>
            </condition>
          </filter>
@{if(equals(triggerBody()['text_2'],'contact'),outputs('ContactAttributes'),if(equals(triggerBody()['text_2'],'lead'),outputs('LeadAttributes'),null))}
      </entity>
</fetch>

Merge your chunks

After each Dataverse query we need to combine the current items in the MemberArray with the additional chunk of data. Inside the Apply to each action, add an action ‘Data Operations - Compose’:
**Action Name**: Merge
**Inputs**: (below)

union(variables('MemberArray'),outputs('ListRows')?['body/value'])

Set the MemberArray value

Update the MemberArray variable with the new combined set of data, make sure you use ‘Set’ not ‘Append’. Inside the Apply to each action, add an action ‘Variable - Set variable’:
**Name**: MemberArray
**Value**: outputs('Merge')

Respond with data

Finally, we need to send the data back to our custom page (or wherever else you want to send it), using a ‘Response’ action gives us the ability to return an array rather than individual outputs of text/number/boolean. It is **really important** that you define the Response Body JSON Schema otherwise the Custom Page/Canvas app does not recognise the data as being available and you won’t be able to build a gallery to display the data. The response body needs to include the lead and contact data attributes, it’s ok if the data isn’t returned it will just show as blank. Remember if you add attributes into the FetchXML, you need to also define them here so that they are available as data.
**Status Code**: 200
**Body**: variables('MemberArray')
**Response Body JSON Schema**: (example below)

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "contactid": {
                "type": "string"
            },
            "leadid": {
                "type": "string"
            },
            "firstname": {
                "type": "string"
            },
            "fullname": {
                "type": "string"
            },
            "_ownerid_value@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "lastname": {
                "type": "string"
            },
            "emailaddress1": {
                "type": "string"
            },
            "jobtitle": {
                "type": "string"
            },
            "preferredcontactmethodcode@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "industrycode@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "_parentcustomerid_value@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "_transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "_originatingleadid_value@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "ParentAccount._transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "ParentAccount.industrycode@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "ParentAccount.territoryid@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "ParentAccount.address1_country": {
                "type": "string"
            },
            "subject": {
                "type": "string"
            },
            "companyname": {
                "type": "string"
            },
            "statuscode@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "_msdynmkt_marketingformid_value@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "leadsourcecode@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "_campaignid_value@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            },
            "_parentaccountid_value@OData.Community.Display.V1.FormattedValue": {
                "type": "string"
            }
        }
    }
}

Note here the annotation is different depending on the field type to allow you to get back the human readable text versions of the data

Plain text attributes format:
"jobtitle": {
"type": "string"
}

Lookup format:
"_parentcustomerid_value@OData.Community.Display.V1.FormattedValue": {
"type": "string"
}

Option set/currency/date format:
"preferredcontactmethodcode@OData.Community.Display.V1.FormattedValue": {
"type": "string"
}

It’s a chunky wrap!

That’s it, no more mentions of chunk for a while. As mentioned, the why/more visually appealing context for this can be found here.

Here is the flow layout top to bottom below for reference. Drop a comment below if you have an questions!

Ps. Please send all complaints about the fact I’m using the classic Power Automate designer to cleo@ameyholden.com

Retrieve Member Data from Customer Insights - Journeys Segments in Power Automate

Alternative title: how to query Dataverse with an array of GUIDs

It starts from a custom page in a model-driven app

Start with a ‘When Power Apps calls a flow (V2)‘ trigger

Find the calling user in Dynamics 365

Retrieve segment member GUIDs

Initialize an array variable

Define your data query

Chunk it

Retrieve member data using the fanciest FetchXML query you have ever seen

Merge your chunks

Set the MemberArray value

Respond with data

It’s a chunky wrap!

Amey ‘anything but code’ Holden

Dynamics Marketing & Power Platform Experience Specialist

Microsoft Most Valued Professional

Retrieve Member Data from Customer Insights - Journeys Segments in Power Automate

Alternative title: how to query Dataverse with an array of GUIDs

It starts from a custom page in a model-driven app

Start with a ‘When Power Apps calls a flow (V2)‘ trigger

Find the calling user in Dynamics 365

Retrieve segment member GUIDs

Initialize an array variable

Define your data query

Chunk it

Retrieve member data using the fanciest FetchXML query you have ever seen

Merge your chunks

Set the MemberArray value

Respond with data

It’s a chunky wrap!

Add the Company Name Lookup to a Marketing Forms in Customer Insights Journeys

Browse and Search Segment Membership in Customer Insights - Journeys

Amey ‘anything but code’ Holden

Dynamics Marketing & Power Platform Experience Specialist

Microsoft Most Valued Professional