3 Replies Latest reply on Jul 29, 2014 10:31 AM by philmodjunk

    Trouble assigning a primary key value to a new record



      Trouble assigning a primary key value to a new record


           My situation involves users who have independent copies of the data each on their own ipad.  Periodically they perform an export and an administrator imports these into a master database.  I know this isn't the best approach, but it is a temporary solution until we find time to deploy a server based solution.  In either case the problem still remains that the users will have an independent copy while offline in the field.  So when a new account record is created, the account needs to be assigned an ID.


           So my account table has an accountID field.  When a new record is created, my thought is to assign a value to the ID field based upon the left 5 characters of the user ID (which will be unique) followed by a incremented number.  So if the user ID is RUHLANMA, the new ID would be RUHLA9001.  The next would be RUHLA9002 and so on.


           I'm trying to keep it simple.  So I was attempting to use the Auto-Enter and assign a Serial Number on creation with the next value being the function UPPER(LEFT(GET(accountname);5))+1 and incrementing by 1.  But the result in accountID field for the new record is "UPPER(LEFT(GET(accountname);5))+1"  The next record is "UPPER(LEFT(GET(accountname);5))+2" and so on.  So apparently I can't plug a function into the "next value" field on the Auto-Enter tab.

           I can't imagine this is a unique problem.  Does anyone know of a solution?


           Thanks in advance

        • 1. Re: Trouble assigning a primary key value to a new record

               Here's a better idea. A feature was added to FileMaker to specifically deal with this issue.

               Define a text field with this auto-enter calculation:

               Get ( UUID )

               Use this field as your primary key. The Uniquie Universal ID code generated by this function is intended to be unique across all tables and devices.

          • 2. Re: Trouble assigning a primary key value to a new record

                 Wow!  That's pretty cool.  Thanks PhilModJunk.  

                 However, I really need to limit the length of the assigned value to 15 characters.  We use this value, sort of like a phone #, to quickly lookup a customer.  Having to type the string producted by Get(UUID) would be pretty messy for a quick search by ID.  It would also require changes to the structure of our master database to accomodate such a long string value in both parent and child tables that use this field in relationships. 

                 Is there no other possibility?



            • 3. Re: Trouble assigning a primary key value to a new record

                   Of course there are, but what I suggested is what you should use for your primary key. Don't use it for looking up customers. What you describe is an ID field, but not one that should be used as the primary key to link records in a relationship.

                   An ID that starts with part of a user's account name followed by a serial number doesn't strike me as a practical ID for looking up records. A name search that can also filter by an auto-entered account name sounds much more user friendly and there are a number of ways that you can set that up for finding records.

                   To get the ID that you want here, use two fields. Define one as an auto-entered serial number and then define a text field to auto-enter the serial number field's value combined with the text that you want in an auto-enter calculation.