cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OkayGoogle
Helper II
Helper II

Basic Relationship Advice

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

 

Table 1: Items

Item IDItem NameCustomerCustomer Item ID
1ballBilly Mart1b21481nd1
2stringJohnny Mart9838b2b0b2
3yoyoDewy Supplies

18c83b1bv1zls

1ballJoey Groceries

b300zxy1314

 

Table 2: Inventory

Item IDAvailable
167
289
352

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

7 REPLIES 7
ChrisPiasecki
Super User
Super User

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.

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?

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.

View solution in original post

OkayGoogle
Helper II
Helper II

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
Helper II
Helper II

@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
Super User
Super User

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.

That is very helpful to know, thanks again!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (75,439)