Multiple Children with Multiple Parents

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