cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
m_williamson
Frequent Visitor

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

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_companyguidtnm97_contactguid
ABC123
BCD123
CDE456
DEF123
EFG456
FGH789
GHI789
HIJ123
IJK789
JKL789

 

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_tempGUIDtnm97_entityguidtnm97_concat
1123ABC;BCD;DEF;HIJ
2456CDE;EFG
3789FGH;GHI;IJK;JKL

.

My Flow looks like as follows:

m_williamson_0-1629709637964.png

 

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.

2 REPLIES 2
abm
Super User
Super User

Hi @m_williamson 

 

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

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials
m_williamson
Frequent Visitor

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,248)