Forum Discussion

LeslieG's avatar
LeslieG
Qrew Cadet
8 years ago

How to relate many records to many other records in the same table?

I have a table called "Accounts" where we store all of our customer data for order tracking and invoicing. Some accounts are individual artists and some are art galleries. Here's my issue: We need to ...
Relationships
QuickBaseCoachD's avatar
QuickBaseCoachD
Qrew Captain
8 years ago
Yes, this is possible but it is confusing to set up especially of you have not set up a many to many relationship before.  i just did one myself a week ago and it worked fine.  You may want to contact me off line for assistance if we can't communicate well enough here.

You will need to set up one additional table called Artist Gallery Connections.,

I imagine that you have multiple choice field indicating whether the Account is an Artist or a Gallery.

Make a Relationship where 1 Account has many Artist Gallery Connections. Rename the field from [Related Account] to be called [Related Gallery].  Lookup the field for Account name and rename it to be called [Gallery Name].  Check the field properties for the field [Related Gallery] and ensure that the Proxy field is set to Gallery Name.  

On the  form for the field [Related Gallery] there will be a drop down field to choose an Account.  Make a report filtered for just galleries as a drop down list.  Edit the form properties ot use that report so that when you select an "Account" you will only be offered a list of Galleries.

Now ....repeat for Artists ...

Make a Relationship where 1 Account has many Artist Gallery Connections. Rename the field from [Related Account] to be called [Related Artist].  Lookup the field for Account name and rename it to be called [Artist Name].  Check the field properties for the field [Related Artist] and ensure that the Proxy field is set to Artist Name.  

On the  form for the field [Related Gallery] there will be a drop down field to choose an Account.  Make a report filtered for just Artists as a drop down list.  Edit the form properties ot use that report so that when you select an "Account" you will only be offered a list of Artists.
  • LeslieG's avatar
    LeslieG
    Qrew Cadet
    8 years ago
    Wow, this makes total sense and is technically working, so thank you!

    The only hiccup is that I can't get the embedded report to show the related gallery/artist after it is added. I can find the record in the new table, though, so I know it's been successfully created.

     I checked the field properties for the report on the accounts form, and it's filtering the report by matching Record ID# to the [Related Gallery] or [Related Artist] appropriately. Is there any other reason it may not be showing up on the account record?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    The embedded report should have no filters.  The report link field itself should be all the filtering required.
  • LeslieG's avatar
    LeslieG
    Qrew Cadet
    8 years ago
    That issue is resolved, but now I have a new one! The form for setting up the gallery-artist connection is always autopopulating the "related artist" field with the account name, even if it's a gallery. See screenshot. I don't even care if you have to reselect the gallery in "Related Gallery", I am just concerned it will cause confusion when it appears as "Related Artist." 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Hmmm, I'm not I can solve this one without looking at the app.  Can you explain what you mean by auto populating?  In your Accounts table, my understanding was that you had a single field which held either the Gallery name or the Artist name.  But in fact do you have those in separate fields in the Accounts table?
  • LeslieG's avatar
    LeslieG
    Qrew Cadet
    8 years ago
    That's correct -- There is one field for Account Name on the Accounts table.

    What I mean by auto-populating -- I begin on the Accounts record for "Yancey Richardson Gallery" and click the button to "add an artist". This takes me to the form, where there is the "Related Gallery" and "Related Artist" fields. Each dropdown has it's custom record picker, which correctly filters, but when you arrive at the form the account name is filled in for "Related Artist" while "Related Gallery" says "Make a selection..." So it's somehow forcing whatever account record you started on in into the "Related Artist" category.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    OK, can you post the formula for the Add button and I will correct it.
  • LeslieG's avatar
    LeslieG
    Qrew Cadet
    8 years ago
    Sure, here is the formula for the "Add Artist" button:

    URLRoot() & "db/" & [_DBID_ACCOUNT_ASSOCIATIONS] & "?a=API_GenAddRecordForm&_fid_8=" & URLEncode ([Record ID#])& "&z=" & Rurl()
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Can you tell me the field ID for the two fields. The field ID is available off the usage tab for the field or hovering over the field in the field list. 

    Related Artist
    Related Galley 

    Thx
  • LeslieG's avatar
    LeslieG
    Qrew Cadet
    8 years ago
    Yup! 

    Related Artist - 8

    Related Gallery - 6
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    I think that what you want is to rename the button to be called Add Artist  rename the field and the Link text in field properties - both.

    Then duplicate that field and call it Add Gallery (and also edit the link text to match) and edit the number 8 in the formula to be a 6.

    Put both buttons on the Accounts record form.