How do we concatenate the info from a text field in all details/child records into a single field?

  • 1
  • 2
  • Question
  • Updated 4 years ago
  • Answered

Our master table is Projects. One of the details tables is Faculty. Within the Faculty table is a field called "Latest Recruitment Update." We want to concatenate all the info in the "Latest Recruitment Update" field from all Faculty children/detail into a single field. How do we do this?

Photo of Jennifer

Jennifer

  • 20 Points

Posted 4 years ago

  • 1
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Realistically, how many Faculty child records could there be per Parent Project record?
Photo of Jennifer

Jennifer

  • 20 Points
Typically 2-3. But I think 5 would be our max.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
You will need some Summary fields and 5 reverse relationships.  Here are the steps. I suggest these field names so you don't confuse yourself.

I suggest making a field on the Child table called [Faculty: Latest Update] like


[Faculty Name] & ": " & [Latest Recruitment Update]


  1. Summary field Minimum Record ID# of the Children.  Name it [Record ID# of Faculty #1]
  2. Look that up down to the child, and rename to maintain its original name of [Record ID# of Faculty #1].
  3. Duplicate the Summary field, rename to [Record ID# of Faculty #2] and add a filter where [Record ID#] > [Record ID# of Faculty #1].
  4. Look that up down to the child, and rename to maintain its original name of [Record ID# of Faculty #2].
  5. Duplicate the Summary field, rename to [Record ID# of Faculty #3] and change the filter to where [Record ID#] > [Record ID# of Faculty #2].
  6. Look that up down to the child, and rename to maintain its original name of [Record ID# of Faculty #3].
  7. Duplicate the Summary field, rename to [Record ID# of Faculty #4] and change the filter to where [Record ID#] > [Record ID# of Faculty #3].
  8. Look that up down to the child, and rename to maintain its original name of [Record ID# of Faculty #4].
  9. Duplicate the Summary field, rename to [Record ID# of Faculty #5] and change the filter to where [Record ID#] > [Record ID# of Faculty #4]
You are half way there.

A. Next make a Relationship where One Faculty is related to many Projects (yes, that sounds reverse). Important - on the right side of the relationship select the field [Record ID# of Faculty #1] (not the usual default [Related Project])

B. Then lookup the field [Faculty: Latest Update] and call it [Latest Recruitment Update Faculty #1]

Repeat steps A and B 4 more times for each of [Record ID# of Faculty #2] [Record ID# of Faculty #3] .. etc

Great, almost there.



Now concatenate all the updates


List("\n",

[Latest Recruitment Update Faculty #1],

[Latest Recruitment Update Faculty #2],

[Latest Recruitment Update Faculty #3],

[Latest Recruitment Update Faculty #4],

[Latest Recruitment Update Faculty #5])