Report with values from multiple child tables

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

I have three tables:

  1. Persons
  2. Addresses
  3. Phone Numbers

A person can have zero or more addresses.

A person can have zero or more phone numbers.

When I run a report on Persons, the Address column contains a link to the Addresses for each person and the Phone Number column contains a link to the phone numbers for each person.

The functionality I need is to have a report that shows the details of each address and phone number for each person instead of a link.

I do not believe this type of functionality is available, but is there a way I can custom code it? Do I have to run it in a third party script using the API?

--

To visualize, let's say a person has a first and last name. The Addresses table has a street address, city, state, and ZIP code. The Phone Numbers table has the phone number.

John Doe has a home and a work address as well as a work phone and mobile phone. I need a report that displays the following for John Doe's record (I have transposed rows and columns here for readability):

First Name: John

Last Name: Doe

Address 1 Street: 101 Main Street

Address 1 City: Boston

Address 1 State: Massachusetts

Address 1 ZIP Code: 02108

Address 2 Street: 1942 Columbus Ave

Address 2 City: Boston

Address 2 State: Massachusetts

Address 2 ZIP Code: 02108

Phone 1: (617) 123-4567

Phone 2: (617) 321-7654

--

I really hope that this is doable in QuickBase so I don't have to junk up the Persons table with a fixed number of addresses and phone numbers.

Photo of Joe

Joe

  • 0 Points

Posted 5 years ago

  • 0
  • 1
This can be done in native QB, but not for an unlimited number of child records. The game here is to bring data in a reverse direction up from the child to populate the parent record.


Bringing up the first child record is fairly easy. One assumes that you have that Relationship set up between parents and children.

1. Create a Summary field of the Minimum of the Record ID# of the children. Call it [Minimum Record ID#1]


2. Create a new reverse relationship where one Child is related to many parents, and the common field is that [Minimum Record ID# 1] of Child (now available on the Parent Record, and the record ID of the Child.

3. Bring in the Lookup fields that you want to bring up to the parent.

So, you get yourself that far and satisfy yourself that you have accomplished the entry level diploma in reverse relationships 101.

Now for the 202 course.

Bring that [Minimum Record ID# 1] back down to the child record in a lookup for your original (normal) relationship.

Now copy the field [Minimum Record ID# 1] and call it [Minimum Record ID# 2], but set a filter where the record ID# is greater than the value in the field [Minimum Record ID# 1]. YOu will find that in making the copy of that field, it conveniently also make a new relationship for you. bring down any lookup fields you need.

Just keep doing this until you get to reasonable maximum number of Phone numbers and addresses that a Person might have. I suppose that 5 would be plenty.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
>Just keep doing this until you get ...

... a headache. Needless to say you can do this with script without the headache. I am going to a meeting shortly to learn Swift but I will show you how to do this later with an example.
Photo of Joe

Joe

  • 0 Points
Thank you for the great feedback Mark. I'll give this a whirl.
Photo of Joe

Joe

  • 0 Points
This sounds promising. Thank you QB Pirate!
Photo of Joe

Joe

  • 0 Points
101 is working like a charm - now on to the 202 course!

Thank you again - I was about to lose hope.
You will find that my approach is native and Dan's,  while more flexible,  does have a larger learning curve. So it depends on your aptitude for non native approaches.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
I just got off the phone with QuickBase HQ and we hatched a plan to replace the QuickBase's formula language with JavaScript. Going forward JavaScript will be native and then you can promote excessively flexible solutions as well.
:)
Photo of Joe

Joe

  • 0 Points
I would be interested in the script example. I am new to QB so a script may make more sense to me than the native formulas.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
Here is an example of splicing a miniature report of related Addresses and Phones into a view Contact record using the image onload technique:

Contacts / Addresses / Phones
https://haversineconsulting.quickbase.com/db/bi8ycjbpc?a=dr&rid=1

I know you asked for placing this information into a report but the procedure is basically the same except that you have to iterate over all records in the report and gather up more information and insert it into a column in the report. You can adapt the code I supplied to that purpose with just a little extra work which I am too lazy to do because it is just repetitious of other examples I have already created.

You may be thinking "so what - what's the big deal?" about this example considering that the miniature report spliced into the view record is not so beautiful as the naively embedded sub-reports and carries exactly the same information. This difference is that this method can insert any content anywhere - all you have to do is gather up the data using script and the API and supply an appropriate template to merge the data into. So the miniature report spliced into the view record page is crude only because I used a crude template:

Template
https://haversineconsulting.quickbase.com/db/bi8ycjbne?a=dbpage&pagename=template.txt

If you want a prettier output all you have to do is add a little styling with a few CSS statements. If you want a different layout or information fields just define the structure you want in the template. And yes you could include the svg of a Highchart in your template and just merge in the data and a beautiful chart will appear in the rendered output. You want miniature charts in your reports - no problem. Miniature forms in your reports - no problem. Miniature charts in your forms - no problem. Just about anything is possible.

Here is the essential code:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=328

Again this technique can be used to insert any generated content in your (1) dashboards, (2) user defined pages, (3) views, (4) reports or (5) forms etc. One more thing if I failed to mention it - you can use this technique to generate any content you want.
Photo of Joe

Joe

  • 0 Points
Very cool – Thank you for taking the time to lay this out!
Photo of Joe

Joe

  • 0 Points
Since this method essentially just adds data to the DOM, I won't be able to export it with the "Save as spreadsheet" functionality. I think I'm stuck with the native approach.
Photo of Joe

Joe

  • 0 Points
Does this mean I can finally use regular expressions in my validation formulas? Is there an ETA on native JS?
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
This was humor trying to nudge QuickBase in that direction. You will have to use the image onload technique to inject your JavaScript but then you can do anything you want.
Photo of Bennett

Bennett

  • 10 Points
I followed this approach and though the lookup works, the lookup data for the children appears as a single row as opposed to one row per child row.  Is there an approach which, if I look up 10 children, each child will have its own row?
Photo of Matthew

Matthew

  • 0 Points
Thanks for this, I'm going to test it out with something I'm working on: A parent record having multiple child records (basically each one being an uploaded image), and then having the parent record show thumbnails of those images. Do you think your solution is probably the best for what I am attempting? There really would only be 2-3 child records per parent record, but theoretically, it could be upwards of 30.