Multiple Children with Multiple Parents

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I've been searching for an answering but I can't find exactly what I need so I'm hoping someone can help walk me through this. I have [Construction Projects] (Parent) and [Permits] (children), each parent can have multiple children and each child can have multiple parents. 

The way I have it setup in Quick Base, I use a related proxy field to select my parent on the child record. What I need to be able to do is select multiple parents at one time. So Child Record 1 might have Parent Record 2, Parent Record 3, and Parent Record 4. Parent Record 2 might have Child Record 1, Child Record 2, Child Record 3, and Child Record 4. 

I've seen that I can set up a many-to-many relationship with a third table but I'm not sure if that's what will solve my problem. I need to be able to do lookups/summaries between the parents/children and I need it to be user friendly. 

I don't know coding, so if coding is required, I will need detailed steps. Thank you in advance! 
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
This is definitely a Many to Many situation.  i have a home reno going on now and its needs permits, so I can picture the situation.

I would call the middle table Project Permits.

One Project has many Permits.
But I gather that maybe 1 permit has many Projects, say a whole row of houses or condos going up and they are all under the same electrical permit.

One you set up that middle table and the two relationships, you will be able to show an embedded report of the Project Permits on both the Projects Record Form and the Permits form.  

Post back once you get that set up.  If you need some summary fields like permit dates or Permit numbers floated up to a Parent record, there are ways to do that. 
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
I'm so glad you understand my situation! I got the relationships setup and working correctly. Now onto the next and most import issues. 1) On the Permit, I have a field named Status. On one parent, I may have 10 permits with 10 different permit statuses. I'm having trouble rolling the Statuses up since they're not a numeric value. 2) Ultimately, what I need to do is pull one large report showing all of the construction projects with all of their permits and the statuses of the permits. Just how the report link is setup, I need to take that same data and generate a report for my entire construction table. Is that possible? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
I have to run to a meeting now and will be off line for quite a few hours.  While this can be done with a whole bunch of reverse relationships, I would need to know what the maximum number of permits which would need to be rolled up to know if reverse relationships will work or we need another technique.  can you tell me more about these Status choices?  Is it a limited number of choices for the statuses.  Also, is there a limited number of choices for the types of Permits?  That would help me suggest a solution.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
There could be a maximum of 10 permits for one construction project. There are 15 permit statuses to choose from in a multiple choice field. There's only one type of permit, so hopefully that makes it easier. I'm messing around with the reverse relationships to see if I can get it to work in the meantime. I'll update you if I crack it. Thank you so much! 
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Alright, after taking a hiatus from the community. I've decided to come back and prevent the creation on unnecessary reverse relationships. 

Now that you have a joined table, you can build the report on the joined table, and just group by the project, then you can see any and all the details of the project or permit you need to.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
I feel honored that you returned on my behalf :) That definitely helped me with #2 on my list! Thank you so much! 

I would still like to figure out how to pull the permit statuses onto the construction record. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
OK. I am in the car now so this is really Siri talking. So given that there is a maximum of 10 and while Matthew won't like this approach, it can be done with 10 verse relationships. I have one more question before I explain the technique. Is there typically only one permit per trade. I.e. is there only one electrical permit and one plumbing permit. Or are they kind of random and you could have more than one electrical permit. I am just trying to think of when these fields are rolled up to the parent whether they can have nice field names or and be in a consistent sequence.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
You're devoted, I like it! All of the permits are for underground work. The only thing that differentiates the permits are the permit numbers. I have another table setup for aerial permits. So whatever I end up doing to the permit table, I'm going to replicate on the aerial permit table. Hope this all makes sense. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb

Reverse Relationships

Sometimes you need to float text information up from child records to a parent record.  But you find that there is no “Summary” field type which can do that in a relationship.  Until there is a better way, we use Reverse Relations.

Here is an example where there is a Relationship where 1 Project has Many Project Permits.

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

2.     Make a new “Reverse Relationship” where 1 Project Permit has many Projects.  Yes, that does sound backwards, but we need the child to be the Parent so we can lookup text field(s) or other fields from Project Permits to the Project.  On the right side of the relationship for the “reference field”, do not allow Quick Base to create a new field for you but rather use the field that you just created, [Record ID# of Permit 1].

 3.     Look up the Permit Name and Permit # from the Project Permits table to the Projects Table.  Call them [Permit Name 1] and [Permit Date1]. Congratulations, you now have the 1st Project Permit fields floated up to the Parent Project records. 

4.     While you are there, clean up (delete) the two automatic fields on the left side of the relationship, the Add Record and the Report Link fields.

 5.     Go back to the regular relationship where 1 Project has many Project Permits.

 6.     Create a lookup field down to Project Permits of the [Record ID# of Permit 1].

 7.     Duplicate the Summary field on the left side of the Relationship, and call it [Record ID# of Permit 2].  Adjust the Summary condition to Summarize the minimum Record ID# subject to the condition that the [Record ID#] is greater than the [Record ID# of Permit 1].  In other words it is the 2nd most Minimum Record ID, ie the 2nd Permit entered.

 8.     Conveniently, when you duplicated that summary field which already had a reverse relationship built, QuickBase will have duplicated that reverse Relationship as well.  So locate that relationship and pull down the Permit Name and Permit date and call them [Permit Name 2] and [Permit Date 2].

 9.     Just now return to step 5 above and make as many loops as you need.  However, be cautioned, that too many loops will slow down your app.  I would suggest that 5 loops is OK and 10 is the max you should do.

 10.  Once you have all your fields, you may want to concatenate them into a block of text to use on reports so the report are not crazy wide.  The List function is excellent for that.  If you wanted it list them vertically, the formula would be

List(“\n”,

List(“ “, [Permit Name 1], ToText([Permit Date 1]),

List(“ “, [Permit Name 2], ToText([Permit Date 2]),

List(“ “, [Permit Name 3], ToText([Permit Date 3]),

List(“ “, [Permit Name 4], ToText([Permit Date 4]),

List(“ “, [Permit Name 5], ToText([Permit Date 5]))

(Edited)
Photo of Neal

Neal

  • 70 Points
I have created screenshot prototype :
I have assumed that values of permit-status are shared by different types of permits.

NealPatil @ gmx.com

Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
Thank you so much for the detailed steps, you don't know how helpful you've been in making this huge transition for our company's app. Thank you again! 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
OK, post back if you get stuck anywhere.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
I'm finally working with real data in the new table- "Dependencies". I'm having trouble with the maximum summary dates not working now. What I have is a lookup from the original Permit table to the Dependency table, then a summary to the Construction Project table. It used to work when I didn't have the middle table. I'm not sure what I'm doing wrong now. Thank you! 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
Have you populated data into that middle table?  Are there records there?
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
I populated data in it. I have a Maximum Summary Date if the field is not blank but it's still populating a Maximum Date. I found a work around, it's not the cleanest but it does the job. I have a count of all the blank date fields and a formula field that will only use the maximum date if the blank date count is 0. Not sure if this is the best way. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,240 Points 50k badge 2x thumb
It sounds like you know what you want the logic to be, and counting the blanks needs to part of your logic. So it makes sense to me.
Photo of Dana

Dana

  • 532 Points 500 badge 2x thumb
awesome, thank you for confirming!