3 Replies Latest reply on Dec 22, 2014 3:55 PM by philmodjunk

    Creating new values in related table without duplicating

    SamRembert

      Title

      Creating new values in related table without duplicating

      Post

      I've been reading through various related forum threads, but not sure I've found one which answers my specific question.  I'm also blurry-eyed, and may just need a little slap in the face to get me to figure out what's going on.

       

      I've got a database with multiple tables.  The main list (table) contains a list of fixtures, quantities, locations, and suppliers, amongst other data.  Another table should contain just the fixtures being used.  A third table would keep a list of suppliers used.  

      Now, I could keep this all in one table and use value lists from that, but what I don't want to happen is that if I delete a record in the main list, or even all of the records, that the value lists get lost.  However, I don't want to manually create value lists, either.  I want them to update as info is entered (but never get deleted).  Make sense?

      So, essentially, as I build the main list, the lists of fixtures and suppliers will update themselves, and populate into value lists.  I've got that part working using related tables and telling the relationship to create new records in the fixtures and supplier databases.  However, it creates a new record for every record I create, even if it already exists..  So, if record 1 in the "Main List" contains one WhizBang fixture, a new record is created in the "Fixtures" table, as desired.  Now, if I want record 2 in the "Main List" to also be a WhizBang, and I choose WhizBang from my pop-up menu, it creates a second record for WhizBang in "Fixtures".   

      What I want to do is whenever I enter a new fixture in the "Main List", for it to update the "Fixtures" list, but if the fixture already exists in "Fixtures", it just uses the existing data, instead of creating a new record.  Seems simple, and I feel like I've done this before, and am just missing something simple.

      Thanks!

        • 1. Re: Creating new values in related table without duplicating
          philmodjunk

          Sounds like you have something wrong with your layout design. Selecting or even entering a new value in a field on your main layout should have no automatic effect on the data in your related tables--no matter if "allow creation..." is enabled or not. Thus, I suspect something is not right with your design. (or there are some key details missing from what you have currently in place in your database.)

          I would use a "new" button. If the user pulls down the value list and can't find the desired value, they click "new". A window, custom dialog or popover opens where they enter the data for a new entry in the value list and a script then takes the data from these fields and creates a new record in the related table.

          • 2. Re: Creating new values in related table without duplicating
            SamRembert

            Thanks Phil.  That's my workaround for now, having the "new" button.  

            I created a very basic database to test out what I'm trying to do, but I don't know how to upload that here.

            • 3. Re: Creating new values in related table without duplicating
              philmodjunk

              You can't upload files here, but you can upload the file to a file sharing site such as Drop Box and then post the down load link here.

              Two other observations:

              You can set a "unique values" validation rule in the values table to catch and prevent the generation of duplicate records.

              With a bit of creativity, you can use a relationship with that "allow creation" option enabled such that a Set Field step from the context of the main table will create new records in the value table, but only if the value is not already present in the table. This would almost certainly require adding a second table occurrence for the values table using a different match field pair for the relationship.