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?

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.

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?

The embedded report should have no filters. The report link field itself should be all the filtering required.

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."

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?

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

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
  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
  Qrew Captain
  8 years ago
  The embedded report should have no filters. The report link field itself should be all the filtering required.
- 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."
TanishaElias
Qrew Trainee
7 years ago
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:)
QuickBaseCoachD
Qrew Captain
7 years ago
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.

Forum Discussion

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

Related Content

Show related records

Custom Record Titles

Using Grid Edit to Edit Multiple Records at Once

Filter out some records on related table

Record Details