Forum Discussion

BenJohnson's avatar
BenJohnson
Qrew Member
3 years ago

Auto-populating sections of parent table form single child table

Hi all,

Very beginner database manager here, so bear with me as I try to speak database language.... especially when I try to keep my terms straight when dealing with child/parent table to table relationships that are specifically related to real human children/parent data sets.

It seems I use Quickbase a little differently than lots of folks - to manage a youth program's participants.

The challenge I'm having is in integrating two different child tables into a single parent table. Basically, I have two participant-generated datasets that I import into separate similar tables: I want to cherry pick a few lines of data from each and auto-populate into a pre-existing single parent table. I can only figure out how to make it work the other direction - for data from the parent table to be a lookup field in the child table.

Here's the frame of reference for my workflow: I work with human kids, and initially create a very basic master "kid profile." After I create a "kid profile" I receive separate Google forms (intake forms) from each "kid" and "mom&dad" - and would like certain fields from each "kid/mom&dad" intake form to auto populate the master "kid profile."

These current Google Forms used to be paper forms that were entered manually, but we are working toward better automating this work flow.

Thanks for the help!



------------------------------
Ben Johnson
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    Good to know that you are working with human kids and not the billy goat kind.  

    OK so we have a Kid Profile and can I assume that you have or can make a relationships where 

    One Kid Profile has many Kid Intakes
    One Kid Profile has many Parent Intakes.

    I assume that you will manually attach the child records for the two different Intake form records to the proper Kid Profile as the come in unless you have managed to uniquely identify the kid profiles and get the intake forms to have that number which I doubt. 

    If you have those Relationships  then you can float up text fields from child records to the parent records (now I'm using generic Quickbase terms for Parent and Children record as your kids are the parents!). 

    Just go to the relationship and create a summary field which is a Combined Text Summary of a particular text field.  If you are trying to float up something which is not text for example an email address field type or or a phone number field type, then you need to make a mirror field on the child records that converts them to text.  For example you might have a field called 

    [Mobile 1 (text)]
    and the formula would be 
    [Mobile 1]

    Then when you observe the native Combined Text Summary field on the Parent record it will appear like a bubble/pill which I find visually odd.

    What I typically do is call the native field like  [Combined Text Summary Mobile 1]

    Then create a new field on the Parent like [Mobile 1] with a formula of

    SearchAndReplace(ToText([Combined Text Summary Mobile 1]), " ; ", "\n")

    Those combined text fields are when converted to plain text have the elements separated by "space ; space" and with the SearchAndReplace it's replacing that with  vertical carriage return "\n" so that on the parent table they stack vertically in a tidy fashion.   


    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • BenJohnson's avatar
      BenJohnson
      Qrew Member
      3 years ago
      Thanks Mark. I simultaneously posted my work-around, but I'll summary field / combined text field a go as well - it sounds a bit cleaner!

      ------------------------------
      Ben Johnson
      ------------------------------
  • BenJohnson's avatar
    BenJohnson
    Qrew Member
    3 years ago
    Think I got it... I created a secondary table to table relationship so that each of my tables were both parent and child, then had to fiddle around a little with the reference fields and proxy fields to make it all work how I wanted it to.

    ------------------------------
    Ben Johnson
    ------------------------------