How to display text values of child records in many to many tables?

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
I'm new to Quickbase, so if these questions have been answered before, apologies - I couldn't find it in the forums. Perhaps I'm using the wrong terminology.

I have two tables, Individuals and Organizations. One organization can contain many individuals, and one individual can be associated with many organizations.

Question 1: I don't want to share any data except for which organizations relate to which individuals and vice versa. Is a many-to-many relationship the only option? Is it the best option? Perhaps this question depends on the answers to my subsequent questions.

Now, I have set up a many-to-many relationship, which seems to be working reasonably well. But it would be extremely useful to be able to see in each table's grid the actual related organizations in the individuals table, and vice versa.

Question 2: In other words, in the individuals table, how do I replace the report link to the join table with an actual list of the related organizations?

Question 3: If I do manage to the pull the data of the actual list of related organizations into the individuals , will I then be able to sort the tables by the related organizations listed?
Photo of Sam W

Sam W

  • 300 Points 250 badge 2x thumb

Posted 10 months ago

  • 0
  • 1
I believe that you are asking if on a Parent record to see a summary of the details in text information from child records (the children here are your Join table records) . There is not currently a Summary text type fields where you can roll up the children into a long text field.

However, using a technique called reverse relationships you can float up the data contained in a limited number of child records up to the parent.

Can you tell me what the reasonable number of children you need to flow up is.  Note that you can also have a message that tells the user that there are more.

I will post an additional response with how to do reverse relationships.
Here is an answer to the situation where 1 Job has many Operators.  this is a manufacturing situation where the Job (an Order) has many Machine Operators required to complete that job.  The "ask" was the same, ie to look at a report of Jobs and see on the report, all the Operators. 

This technique can be used for say up to a half a dozen "loops", but if you plan to get into say 20 loops then while it will work, Quick Base will need to do a lot of calculations to display the report, so the report will get slow if you have a lot of data.

As for sorting, I think that will be tough (ie not possible using native Quick Base) to do.  The data from the child record will float up in the order it was entered.

Reverse Relationship setup

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.
Photo of Sam W

Sam W

  • 300 Points 250 badge 2x thumb
Thank you so much! I'm going to have a go implementing this now.
OK, let me know how you make out.  Note that is you decide to float up say the 1st 5 children, you van male a summary field to count the # of children, and then also include in your concatenated field list

, IF([# children] > 5, " .. and see Parent for more") 
Photo of Sam W

Sam W

  • 300 Points 250 badge 2x thumb
Can I clarify: should this reverse relationship be with the many-to-many relationship's join table? Or should it be directly between the two master tables (i.e. getting rid of the join table)? 
It's with the Join table.
Photo of Sam W

Sam W

  • 300 Points 250 badge 2x thumb
Worked beautifully. Thank you.
Photo of Sam W

Sam W

  • 300 Points 250 badge 2x thumb
I'd be extremely grateful if you could help me with one further question:

My organizations table has two fields. Name and Acronym. e.g Quickbase and QB.

Thanks to your instructions, I am able to display the related organization name and acronyms in my master Individuals table. The thing is, not all of my organizations use acronyms.

Is there a way to write the list formula so that: it will first look to display [Acronym 1], but if the field is empty, it will instead display [Organization Name 1]?

(And also, is there a good guide to understanding Quickbase formula you could recommend?)!
No problem.

In the Join table make a formula text field called [Organization Name or Acronym] with a formula of

IF(Trim([Acronym])<>"", [Acronym],[ Organization Name])

The reason for the Trim is in case someone has entered an Acronym with just a few spaces.  Then, in each of your 5 reverse relationships lookup that field into your Parent table and concatenate them all together.

There is a master list of all the Formula functions here.

That would be a good bookmark to save in your favorites as it has examples for each function.

There may also be a general video in the video education resources at Quick Base university.

This is probably a good course here