Data Flow to Product Entity

[irobba]

I'm trying to set up a Data Flow to syncrhonise Product data from our own SQL Database in to the Product Entity for use in Dynamics. I believe I'm most of the way but hit an error when actually running the flow - "The unit schedule id is missing"

Now I understand that this is to do with setting the Unit Group and Default Unit as required fields for a Product which is fine but those fields don't appear on the Product Entity on the load page of the Data Flow editor so I'm not sure how I can map these through. 

Is there something else I need to do to make these fields available to the Data Flow, or is there some way I can configure a default for these values so that they are not required in the flow itself?

Any help with this would be greatly appreciated. 

Thanks

Ian

[EricRegnier]

Hi @irobba,

It's a limitation of Dataflows that lookup data type fields cannot be mapped. Workarounds are:

1. Create a Single Line of Text field and populate the value there. On create/update, set the lookup field based on that text value with Power Automate or Plugin.
2. If need to set default values, can you create a sync Plugin on Pre-Operation to set the values. A Power Automate or Workflow won't work since they run post operation.

Hope this helps!

[HSheild · Accepted solution]

Hi @irobba 

You can map lookup fields in dataflow, it's just not documented anywhere.  @EricRegnier FYI.

It is possible to import data into the Product entity with dataflow but you need to set up some Alternate Keys on your entities in CDS first.

1. Set up Alternate Keys on both the  Unit Group and Unit entities in CDS - I used the Name field as the alternate key as in my situation it will be unique.  Otherwise create a new field on these entities - you will probably have an ID field or something similar for these entities in your source SQL database
2. Set up an Alternate Key on the Product entity - this enables the dataflow to determine whether data should be updated or created when a refresh is run
3. Create your dataflow.  When you get to the mapping stage you will see that the Default Unit and Default Unit Group (DefaultUoMSchedule) are now visible (with the Alternate Key field name appended to the end) and available to map to.

I hope this makes sense.

-----
 
If this post has answered your question please consider it for "Accept as Solution" or if it has been helpful give it a "Thumbs Up".

[EricRegnier]

@HSheild, so what makes the lookup fields visible on Dataflows? The alternate keys is a better workaround to map the fields as well! But doesn't make lookup field available in the mappings... 

@irobba, I prefer the alternate keys solution than my workaround (#1) since it involves no code, but if you some reason you can't pass these values from the source than you'll need to see them at the destination, so you might be stuck with #2

[HSheild]

Hi @EricRegnier,

I think what happens is that the Alternate Keys enables the lookup fields to appear in the mapping for the dataflow.  For example, take a look at the Product entity and the name of the lookup fields to Unit and Unit Group.

Compare the lookup names with the destination lookup fields that are available in the dataflow mapping.  They are the same names but with "." and the Alternate Key name appended to the end.  It looks like the Alternate Key is required to enable the dataflow to be able to support a lookup.

[irobba]

AWE - SOME! Thanks @HSheild , that did the trick perfectly and all my products are now syncing wonderfully. 

Thanks for your input as Eric, made sense but as you say - much prefer the code free solution when working in Power Apps.

Thanks so much again! These are exactly the sorts of magic tricks I come to wizard school for! 🙂

Ian

[DecSft]

@HSheild That is a really great solution.  Thank You

[crhambrick]

I have not gotten this to work.  So I'm missing something.

First, @HSheild what Key are you creating in your listed stop 2?  (on the Product table)?

I created a Key on tables Unit Group and Unit using the Name field for each since that's what we're passing in.

The dataflow does not create new Product records.  To test it I made sure it writes the data successfully to a new table.  So I'm assuming it's something within this lookup issue.

[crhambrick]

Through further troubleshooting I realized the failed notice was a link and had some information to go on.

It turns out that having the same Base Unit name was causing the Key creation to fail.

So that at least sort of solves my issue but now I have to figure out a work-around because:

OOB there is a Unit Group with associated Units one of which is "foot".

We sell bulk cable and therefore need a Base Unit named "foot".  The Key doesn't like duplicates and of course the Name would be duplicated so we have to figure something else out.

I guess an easy answer might be to use "foot" for one of them and "feet" for the other.  I hate having incongruous nomenclature so hopefully the OOB Unit can be the one I can change.

[HSheild]

Hi @crhambrick ,

First of all, yes, I created a Key for the Product Table on the Product Number field which is used in the Alternate key field at the top of the Field Mapping page (see red circle in screenshot below).  This determines whether a new product should be created (if the key value does not already exist in the database) or an existing product should be updated (if the key value does already exist in the database) .

For your issue with the key on the Unit Name field.  You don't have to use the Name field if it is not going to be unique.  Create a custom field or use another field on the Unit table that you know will be unique.  Uniqueness is a mandatory requirement for creating a Key.

Hope this helps and makes sense