Discussions

Expand all | Collapse all

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

  • 1.  How to relate many records to many other records in the same table?

    Posted 06-15-2017 14:56
    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?


  • 2.  RE: How to relate many records to many other records in the same table?

    Posted 06-15-2017 15:22
    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.


  • 3.  RE: How to relate many records to many other records in the same table?

    Posted 06-15-2017 19:36
    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?


  • 4.  RE: How to relate many records to many other records in the same table?

    Posted 06-15-2017 21:11
    The embedded report should have no filters.  The report link field itself should be all the filtering required.


  • 5.  RE: How to relate many records to many other records in the same table?

    Posted 06-16-2017 15:17
    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." 


  • 6.  RE: How to relate many records to many other records in the same table?

    Posted 06-16-2017 15:20
    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?


  • 7.  RE: How to relate many records to many other records in the same table?

    Posted 06-16-2017 15:44
    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.


  • 8.  RE: How to relate many records to many other records in the same table?

    Posted 06-16-2017 15:51
    OK, can you post the formula for the Add button and I will correct it.


  • 9.  RE: How to relate many records to many other records in the same table?

    Posted 06-16-2017 16:04
    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()


  • 10.  RE: How to relate many records to many other records in the same table?

    Posted 06-16-2017 16:12
    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


  • 11.  RE: How to relate many records to many other records in the same table?

    Posted 06-16-2017 16:18
    Yup! 

    Related Artist - 8

    Related Gallery - 6


  • 12.  RE: How to relate many records to many other records in the same table?

    Posted 06-16-2017 16:49
    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.


  • 13.  RE: How to relate many records to many other records in the same table?

    Posted 06-16-2017 16:59
    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. 


  • 14.  RE: How to relate many records to many other records in the same table?

    Posted 06-16-2017 17:11
    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


  • 15.  RE: How to relate many records to many other records in the same table?

    Posted 10-30-2017 21:28
    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.


  • 16.  RE: How to relate many records to many other records in the same table?

    Posted 10-03-2018 20:21
    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:)


  • 17.  RE: How to relate many records to many other records in the same table?

    Posted 10-03-2018 20:24
    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.