Dataverse - Import related data - Alternate Keys

[MikeS2023]

Hi All,

I am coming from an MS Access background and trying to figure out the best way to import related data.

I can create tables in Dataverse, create look up columns to link tables and I can create alternate keys to prevent duplicates.

I understand that the primary key is a GUID which is populated in the many table when the header record is selected.

My understanding is that the Primary Name column  from the header table is the column that presents when selecting data in the look up column of the many table.

This is all good if I want to start from scratch and manual enter data and manually link the data via the look up column.

And based on this I can create views with the related data.

However, how would I go about setting up the tables to be able to import the following that automatically creates the lookup.

I have been searching everywhere and can't seem to figure it out. I see a lot of tutorials on line but no one appears to be addressing this scenario which I would have thought would be common.

The columns with red text are the primary keys (Composite)

Purchase Order Header

Vendor Id	Purchase Order	Purchase Order Date	Delivery By Date
V10002	3333333	01-Dec-2023	14-Dec-2023
V10003	7777777	01-Dec-2023	14-Dec-2023

Purchase Order Line Items

Vendor Id	Purchase Order	Item Code	Quantity	Unit Price	Total Line Value
V10002	3333333	ITM11111	10	1	10
V10002	3333333	ITM22222	20	5	100
V10002	3333333	ITM33333	30	10	300
V10003	7777777	ITM44444	40	12	480
V10003	7777777	ITM55555	50	6	300

Vendors

Vendor Id	Vendor Name	Payment Terms
V10002	Apple	30
V10003	Microsoft	30

Please let me know if I need to come at this from a different angle and disregard the use of composite primary keys.

Do I need to create a single column in each of these tables to define the KEY?

Any help will be very much appreciated.

Thanks for your help.

Michael

[Kristian_Cole]

Morning Mike I know what you mean I had the same type of issue recently. For your data structure it looks like there are no GUIDs but general ID's like V000001? Etc. If you imported vendor for example, your vendors dataverse table would generate guids, but...linking them back to joined tables would be painful however you do it.

If it were me I would generate guids in some way like here:

https://stackoverflow.com/questions/7031347/how-can-i-generate-guids-in-excel

Get my vendors into excel, add column for vendorGuid, generate guid for each vendor and import into dataverse. Then in excel for the other joined tables use vendor as a pivot table and attempt to link it altogether.

There may be a better way. I offered the guid way because it just makes for better relationships between tables.

[MikeS2023]

Thanks for taking the time to help me out Kristian. I really appreciate it.

I was hoping for a more elegant solution than this.

However, I will follow the VBA examples in the link and see if I can get it up and running. If I'm successful I might then try and create a flow that uses the Power Query interface to generate the GUIDs so that I am keeping everything in Dataverse rather than using VBA and separate systems to manipulate the data before importing. Hey I don't really know what I am doing yet but that is my thought process.

It's evening time here in Aus so I will take a look at it in the morning and circle back.

You're a good man Kristian. Thanks heaps.

[Kristian_Cole]

No worries Mike, as soon as it gets complex it never gets elegant lol!!! Glad I could help in some small way.

[Fubar]

Exactly what you need will depend on the method of Import and your import is a once off versus continuing into the future (i.e. will likely involve updates in the future). 

If updates in the future then look into using Dataflow, if a once off then can be done with either standard Data Import or Dataflow.

For the PO table in dataverse, you would add a new Lookup field for the Vendor 

For the PO Line Item table in dataverse, you would add a new Lookup field for the PO (and possibly one for the Vendor if you intend to carry it through into Dataverse)

Standard Data Import

