I have three tables:
- 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.
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.
Contacts / Addresses / Phones
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:
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:
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.