How to lookup related records across tables and concatenate/combine the lookup outputs?

Question

Hello,I am trying to create a single summary table that combines/concatenates related data from 3 separate data tables ("Risks", "Controls", "Treatments").The relationship between the tables is:

each "Risks" has multiple "Controls"
each "Risks" has multiple "Treatments"

Is there a way I can create a new summary table that shows each "Risks" and for each "Risks" looks up and combines/concatenates/nests all related "Control" into a single field? (and similarly for each all related "Treatments") - please see example layout below.

"Risk" Field
"Control" Field
"Treatment" Field

R1.1
R1.C1.1
R1.T1.1

R1.C1.2
R1.T1.2

R1.C1.3
R1.T1.3

R1.T1.4

Appreciate any assistance, Thanks!------------------------------Filip Glisic------------------------------

MarkShnierYou · Answer

Obviously you are aware that you can be sitting on a single risk record on a form and see the embedded tables for the Controls and treatments.

If you want to get a similar effect but on a report, one solution would be to create a combined text summary field of the Controls to summarize the Control up to Risks. Then do the same with the Treatments.

I do not like the appearance of the combined text summary field which kind of looks like bubbles, they can be changed to be looking like normal text with an easy formula field.

That is not what you were looking for can you better describe an example of the result table that you are looking for using your data above.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

"Risk" Field	"Control" Field	"Treatment" Field
R1.1	R1.C1.1	R1.T1.1
	R1.C1.2	R1.T1.2
	R1.C1.3	R1.T1.3
		R1.T1.4

Sign up

Login to the community