[SOLVED] Fetch Distinct Contacts from Dataverse List Rows (to Concatenate)

[m_williamson]

Hi,

I have a table in the Dataverse/CDS that contains, among other things, a Company Guid and, for each, there is a Primary Contact lookup to a contacts table.

My table (broadly) looks like as follows:

tnm97_companyguid	tnm97_contactguid
ABC	123
BCD	123
CDE	456
DEF	123
EFG	456
FGH	789
GHI	789
HIJ	123
IJK	789
JKL	789

I am trying to use a Fetch XML query in the List Rows action to get a list of distinct contacts that exist in this dataset, then run an Apply to Each to then concatenate all the Company values into one cell, with a dataset that should look like:

tnm97_tempGUID	tnm97_entityguid	tnm97_concat
1	123	ABC;BCD;DEF;HIJ
2	456	CDE;EFG
3	789	FGH;GHI;IJK;JKL

.

My Flow looks like as follows:

However, when I run my initial XML Query:

<fetch distinct="true" >
  <entity name="tnm97_companies" >
    <attribute name="tnm97_contactguid" />
  </entity>
</fetch>

It fails (Key property 'tnm97_companyguid' of type 'Microsoft.Dynamics.CRM.tnm97_companies' is null. Key properties cannot have null values.) as I don't include the GUID for the Companies table. However, if I do include the GUID as a select attribute, then it returns all the rows, not just the distinct Contact GUID.

How do I bypass this distinct selection, or is there a better way to achieve my aims? Ideally I want to avoid using a variable within the Apply to Eaches as my dataset is 1000s of rows long, so not running concurrency controls would be unideal.

[abm]

Hi @m_williamson 

What's the relationship between Companies and Contacts? Is that one contact can have many companies (1:M)?

Thanks

[m_williamson]

Hi @abm ,

Yes, it's 1:M for contacts.

For anyone else's future reference, I managed to make this work by adding a temporary boolean (tnm97_boolean) in the tnm97_contacts table and:

1. Doing an XML Filtered List Rows action for all tnm97_Companies
2. Apply to Each tnm97_contactguid (can be concurrent)
   1. Update a Row to set this tnm97_boolean to 'true'
3. List Rows for all tnm97_contacts with tnm97_boolean as 'true'
4. Apply to Each row (can be concurrent)
   1. Add a New Row in tnm97_temp with tnm97_contactguid as tnm97_entityguid
   2. List Rows from tnm97_companies where tnm97_entityguid = tnm97_contactguid 
   3. Apply to Each (cannot be concurrent)
      1. Get a Row by ID from tnm97_temp (to get latest version)
      2. Update a Row for that tnm97_temp table row, as such:
         'tnm97_concat;tnm97_companiesguid' (to do the CONCAT)
   4. Update a Row in tnm97_temp for that with an expression on the tnm97_temp row to remove the trailing semicolon:
      'last(split(tnm97_concat, ';'))' 
   5. Update a Row for tnm97_contacts to reset tnm97_boolean as 'false'

Et voila. It's not the cleanest, and rather convoluted. But it does the job.

Thanks.

[maxpower45255]

@m_williamson I'm glad you got your problem solved. But it boggles my mind at how many hoops one has to go through to find distinct values in Power Automate/Dataverse using the low-code no-code approach. Microsoft has to find a better way. This can be accomplished in one step in sql (select distinct(column) from table x). We need something similar in the "List rows" action. I hope Microsoft is listening and monitoring these posts. 

If anyone has found a better solution, please post it here. 

thanks. 

[takolota]

Seems like much of the original request could be done more efficiently with some Select actions, union( ) expressions, & join( ) expressions.

But if it is something like the Distinct function you want, there is a template for that: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Get-Distinct-Records/td-p/2191533