6 Replies Latest reply on Nov 2, 2011 1:03 PM by CathyDurso

    Help? Master list of terms link to individual records

    CathyDurso

      Title

      Help? Master list of terms link to individual records

      Post

      Hello,

      I've been trying to figure out if there is a calculation or function that can do what I am trying to do and I haven't had much luck. Here is what I'm trying to do:

      I have a table containing records of artwork. Each artwork record has one field containing a list of subject terms. I want a related table that would be a master list of all the subject terms from all the artwork records. This could be either one field containing a list of all the terms, or each record could be its own subject term (I'm not sure which way would suit my needs better, or if it would even matter). I do want the master list of terms to be in ABC order. I want the Master List of subject terms to update automatically when a new subject term is added to the list of subject terms for each of the artwork records in the related table. I also want the subject term field for my artwork records to fill in with terms from the master list - if I started typing the beginning of a word, it would fill in the rest of the word using the terms from my master list.

      Does this make sense? Is this possible? Any tips would be appreciated!

        • 1. Re: Help? Master list of terms link to individual records
          mgores

          Sounds like having a separate table with each term being a separate record would be best.  That way you can sort it alphabetically and be able to add new terms should you need to.  For each record the artwork table you could have a portal to list or add to the terms associated with it and set up the field to be an auto complete dropdown from existing values in the terms. 

          • 2. Re: Help? Master list of terms link to individual records
            CathyDurso

            Thanks for the suggestion, I hadn't thought of using a portal (not very familiar with them). I tried figuring that out, but I can't get it to work. I have a new table that is related to the artwork table, and in the new table I made a portal and set it to show records from the subject terms field in my artwork table. It didn't do anything. I don't know how to make it update the records automatically without me having to type in a new record each time I type a new subject term in the artwork table.

            • 3. Re: Help? Master list of terms link to individual records
              philmodjunk

              I suggest describing the relationship between a specific artwork and the specific terms you want listed. Do you want all the terms listed for all the artworks or only certain, specified terms for each artwork?

              • 4. Re: Help? Master list of terms link to individual records
                CathyDurso

                All of the artworks wouldn't have all of the subject terms, but they can overlap. For example, more than one work may have "sunflower" as a subject term, but maybe there is only one work that has "tomato" as a subject term.

                My dream master list would be automatically updated to include every subject term that is used to describe the artworks, but each subject term would have to be listed only once, and in alphabetical order. I could just list them all out manually, but it would be nice to have that list linked to the artwork table.

                • 5. Re: Help? Master list of terms link to individual records
                  mgores

                  Actually to relate the artwork to subject term (assuming each artwork can have multiple subject terms and each subject term is used for multiple artworks)  you need a "join table" between them.  This join table can contain just 2 fields, the artworkID and the subject termID.  Each combination of artwork-subject term will be a record in that join table.  If you want to have a selection list so that you can see all of the possible terms, you can use a portal to the other table with a cartesan relationship (X rather than =) and set up a button in the portal row that creates the record in the join table associating that subject term to the artwork.

                  I have used a layout with 2 portals, one based on the cartesan for selecting the other one the join table to see which have been selected for entry.  Then another layout showing only the portal to the join table for viewing.

                  here is a basic relationship structure, showing products and categories.  You would substitute artwork and subject terms.  This one is set up to go both ways, products to categories  and categories to products.

                  • 6. Re: Help? Master list of terms link to individual records
                    CathyDurso

                    This is kind of going over my head. I'll keep working on it. Thanks again.