Discussions

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.">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1580595/RackMultipart20170410-97803-2auf7l-space_inline.PNG?1491844914">

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?

For the #1 question, edit the form properties and erase the values where it says maximum height and rows.

done, thank you.

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.

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

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?

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.  

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?

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

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.

This doesnt work in my case because a summary report doesnt allow me to summarize a formula text field (unless I am missing something?)  The option for that field doesnt even appear on the drop down

Use the text values for the grouping or rows, not the summary

Ok i'm with you. the summary report actually looks ok. Just on the report link, not sure how to display this report on the parent table through a report link field.

Geoffrey,

You will want to edit the field properties for that report link, and set the 'target'.

In the image attached, this is a report link field on the 'projects' table and it is displaying related child records on the 'time entry' table.

done

I am needing to this also, but think i am missing something.  you state "On your child "Cards" table, create a summary report that combines like values in the text field." what do you mean "in the text field"?  i have a parent table that has multiple children, want to display all the children in in a report of the parent.  Can you please give me more detail?

thanks, 
Jeanne

Jeanne
How many children would you need to float up data from up to the parent record.

a parent will have at least one child but can be up to 10.  avg would probably be 3 or 4.

thanks for you help!

Here is an answer from a previous  post which you can adapt to your situation.

If you have a large app with say over 100,000 records in the relevant tables, then performance could be affected by all the looping.  But if you are dealing with just thousands of records you should be OK.

The way to do this is with as many "reverse relationships" as you guess will be enough to cover off 99% if the situation.

So, let talk about Operators.  One Job has many Operators.  Make a summary field on that relationship of the Minimum of the Record ID# of Operators.  Call it [Record ID# of Operator 1]. 
Then do a lookup of that down to the child Operator table as we will need it later.

Then do a new Relationship where one Operator has many jobs. For the reference field (the field on the right side), use that new Summary field called [Record ID of Operator 1].  Get rid of the extra fields that get created on the left side of the relationship (Add Operator and that report link field).  Do a lookup from the Operator down to Jobs and bring in the Operator Name and call it Operator 1.

Wonderful, the Job now knows the first Operator.

Now, make a duplicate of that summary field, but in the duplicate add the extra condition that the [record ID#] is greater than the [Record ID# of Operator 1].  Call it [Record ID# of Operator 2].  Do a lookup of that field back down to the Child Operator table as we will need it later.

Now, in making that duplicate, conveniently, you will find that it duplicated the reverse relationship so now do a lookup in that relationship of the operator name called it [Operator 2].  Wonderful again, we now have Operator #2's name on the Job Record.

Then make another duplicate of that summary field and just keep going, each time changing the filter so that the record ID# is greater than the previously highest record ID of Operator.

In the end say you collect the names of the first 4 operators on a job, so then just string them together in a formula like

List ("\n",
[Operator 1],
[Operator 2],
[Operator 3],
[Operator 4])

 and put that concatenated field list on your report.

have a little trouble following , also i made my own unique key, can't seem to do what this is calling for.  Are there any more detailed instructions?

thank you for your help!

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.

So would there be anyway to list all the children records on a report from the parent?

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

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?

No it's not possible then.  Are you sure that you need the key field on that child table to be a concatenated field?

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.

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)
...
...

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)

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.**

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!