Expand all | Collapse all

One to Many

  • 1.  One to Many

    Posted 12-28-2018 13:49
    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.

  • 2.  RE: One to Many

    Posted 12-28-2018 15:23
    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.

  • 3.  RE: One to Many

    Posted 12-29-2018 15:49
    Okay, I feel like a dolt or perhaps just new. Where do I set the form property to show the child record? Thanks...

  • 4.  RE: One to Many

    Posted 12-29-2018 16:07
    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.

  • 5.  RE: One to Many

    Posted 12-29-2018 18:31
    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!!

  • 6.  RE: One to Many

    Posted 12-29-2018 18:40
    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?

  • 7.  RE: One to Many

    Posted 12-30-2018 03:50
    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.

  • 8.  RE: One to Many

    Posted 12-30-2018 13:54
    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.

  • 9.  RE: One to Many

    Posted 12-30-2018 14:06
    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? 

  • 10.  RE: One to Many

    Posted 12-30-2018 17:40
    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

  • 11.  RE: One to Many

    Posted 12-30-2018 19:07
    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...

  • 12.  RE: One to Many

    Posted 12-30-2018 19:42
    Can you contact me at directly via the contact info at QuickBaseCoach.com

  • 13.  RE: One to Many

    Posted 12-30-2018 21:16
    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.