Using CDS Alternate Keys in Flow

[NewcombR]

Am looking for a way to leverage the Alternate Key feature in CDS.

We have numerous CDS environments.
We have a core set of Contacts that we maintain in one environment. I have setup an alternate key on the Contact entity, and have been able to successfully import from an external source to the environment using the alternate key as a map.

However, we have numerous environments. Rather than having to maintain the contacts in all environments using the same import process, is there a way to use Flow to do this? eg if there is a new contact or a change to an existing Contact in the “Master” environment, a Flow should trigger to make the change in the other environments?

The GUIDS in the different environments are different, so I need to use Alternate key to map. I can’t seem to figure out how to get this working. Any help would be appreciated. Any ideas?

[HSheild]

Hi

You can do this with Flow, take a look at an article I wrote on integration using Flow with Dynamics 365 (same as CDS and uses the CDS connector). Hopefully it helps.

https://dynamicscitizendeveloper.com/2019/06/06/microsoft-flow-csv-integration-with-dynamics-365/

With regards to the Alternate Key and looking up the Contact in the destination CDS, the Get Record action of the CDS connector does support Alternate Keys, just the GUID. You will need to use the List Records action and filter on the Alternate Key. There is a similar example in my article where a Currency lookup is done use the same method.

[NewcombR]

Thanks for your response... but I’m confused. You’re saying the Get Record does work for Alternate Keys, but only for GUID?

The alternate key I’m trying to use is a custom text field/column I added to the Contact entity. I configured the field as an alternate key.

Shouldn’t I be able to do something like this in a Flow Action?


Trigger:
When a CDS record is updated
Entity: Contact
Environment: myEnvironment1

Action
CDS Get Record
Entity: Contact
Environment: MyEnvironment2
ID: MyAlternateKey


In other words, if I don’t know the GUID of the record in the second environment, I should be able to use the Alternate Key using Get Record. Isn’t that the point of the Alternate Key?

Are you saying I need to instead use List Records and add a ODATA filter such as:

MyAlternateKey eq MyAlternateKey

[HSheild]

Hi @NewcombR ,

Sorry, that was a massive typo in my post!  I meant to say... Get Record does not work for Alternate Keys.

You have to use the List Records action as you have described.

[Satish-Reddy]

It's frustrating to know that simple stuff like this not included in the CDS connector.

I proposed an idea in the community. Please upvote. 

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/Using-CDS-Alternate-Keys-in-Flow/idi-p/471843#M...

[v-yutliu-msft · Accepted solution]

Hi @NewcombR ,

Do you want to use your AlternateKey field as the unique value in the action of "CDS Get Record"?

I'm afraid it's not supported to use custom AlternateKey field as the unique value in the action of "CDS Get Record" in flow currently.

In flow, the item identifier of "CDS Get Record" action could only be the record's guid.

Usually, the guid field is like this:

Its data is like this:

 While your Alternate Key field is text type, not the record's guid field.

So in your issue, I think using "CDS Get Record" is not suitable.

I agree with what he said @HSheild .

I suggest you use "List records" atcion instead. Configure List records-Filter Query to get the record.

Since your Alternate Key field is also a unique value in Entity, you could set this field to key.

Please note that since your Alternate Key field is text type, you need to enclose it in single quotes.

I've made a similar test for your reference:

ID is my Alternate Key field, not the guid field. To get the record by using ID field, I create the flow like this:

 On your side, the filter query should be like: AlternateKeyfieldname   eq 'ID'

If the Alternate Key field value exists in environment2, then update record. If not, create a new record.

Here's a similar issue for your reference:

https://powerusers.microsoft.com/t5/Building-Flows/Using-Get-Record-Action-in-Common-Data-Service/td...

Also, if you want the item identifier of "CDS Get Record" action could be the custom field, you could post your idea here:

https://powerusers.microsoft.com/t5/Ideas/ct-p/PA_Comm_Ideas

If many people vote for your idea, this may come true in the future.

Best regards,

Phoebe

[NewcombR]

Thanks everyone.  I will try the suggestions of using list records. 

If I wanted to run the flow to check and update/create (“upsert”) multiple contacts, it seams that I would need to also do the following before the suggestions above:

• List records in the “source” CDS environment
• For each record in the Value list items...
• Get the Record (in source environment environment)

and then apply the logic suggested above, to use List Items in the target environment, update/create condition, etc.  

Let’s say I have about 4K contacts in the Source environment...any concerns about the Flow timing out?  Would there be a way to improve the speed?

[HSheild]

Hi @NewcombR 

Your logic is about right but I wouldn't do the 3rd step as you should already have the source record from the first step.

As for bulk processing 4K records you will need to pay attention to the Power Platform API limits in consideration with other things that are processing data in CDS https://docs.microsoft.com/en-us/power-platform/admin/api-request-limits-allocations

To speed up the processing you can update the Concurrency Control setting of your Apply to each control.  This will process multiple records at once. See the Performance section of this article https://dynamicscitizendeveloper.com/2019/05/23/dynamics-365-integration-with-microsoft-flow/

The good thing about Power Automate is that it is really quick to configure so things like performance are quick to test.

[NewcombR]

@v-yutliu-msft 's suggestions and @HSheild 's recommendations worked like a charm on a series of tests I did, using progressively larger datasets (1, 100, 500).

I had placed a "cap" on the initial list of records from the source environment in the List Records query to Top Count.  

In my final test, I removed the "Top Count" cap... and was attempting to perform a full test with all of our contacts in the source environment.  However, for some reason the Flow completed in about 8 minutes.... but it only used 512 records?  Is there some hidden cap on the number of List Record items that can be processed?

[v-yutliu-msft]

Hi @NewcombR ,

Yes, the list records action has a limit of 512 records by default.

Could you tell me what is your license?

If you have per-user or per-flow license, you could enjoy additional capacity about CDS .(we can increase the limit up to 100000)

Other licenses could only have the limit of 512.

If you have per-user or per-flow license, you could change the limit here:

By default, Pagination is off. Switch it on and adjust the limit.

As you can see above, the maximum limit is 100000( connector attempts to retrieve data in 512 record sets ).

To sum up, if you want to increase the default limit, you need to have premium plan and change the settings.

Here are docs about this for your reference:

https://powerofpowerplatform.com/cds-list-record-action-limitations-power-automate/

https://docs.microsoft.com/en-us/power-platform/admin/powerapps-flow-licensing-faq

(Please notice the P1 and P2 is previous version of premium plan. Currently, we use per-user or per-app plan.)

Best regards,