Basic Relationship Advice

[OkayGoogle]

I am having trouble getting relationship data to show up in Dataverse (Common Data Service) views.

Table 1: Items

Item ID	Item Name	Customer	Customer Item ID
1	ball	Billy Mart	1b21481nd1
2	string	Johnny Mart	9838b2b0b2
3	yoyo	Dewy Supplies	18c83b1bv1zls
1	ball	Joey Groceries	b300zxy1314

Table 2: Inventory

Item ID	Available
1	67
2	89
3	52

GOAL: I want to show inventory in Table 1 ( Items ).

STEPS to reproduce:

1. I go into my Inventory table, select the Keys tab, add key based on Item ID
2. I go into my Item table, select the Keys tab, add key based on Item ID
3. I go into my Items table, click on Relationships, and add a Many to one relationship to Table 2 ( Inventory ).
4. I go into my Items table, select the Views tab, go into Active Items, and add a column (related > Inventory > Available). I publish my view.
5. I go into my Items table, select the Data tab, Refresh the data, and what do I see? No inventory, it's all blank.

Please help.

[ChrisPiasecki]

Hi @OkayGoogle,

At this point you've just established a relationship between the two tables. You still must go through the exercise of linking the actual records together. You can export your list of Items to Excel using the active items view, then populate the inventory fields with the appropriate values, then import the Excel file back into Dataverse.

More details on how to do this is explained here. 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[OkayGoogle]

Hi @ChrisPiasecki , thanks so much for your quick response! I forgot to mention in my initial post that I already entered data into the two tables using the Excel method. This was done before step 1.

I am under the impression from your response that I need to manually fill out the inventory field on my Items table. That doesn't seem right to me, since the point of making a relationship is so that it would automatically grab the data in the Available field from the Inventory table and let me access it from the Items table based on the Item ID Keys I made in both tables. Obviously my understanding is wrong since it isn't working, but could you please elucidate?

[ChrisPiasecki · Accepted solution]

Hi @OkayGoogle,

(Alternate) Keys are a way to enforce uniqueness on another column or set of columns, which is equivalent to a composite primary key in a database like SQL Server. In your example, if you try to save a new Item record with an ID that already exists, it will fail due to the key constraint. These keys and the relationship you setup have no correlation. 

When you setup the one to many relationship between Inventory and Items, behind the scenes it creates a Lookup field on the many side of the relationship (Items) to the Inventory table. This Lookup column  the unique identifier of the Item table, which is equivalent to a foreign key in a database. So at this point the column is created but no data is in the column.

Hope this helps clarify. 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[OkayGoogle]

Thanks for all your help! For any future readers, I will just summarize what I learned here:

• Lookup columns are not SQL Joins, they are rather foreign keys
• After you created the tables and the relationship, you need to Open in Excel, click on your relationship column (in my case it was " Inventory (Lookup) "), and choose which row from the related table you want to show up in the column, and publish.

[OkayGoogle]

@ChrisPiasecki I have one more question if that's okay with you. Is there a way to get my Available column from my Inventory table to automatically get data for new records made in my Items table? Right now using the Excel method, if I enter a row with a previously existing item (into my Items table) but do not fill out the related Inventory column, it will stay blank. Here are my criteria:

• Have a constantly changing / growing table of items, but some will be repeated because different customers can have the same item
• Items need inventory to automatically get assigned to the item from the Inventory table

In SQL this would be accomplished with a JOIN, but I can't figure out where this functionality is in Dataverse. Really appreciate your help here!

[ChrisPiasecki]

Hi @OkayGoogle,

If I am interpreting correctly, every time you create a new Item record, you want to automatically create an equivalent Inventory record and set it's lookup to the Item? If my interpretation is correct, I would using a workflow to automate this process. You can use a Power Automate flow to do this asynchronously (in the background), or if you want to do this synchronously (real-time) you can achieve this using a classic Dataverse workflow.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[OkayGoogle]

That is very helpful to know, thanks again!