Question regarding dataflows and alternate keys

(0) Share

Report

Posted on by Sandra Soniec

128

Hi -

I need clarification on updating records in CDS using dataflow. Think made bad choice for the primary field of my entity.

The values of my primary field will change values with updates via dataflow over time. I have added and additional field "work item" which has unique values and set an alternate key to this field to ensure uniqueness. This new field is also used as key for field mapping of dataflow to CDS and I set a duplication check rule. Was running a dataflow where the primary field changed values and received the following error message.

Reason: Precondition Failed, Error code: 0x80060892, Message: A record that has the attribute values Work Item already exists. The entity key Work Item requires that this set of attributes contains unique values. Select unique values and try again.

Seems as if CDS tried to create a new record rather than updating the existing one . Is the primary field considered along with the new "work item" field to decide if a record is updated vs. new record created? If yes, do I need to set-up my custom entity again or is there a workaround?

Thanks in advance.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Joel CustomerEffective 3,224 on at

Like (1)

Report
Copy link

Link copied!

Upserts in data flows use alternate keys for matching. If the key is changed then it will not be able to update the record.

if you have changing keys you might want to consider a different integration tool. Even power automate can be used to handle more complex matching logic

a common design pattern is to use a staging table in azure sql and store the GUIDS of the cds records in it, then as your values change in the source update the staging table. That way you can then match precisely based on the Id of the record in cds when something changes

this approach works with power automate, logic apps, SSIS, to name a few

Was this reply helpful? Yes No
Sandra Soniec 128 on at

Like (1)

Report
Copy link

Link copied!

@jlindstrom Thanks for confirming. Will try out power automate then, since the primary field is changing. Have a nice day.

Was this reply helpful? Yes No
Sandra Soniec 128 on at

Like (0)

Report
Copy link

Link copied!

@jlindstrom power automate does not like my Excel workbook that I worked well for the data flow. It connects to a web data source and I am using Power query to transform the data as needed for CDS upload.
When using "List rows present in a table", Power automate does not detect any tables (although formatted as table). No dynamic data available....
Would work it I copy values to a new workbook and format as table but then I am losing the refresh and data transformation.
Any ideas? Thxs in advance!

Was this reply helpful? Yes No
Sandra Soniec 128 on at

Like (0)

Report
Copy link

Link copied!

The issue appears to be a XML-Map that I used in my workbook. This feature is not supported in Excel for web. Tried another workbook with a power query and web resource, which works fine. Any alternative approaches how I could use the XML data source with power automate?

Was this reply helpful? Yes No