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