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!
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.
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.
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(“ “, [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]))