Native functionality within Dataverse to establish multi-table relationships that automatically update

[Mike__]

I'm in the process of creating a Dataverse database for tracking details of my Suppliers, their Products, and the Systems that they are used in.

The functionality that I want is to be able to provide lists of Suppliers which contains details of the scope of what they supply and any limitations on it, a web address for the Supplier (to reduce ambiguity), risk category, the names of their product or service, Systems which the supplier supports, the types of product that they supply (from a Choices lookup), their accreditations (such as ISO etc), the contract manager, contract start and end dates, etc.

I also want functionality that provides a list of Products, containing the name of the Product, the name of the Supplier, details of the Systems that the product is deployed on, a web address for the Product (to reduce ambiguity), the data types that the product is approved for, the data classifications the product is approved for, storage locations, storage type (such as local, private cloud, hybrid cloud, etc), product accreditations, contract manager, and contract start and end dates, etc.

Further, I'd like a list of the Systems we have, containing the System name, the Suppliers that support the system, the Products or components within the system, the lead responsible for the system and their contact details, data types that can be processed on the system, data storage locations and types, connectivity types into the system (public-facing, VPN, managed device only, etc), hosting details, system approval scope, testing run-out dates, etc.

Ideally I'd like for the tables to update when a relevant record is created or changed on another table, for example, when a new product record, Product 1, is created on the Product table that is supplier by Supplier 1, the "Name of product(s)" column in Supplier 1's record in the Supplier table would automatically update to show Product 1. When a supplier has their record updated to show that they support System 1, the supplier name is updated to show in the "System supplier(s)" column in System 1's record on the System table, etc.

---

What I've tried: To fulfill the requirements, I created three separate tables within Dataverse:

• Supplier table ("Supplier Name" as primary key);
• Product table ("Product Name" as primary key"; and,
• System table ("System Name" as primary key).

On the Supplier table I created a column for "Name(s) of Product(s)", which I set the Data Type Lookup and pointed at the "Product Name" column on the Product table. I also created a "Systems Support" column, again as a Lookup, and pointed that at the "System Name" column on the System table.

On the Product table I created a "Product Supplier Name" column, set as a Lookup, and pointed at the "Supplier Name" column on the Suppler table, and a "System(s) deployed on" column again as a Lookup and pointed at the "System Name" column on the System table.

Finally, on the System table I created a "System supplier(s)" column which is set as a Lookup and pointed at the "Supplier Name" column on the Supplier table, and a "System components" column, again a lookup and pointed at the "Product Name" column on the Product table.

---

Results: I was able to select a relevant record from the related Lookup in each table, or enter in relevant text if a relevant record didn't already exist, but the related records would not update to show the relationship changes across the table.

I now appreciate that it also doesn't seem possible to select multiple choices using a Lookup function, for example to show multiple Suppliers or Products within a System.

---

I've read conflicting advice as to whether what I'm trying to achieve is achievable via m:n relationships or whether I should be looking to use foreign keys and a ()relate function.

Would anyone be able to advise whether what I'm trying to achieve is actually possible within Dataverse, and if so, what functions or solutions I should be looking to utilise?

I haven't touched any kind of SQL database creation since university, so I'm very rusty and I'm also brand new to Power Platform, Dataverse, etc.

Any pointers or tips are greatly received!

[dpoggemann]

Hi @Mike__ ,

Some thoughts here...  

Core table structure I would probably go with would include the following:

Model Reasons:

1. Supplier Table which would contain supplier level details.  Supplier can have multiple products - this does assume that there are not multiple suppliers for the same product.  If that was the case then I would change the model and have another intersect table like the System Product table. 
2. Products Table - This table will contain core information about the product including product name, description, classification etc.  If you have situations where you want to break some of these fields to additional tables you could do this as well or have fields that are choices that allow multiple values.  Normally I stay away from the Choices (multiple values) unless it is a very basic relationship that doesn't need additional fields.
3. System Product - Intersect table that would have a 1:N relationship from the Product and from the System.  This would allow you to create metadata related to the product and the system relationship.  You could do an N:N if you did not have any metadata on this relationship but I end up normally requiring so create the intersect table manually.
4. System - This would be all the system metadata...

Let me know if this doesn't cover your needs and happy to work with you more on this model...

[Mike__]

Thank you for taking the time to read and respond Drew! And apologies for taking so long to come back to you.

I'll go away and give your method a try and report back!

Thanks again,

Mike