Need some help to determine the best way to s...

[Skip2MiLu]

Need some help to determine the best way to setup the data structure in the following scenario.

I have two tables: 1. Global assortment 2. Local assortment 3. Country

1. The global assortment table has a list of global vegetable varieties own by a seed company. eg:

-Variety ID = Unique -Crop = Tomato -Crop Segment = 200g Large Beef -Prod Segment = Indeterminate -Variety = Sylvianna (Label)

2. The local assortment table only has a list of vegetable varieties that a local subsidiary of the company is selling and or developing

-Country ID = South Africa -Variety ID

Questions 1. I initially had an Assortment ID for table two but realized that maybe a computed key between Country ID and Variety would be better as I believe this would restrict me from accidentally adding a duplicate record (i.e more than one record that have the same variety in the same country? WOULD THIS BE A CORRECT WAY TO ACHIEVE THIS?

2. I would like to show the Crop, Crop Segment & Product segment columns per that particular variety in the Global assortment in a local assortment view.

How best do I set this up. I would assume there is only two options.

2.1 Either a virtual column pulling the relevant info based on the product ID 2.2 Adding Crop, Crop Segment & Product segment columns in the Local assortment table with a formula that pull the relevant info when the variety is selected. WHICH WOULD BE THE BETTER OPTION?

I WOULD LIKE TO BE ABLE TO ACHIEVE THE FOLLOWING EXAMPLE: -CHANGE THE CROP COLUMN VALUE IN THE LOCAL ASSORTMENT TABLE AND THIS WILL ALSO UPDATE THE GLOBAL ASSORTMENT TABLE.

Hope I am explaining the scenario correctly and what I would like to achieve.

[RezaRaoofi]

The answer to your first question is: Yes, you are on right track; combination (i.e. computed) key is a good decision in this case.

Regarding your second question, as long as Variety ID is a Ref column referring to Global Assortment table, you can grab all columns from Global Assortment table and show them in Local Assortment table using Dereference method:

[Variety ID].[Any Column from Global Assortment]

To clarify further, your 2.1 option is a good one, meaning a Virtual Column that holds a Dereference formula.

[RezaRaoofi]

Check this out for more details about Dereference:

https://help.appsheet.com/expressions/expression-types/dereference-expressions

[Skip2MiLu]

Thanks Reza. I thought as much. Let say I use the virtual column as you suggested…will I be able to edit the value? eg: Be able to edit say the a value in the crop virtual column in the local assortment and it will also change the actual crop value in the global assortment table?

[RezaRaoofi]

@Michael_Pinto No, in a one-to-many relationship you won’t be able to edit any column on the one side from many side. If you want to edit any row that physically located in a table, you have to edit that table directly.