Skip to main content
Question

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

  • June 15, 2017
  • 0 replies
  • 165 views
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?
    This topic has been closed for replies.

    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.
    • TanishaElias
    • Quickbase Alumni
    • October 4, 2018
    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:)
    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. 
    Terms & ConditionsAccessibility statement