One to Many

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
I have a 'Contacts' table and need to create relationships for each contact in that table. For example, Mary (primary contact) has a father (Fred), mother (Betty) and sister (Sue).  I need to, while viewing Mary's Contact Main Form, see the list of Fred, Betty and Sue on Mary's view with the ability to click on Fred's row and be taken to Fred's Main Form. Additionally, on Mary's form in the section that shows the relationships there is a field for the type of relationship (e.g. a field Relationship would hold the value 'Father'. And conversely, on Fred's record it would indicate that Mary as his daughter. 

Similar to how the George Jones record displays Services.

Thanks in advance for any help.


Photo of Don Sloane

Don Sloane

  • 150 Points 100 badge 2x thumb

Posted 3 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
In order to show the child record on the Parent form, you put the Report link field on the form.  Typically that field is created when you build the relationship.  Set the form property to show the child record on the form, as opposed to just a link.

That will allow you a direct link to "Fred" in your example.

The other part of your question is to want to show child record data directly on the parent record as opposed to as an embedded report.

You can use the new Combine Text summary field on the Relationship to do this.  Make a summary field, chose combine text, and summarize the name field subject to the filter where the relationship is equal to Father.
Photo of Don Sloane

Don Sloane

  • 150 Points 100 badge 2x thumb
Okay, I feel like a dolt or perhaps just new. Where do I set the form property to show the child record? Thanks...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Once you have put the Report Link field on the form, then there is a radio button labelled

Display the related projects directly on the form

It will default to using the default record picker fields to know which columns and sort to show, but then you can make any report  and set the form to use that report for the embedded report.

As an aside, the embedded report can be of any type including summary reports and charts.


Photo of Don Sloane

Don Sloane

  • 150 Points 100 badge 2x thumb
Again, many thanks. I've got this concept but still not getting what I'm looking for.

I want to add a connection to Don Sloane based on a relationship between Contact table and Connections table.





Connected To is a text-multi choice field which is fine.



Then once saved I want the user to be able to go to directly to Josephine's record in the Contacts Main Form. The formula for the button (see below) is "URLRoot() & "db/" & [_DBID_CONTACTS] & "?a=er&dfid=3&rid=" & ?? which works. My challenge is that I can not figure out how to capture the record ID for Josephine as I only have the Name established in the relationship. When the user selects a Name in the Connected To field (above) how can I get the Record ID# associated with that record, Josephine in this example. I don't want to have the drop down display Josephine - 30 for example by including the Record ID# as part of the Default Record Picker.






Again, many thanks in advance!!


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
I'm not understanding your actual question here.  Is this your question?

Then once saved I want the user to be able to go to directly to Josephine's record in the Contacts Main Form. 

I see in our screen shot it shows a button to Go To Connection.  Is that the button that you are trying to get working?
Photo of Don Sloane

Don Sloane

  • 150 Points 100 badge 2x thumb
Yes, that's exactly what I'm trying to get working. I can't figure out how to capture the Record ID# for Josephine when I create the record on the Connection form.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
The Record ID is likely the field which is the reference field on the relationship, ie the contents of [Related Contact] or perhaps it’s called [Related Contact2]

But I don’t understand why you feel you need to capture it since you could set the Josephine field [Connected To] to show as a link to Josephine.
Photo of Don Sloane

Don Sloane

  • 150 Points 100 badge 2x thumb
And again, many thanks. Your suggestion makes perfect sense. Was not able to figure out how to make the [Connected to] value and link. Can you provide guidance on how to do that? 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Ok, np,


Once again I would just mention that the no code way to do this is to enable the checkbox on the field properties for the [Connected To] field, but here is the formula to display a record.


Urlroot() & "db/" & [_DBID_CONTACTS] & "?a=dr& rid=" & [Related Contact2]


You will need to get the correct name of the Contacts table from the Advanced Properties tab if 5he Settings for the Contacts Table.

The field type is formula URL
(Edited)
Photo of Don Sloane

Don Sloane

  • 150 Points 100 badge 2x thumb
You have the patience of a saint, I appreciate your time and expertise. I am a pretty highly educated and seasoned (euphemism for old) professional (in my field) and struggle feeling like a complete idiot in this regard. ARGH!

That said, I fear I have overly complicated what I'm trying to do. From the top, I have a table Contacts with let's say a record for Bob, Billy, Mary, etc. I have another table Connections. I need to be able to create relationships between these records int eh Contacts table. For example, a section on Bob's Main Contact Form called Connections that would show

Billy | Father
Mary | Mother

where values in the column with the names (Billy, Mary) would be hyperlinked to that values Main Contact form respectively. Furthermore, on Mary's Main Form the Connections section would display

Billy | Husband
Bob | Son

Is this a one to many relationship between Contacts and Connections as I've tried to set up here...



or perhaps a many to many between Contacts and Contacts and if it's the latter I have no idea how to set that up. 

Can you help clarify, point me to an app that I can see an example of how to accomplish the above, or recommend a developer that I can contract with?

Again, many thanks...



Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
Can you contact me at directly via the contact info at QuickBaseCoach.com
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
I decided I needed some entertainment as this in fact is a quite a bit of a brain teaser.  Here is a link to an app that you can try.

https://yqc.quickbase.com/db/bpaam5p6s