Consolidating field value from Child records up to field in parent record

Question

I have a parent table 'products' and a child table 'Distribution countries'
Each product can have multiple distribution countries associated to it

In addition to the 'country name' field, the distribution country has a number of other country specific fields

What I would like is there to be a single field on the parent 'product' table that contains a list of all the associated 'country name' fields... thus as users add/remove 'distribution country' records this field on the parent 'product' record would be updated

Example

Product A
Country 1
Country 2
Country 3

Ideally I would like one field on Product A called 'Distribution Markets' that is a multi-select field type that contains [Country 1] [Country 2] [Country 3]

And if a user removes Country 3 child record then this multi select field on the parent is updated to only contain [Country 1] [Country 2]

QuickBaseCoachD · Answer

To do this you will need to float up the different Countries into separate fields on the tech record. Then you can see them and obviously have a formula to combine then into a single Text field. This method will work for a limited number of maximum Countries, say 5 or 10 max.

1. On the relationship between One Product has many Countries, make a summary of the minimum record ID and call it called [Record ID#1 of Country 1]. Look that up down to Countries.

2. Make a new reverse relationship where 1 Country has many products . On the right hand side of the relationship for the reference field use that field [Record ID#1 of Job 1]. Look up the Country field and call it [Country 1]. Now the product record knows the 1st Country. .

Clean up the right side of that relationship by deleting the Add Tech and Techs fields as you don't need them Now, flip back to the regular relationship where 1 product has many countries. Lookup up the field [Record ID 1 of Country] down to Countries.

Duplicate the summary field called [Record ID#1 of Country 1] but call it [Record ID#2 of Country]. But set the filter where the Record ID is > Record ID 1 of Country

You will find that the system automatically also duplicated the reverse relationship for you. Locate this new relationship and Look up the Country field and call it [Country 2]. Now the product record knows the 2nd State.

Clean up the right side of that relationship by deleting the Add products as you don't need them Now, flip back to the regular relationship where 1 product has many countries. Lookup up the field [Record ID of Country 2 down to the countries table

Just keep repeating the steps and each time you make the new summary field add the condition that the Add to the existing filter the additional filter that [Job State] is not equal to [Tech State x] where x is the latest Tech Sta

Probably 5 loops are enough, but that is up to you.

You can then concatonate the counties into one field.
List", ",
[country1]
[Couttnry 2]
[Country 3])

Sign up

Login to the community