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

  • 0
  • 2
  • Question
  • Updated 2 months ago
  • Answered
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 be able to show a table on gallery records listing the artists we work with represented there, and a table on the artist records showing their respective galleries.

I related the Accounts table to itself but because it's a one-to-many relationship, I am either limited to one artist per gallery, or one gallery per artist, depending upon which record I start with.... I don't just want to create multiple relationships, since it looks wonky and there isn't a set number of artists or galleries that may be associated with the other....

Is it even possible to relate many records to many other records in a single table?
Photo of Leslie G

Leslie G

  • 248 Points 100 badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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.
Photo of Leslie G

Leslie G

  • 248 Points 100 badge 2x thumb
Yup! 

Related Artist - 8

Related Gallery - 6
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
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.
Photo of Leslie G

Leslie G

  • 248 Points 100 badge 2x thumb
I'm confused... That's how the buttons are already set up. I have a rule on the Accounts form to show/hide one of the buttons based on whether the Account Record is an artist or a gallery. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
OK, so I'm confused too.

One button should pre-populate the Galley, allowing you to select an Artist.  The other button should be vice versa. Maybe your form rule on which button to show is backwards or the button labels are opposite to what they should be.

Mark
Photo of Eric Whitley

Eric Whitley

  • 190 Points 100 badge 2x thumb
Huge thank you for this response. I was trying to do the same thing and also struggling. I had to re-read this a few times to pick up on what you were doing, but I think I also got there in the end.

What tripped me up was not realizing that an individual field in a form could be linked to a completely separate report as its data source. Finally realized "oh, he means create a report in the source list!" and then put two and two together.

Thank you - learned a lot from your walk-through.
Photo of Tanisha Elias

Tanisha Elias

  • 92 Points 75 badge 2x thumb
Hi Mark -- I am having an issue with something similar...

I need to create a summary report. All my data is on one table. But I have about 12 different fields with 5 different status categories, and I have to create one report showing a summary for each field reflecting the status. Because there are 12 different fields with 5 different status, I am having trouble bringing them all together in a report. Please help:)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
I suggest that you start a new question and explain with an example of what an example record look like in terms of  values in the fields and what you want the resulting report to look like.