Hi folks - please help. I'm new to Dataverse and I'm pretty frustrated at the moment. For two days now I've been trying to get my head wrapped around Dataverse's primary keys, alternate keys, primary columns and relating tables with lookups. I've redesigned, deleted and created my tables too many times, and now I'm turning to the community for help.
Here is what I'm trying to do; maybe my design is off and someone can think of a better alternative. I need to create a Canvas app that will allow the users to input a new record for a product when it has entered our facility to start its journey through a process. The product has a unique identifier, think of a serial number, a service tag, or a sku. Let's go with Service Tag as the unique identifier. So this parent main table will be called "Product Main." For now it has four columns;
1. Product Service Tag (Text. Needs to be unique so no one can duplicate the Service Tag when entering a new record)
2. Product Location (Choice, A01, A02, B01, B02, C01, C02)
3. Product Process Step (Choice. New, Step 1, Step 2, Step 3, Ready to Ship, Shipped)
4. Product State (Choice; In-Process, Complete, Has an Issue)
So far so good, right. I created the above table. I did not use the Dataverse Primary Column, or I suppose I could make it auto-number if I had to, because in order to implement uniqueness for the Product Service Tag, I made that its own column and I had to make that an alternate key. (As a sidenote, the Choice drop downs in my table did not work until I set a default value for them. That's a bug, imo).
The second thing my users need to do is to provide a status, every 30 minutes, on each product as it progresses through our internal process. So I will create another table called Product Status Log where the users can select the Service Tag and enter a quick update on the product in a text field. So this table will simply be three columns.
1. Product Service Tag (Lookup. Should lookup existing Service Tags from the Product Main table)
2. Status Color (Choice. Red. Yellow. Green.)
3. Status Update (Text).
So this is a classic parent/child or 1:Many relationship. One Service Tag can have many log entries. In the Cavas App, I'll have a screen where a user can select a Service Tag and they'll get a scrollable view of all the Status entries so they can get a full history of the product working its way through the internal process.
Here's the problem. From what I understand, the Lookup column in the child table (the many table) HAS TO BE the Primary Column in the parent table. BUT the Primary Column has no ability to be unique. Do I have this correct? I cannot believe Dataverse is so rigid with this silly Primary Column. That has to go, imo. The GUID is the primary key, and it's setup by default. That's good. So now MS, you should allow us developers to be able to create and define columns as we need to. And also, allow us to choose the column in a parent table for a Lookup. Again I'm very new to Dataverse, but have a solid understanding of DB concepts. But maybe I'm missing something here. Again, this will be a Canvas app to enter new records, enter log status entries, view product state, and product status entries but first I need to get my DB tables designed and working correctly.
Thanks for reviewing and any assistance.
