Child report table on Parent record

  • 2
  • 2
  • Question
  • Updated 1 year ago
  • Answered
So I need an answer to 2 questions here:

1)  How do I get the child report table that appears on the parent record to extend all the way accross the screen.  I have attached a pic.

2)  Is there any way to summarise data from multiple child records on the parent record.  So:  one "Job" has many "Job Cards".  how do I for example; get a total of all the hours worked accross all the job cards relating to that job?
Photo of Geoffrey

Geoffrey

  • 1,730 Points 1k badge 2x thumb

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
For the #1 question, edit the form properties and erase the values where it says maximum height and rows.
Photo of Geoffrey

Geoffrey

  • 1,730 Points 1k badge 2x thumb
done, thank you.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
For the # 2 question, I assume that you have a relationship where 1 Parent has many Children.  Open up that relationship and on the left side you will see a button to make a summary field.
Photo of Geoffrey

Geoffrey

  • 1,730 Points 1k badge 2x thumb
This works, but the field list is limited to what I assume are "numeric type fields".  Is there any way to combine formula text fields from a child to a single field in the parent.  example;  I have a field on each job card for items used, I want to get all the items used accross all job cards and summarise in 1 field on the parent record
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Can you give some examples of the current set up you have and the information in the field you are looking to summarize?  Maybe an example of what you want it to look like?
Photo of Geoffrey

Geoffrey

  • 1,730 Points 1k badge 2x thumb
setup is as follows:

Parent table: Job
Child table: Job Cards (multiple job cards per job)

In the child table, there is a text field where the user lists the items seperated by a comma.  I have a formula text field that takes the items and lists them below each other after each comma.

What I want to do:

1) Combine all the items together (accross many child records) -  and show it on the parent record.  
2) Create a summary report for these fields as well.  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
It is possible to do a series of what are called reverse relationships to float up text values form a limited number of child records.  In practical terms how many child records would you need to be able to handle?
Photo of Joshua Norton

Joshua Norton

  • 80 Points 75 badge 2x thumb
How do you create a reverse relationship? I'm having the same issue but my example is more like: "Parent table: Practice, Child table: Multiple Providers"
(Edited)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Geoffrey,

Combining multiple variations of text from a child to a parent via a reverse relationship will not work, because you'd have to account for every variation of the child.  And the number of children.  So if you had 10 child cards, you'd have to have 10 reverse relationships, and then a master formula to recombine them.

Additionally making each of those sequential relationships will require about 4-5 new fields for each of them.  This will take a huge hit on your app performance.

My recommendation:
On your child "Cards" table, create a summary report that combines like values in the text field.
>
Then make a report link on your parent table, and use that summary report on the form.
>
This should give you the summed text values, and display them on the form.

I know its not perfect, but from what I can tell this might be a good option.

p.s. You might consider addressing how they input the multiple items.  That sounds like it could be handled differently, which might give you better reporting.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,404 Points 50k badge 2x thumb
Ah, yes, this does assume that you are using the Record ID# for the Key field or at least a numeric Key field.  I don't think that reverse relationship will work with an text type key field.
Photo of jeanne

jeanne

  • 374 Points 250 badge 2x thumb
So would there be anyway to list all the children records on a report from the parent?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,404 Points 50k badge 2x thumb
Actually if the Parent Key field is text but the children key fields are numeric like [Record ID#] then the technique should still work.  If you are stuck on a step post back and I can help here or contact me directly for one on one assistance via my website QuickBaseCoach.com
Photo of jeanne

jeanne

  • 374 Points 250 badge 2x thumb
they are both text. Still relatively new to QB, but when i developed my application, i created a unique_key field (text - concatenation of several fields to make unique) in both parent / child that links the two tables together.

is it possible to a fields from the child table on the parent report if the keys are text?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,404 Points 50k badge 2x thumb
No it's not possible then.  Are you sure that you need the key field on that child table to be a concatenated field?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Joshua,

Rather than making a reverse relationship, I'd recommend making a joined table.

Call it something like "Practice Providers".

Each Practice will have many practice providers 
AND
Each Provider will have many Practice Providers.

Then you can have multiple assignments and gives you a lot more flexibility for reporting, and appointment setting.
Photo of Joshua Norton

Joshua Norton

  • 80 Points 75 badge 2x thumb
I guess i'm a little confused on how that would work.

What I'm trying to do is create a report based off of one of the child tables, but have it display data linked from the other child table and the parent table.

Or have a parent table that can display data from both child tables.


So for example:
Lab table(child table), shows list of providers(child table) from a specific practice(parent table)

If the providers could be concatenated into one cell that would be perfect.

I'm wanting to see in the report:
Lab name | project name| provider(or list of providers concatenated in one cell)
...
...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,404 Points 50k badge 2x thumb
re: "Or have a parent table that can display data from both child tables."

You can float up data form a limited number of children.  I had asked how many, in practical terms you will have.  Let's say that in practical terms, 5 is enough.

The technique is edit the relationship between 1 parent has many Children to make a summary of the Minimum Record ID# of the child records.  Call it [Minimum Record ID of Providers #1].  Look that field up down to the Child table.

Then make a reverse relationship where 1 child has many parents and on the right side use the field [Minimum Record ID of Providers #1]

Lookup the text value to the parent Practice record.

Then duplicate the field [Minimum Record ID of Providers #1] and call it [Minimum Record ID of Providers #2]

But change the Summary filter to make it the minimum record id# subject to the condition that the [Record ID#] > [Minimum Record ID of Providers #1].  On the main "forward" relationship, lookup that [Minimum Record ID of Providers #2] to the child table.

Conveniently, QuickBase will have duplicated the reverse relationship when you duplicated that field.  Edit that relationship  to look up the text value to the Parent record.  That's two so far we have done.

Basically just keep going and looping.  if you have say a half a dozen to do, then it will work OK.,  if you have dozens of loops to do, then you risk getting into performance issues with your app. 

Once you get enough of them floated up, you can concatenate them together with a List function)
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
If you are going to go to the trouble of all those reverse relationships, you might as well just flip the original.

So make Each Provider, has many Clinics, and make that 4 or 5 times.

**Still not recommended to handle it this way, but if you are going down that path, you might as well cut it down.**
Photo of Joshua Norton

Joshua Norton

  • 80 Points 75 badge 2x thumb
Thank you both of you. Ya we could always flip the original, but we have other child tables linked to the parent(practice), so things get tricky at that point. 

We'll most likely do the consecutive reverse relationships. Thank you both again!