One dimension table with approx. 6,000 records - This is Table One.
A second dimension table with apprx. 800 records. This is Table Two
The first table (with 6,000 records) is a list of products and it has a relationship with the second table (800 records) which is a list of every machine that makes the 6,000 products. One machine can make many products but a product is only made by one machine.
The first table (with 6,000 records) must have several look-up columns with the second table (800 records).
My question is: If I use Dataverse, will I be able to import the table with 6,000 product records with the main look-up column already done in Excel and then, later on, create a relationship with that look-up column linking it to the list of 800 machines?
In other words: My Excel list of 6,000 products has a column called "Machine Name" and that column is a look-up that brings in other data from the table that has 800 machines in it. I want to import the list of 6,000 products into Dataverse and then set the "Machine Name" column to be a look-up to the table with 800 machines and, from there bring more columns in from the machines table.
What I do NOT want to do is have to add a look-up column to the table with 6,000 products and have to manually select which machine creates each product.
I have read this a few times and keep coming back to in the Dataverse you would be creating a 1-Many relationship from Table 2 to Table 1. The Machine would have many products and the way this is done is to add the lookup column to the Products table for the Machine. You can load this from the Excel side automatically by setting the lookup column for the Machine with the name of the Name column from the Machine table and it will import with the relationship.
You can create views that will show fields from the related Machine table on the Products table views to show the fields from the related entity.
I know this is something you did "NOT" want but this and I apologize if the approach does not align but this is the relationship I would create and have created many times. Can you help me with why this is not something that makes sense? You could have a Power Automate Flow that could run and set the Machine column based on business logic so it would not need to be done manually as well...