AnsweredAssumed Answered

Trouble assigning a primary key value to a new record

Question asked by ruhlanma on Jul 29, 2014
Latest reply on Jul 29, 2014 by philmodjunk


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