• Import your existing Primary Key values into new fields in Dataverse as part of your import.  when get to importing will import Vendor, then PO, then PO line items
• When you import, you can change the mapping so that rather than using the default Name, id (guid) fields you use your new field that holds PK value (there is a symbol to the left of the field (when they are Lookups and Options sets) and when you click it you can change the default.   You'll see the symbol in the screen shots here against Primary Contact https://community.dynamics.com/blogs/post/?postid=0df12597-0d43-4bd3-bb57-42824d459b60
• For PO you would change this mapping on the PO import so that the Vendor lookup uses your new field.
• The PO Line items can be more complicated as at the moment you have both Vendor and PO (Dataverse would usually just link via the PO).  If you are not going to carry the Vendor through to Dataverse, then you can just do the mapping change like for the vendor on the PO, but for PO on the PO Line Item.  If you are going to carry the Vendor then you would still do mapping of PO on the PO Line Item, but also do a mapping for Vendor on the PO Line Item Item

(no Alternate keys etc needed as long as you Vendor and PO have uniques in Access)

Dataflow

Would require Alternate Keys to be defined. But has the benefit that it is easier to do future updates with the Alternate Keys defined (can also do the update via Web API using Alternate Keys)

• Import would still be Vendor, PO, then PO Line Item
• Vendor/Account table in dataverse, create an Alternate Key on the Vendor ID (needed so you can see the lookup when you import the PO)
• PO table in Dataverse, create an Alternate Key on Purchase Order
• When you Import PO you will select the Alternate Key for the Vendor ID to populate/link the Vendor.
• PO Line Item Table in dataverse, when you import you will select the Vendor ID and Purchase Order (alternate keys you have created earlier) to populate/link the lookup fields.

[MikeS2023]

Hi Fubu,

I looked at creating GUIDs but that would only work if the upload was a one off. If I wanted to load some invoice data and link it to the PO table at a later date I would need to look up the GUID for each record which I think would be quite slow for large data sets.

What I did was create primary keys in the PO Header and Line Items tables. (Is this necessary as I see the primary key option when importing which appears to pick up the alternate keys that I set up)

Made them be the Primary Name Column.

I set up alternate keys on these tables to ensure no duplicates. I know this is not necessary as we have the Primary Key field being a concatenation of these columns.

I added a lookup to the PO Line Items and referenced the PO Header Key.

I created a lookup on the PO Header to the Vendors table.

POHeaderKEY	Vendor Id	Purchase Order	Purchase Order Date	Delivery By Date
V10002-1055648	V10002	1055648	03-Dec-23	16-Dec-23
V10003-1056658

POLineItemsKEY	Vendor Id	Purchase Order	Item Code	Quantity	Unit Price	Total Line Value	POHeaderLookup
V10002-1055648-ITM11111	V10002	1055648	ITM11111	10	1	10	V10002-1055648
V10002-1055648-ITM22222	V10002	1055648	ITM22222	20	5	100	V10002-1055648
V10002-1055648-ITM33333	V10002	1055648	ITM33333	30	10	300	V10002-1055648
V10003-1056658-ITM44444	V10003	1056658	ITM44444	40	12	480	V10003-1056658
V10003-1056658-ITM55555	V10003	1056658	ITM55555	50	6	300	V10003-1056658

I was able to import data and the relationships are working. I just used the manual import function.

Is this method correct?

I will check out the link you provided and your notes on Dataflow.

Thanks for taking the time to take a look at this. I really appreciate it.

[Fubar · Accepted solution]

---

@MikeS2023 wrote:

Hi Fubu,

I looked at creating GUIDs but that would only work if the upload was a one off. If I wanted to load some invoice data and link it to the PO table at a later date I would need to look up the GUID for each record which I think would be quite slow for large data sets.data and the relationships are working. I just used the manual import function.

---

It depends, if it were a once off you might consider exporting to Excel from Dataverse using a Model Driven App (you can export 100,000 rows to Excel at a time off the Command Bar and hidden column A will contain the GUID),  then with the Excel use VLookup or XLookup to get the GUID's into your your other data.

Alternate Keys are useful to hold the ID from another system, and both the Web API (custom code) and Dataflows can use them for Updates/Upserts (rather than having to use the guids).

If you end up with the correct outcome and everything is linked how you need it, then it was a correct way (and there is usually more than one way to achieve things with Dataverse).

[MikeS2023 · Accepted solution]

Thanks mate.

I really appreciate your help.