Discussions

Expand all | Collapse all

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

  • 1.  Consolidating field value from Child records up to field in parent record

    Posted 08-29-2018 14:44
    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]


  • 2.  RE: Consolidating field value from Child records up to field in parent record

    Posted 09-05-2018 22:35
    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])


  • 3.  RE: Consolidating field value from Child records up to field in parent record

    Posted 12-17-2018 22:37
    An elegant solution. Note that the December 18 release included a new 'Summary Text' field type that may serve this or similar purpose without any contortions on the part of the builder.

    https://help.quickbase.com/release-notes/december-2018-release-notes.html


  • 4.  RE: Consolidating field value from Child records up to field in parent record

    Posted 12-17-2018 22:52
    Indeed.  The new summary combine text field  will concatenate up to 25 unique children.


  • 5.  RE: Consolidating field value from Child records up to field in parent record

    Posted 12-18-2018 01:20
    The new Dec 2018 feature works like a champ, I love it..