Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Custom AutoNumbering
I am trying to find a good way to generate an AutoNumber in a multi-client database. I don't want all clients to be using the same AutoNumber sequence. Ideally I would like each client to have their own sequence yet be using the same AutoNumber field. Any ideas on how I can incorporate this since there seems to be nothing implemented natively for this case?
ID
1041
Category
Customization
Author

Jeff Zortman
Date Created
8/30/2016 8:34:09 AM
Date Updated
4/19/2017 10:26:06 AM
Comments
Gii Systems 8/30/2016 9:24:20 AM
Hi Jeff, I guess you could do this as follows:

Lets assume you have the Id column for the Client in this table - I call this [Client Id]
1. Create a many to many relationship within the same table with a match condition where [Client Id] = [Client Id].
2. Create a summary column within this relationship to count the number of records for this client and call it: [No of Records for this client]
3. Create a Formula Numeric column called [Client AutoNumber] with a formula such as: [No of Records for this client] + 1
4. You can then use formula columns to prefix this number. Maybe a formula Text like: List("-",[ClientId],[Client AutoNumber].

This will create a Unique number that looks like 15-1 the next in the series will be 15-2 etc.

Hope this helps?
Jeff Zortman 8/30/2016 10:50:52 AM
Good idea. I have tried something similar to that. However there is the possibility to delete a record in this table and then effectively drop the "count" thus creating the possibility of having two (or more) records with the same number. I believe the native AutoNumber handles this case.
Kirill Bondar  Staff  8/30/2016 12:07:48 PM
At step 3 I'd rather calculate maximum of key column stripped off client Id
Jeff Zortman 8/31/2016 3:11:21 PM
Seems to be working well using the Max. I suppose there is still the remote possibility that two users could add a new record at the same time and get the same number?
Kirill Bondar  Staff  8/31/2016 6:16:51 PM
If the column to store client-specific ID is marked as unique, there is no change to get the same number as uniqueness is enforced at database level - no records with same ID values can exist in the database. Even if two users will save new record exactly at the same time, one will get a message about key value is not unique. On second attempt to save ID will be recalculated and likely saved successfully.
Jeff Zortman 9/1/2016 8:25:20 AM
My client level id column does not have a client id number in it, so I am unable to mark it unique since it is possible other clients may have the same number. Trying to remember back to my software developer days if it was possible to put a unique constraint on multiple columns...maybe a future possibility?
Kirill Bondar  Staff  9/1/2016 8:35:38 AM
Find duplicates sample may help: https://www.teamdesk.net/find_duplicates

Page text contains instructions on how to modify it to prevent creating duplicates via validation rule
Jeff Zortman 4/19/2017 10:26:06 AM
I ended up implementing a Create Record trigger that takes care of advancing the number instead of using a column formula to set the default value. I made this trigger the first one that fires. I did this to get around the case of creating duplicates when multiple users are starting a record simultaneously. Although I wonder if there is still a very remote chance of getting duplicates?
Feedback
Back to Search Results