TeamDesk Knowledge Base & Support

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?

Eli Cohen 12/17/2022 9:16:34 AM

tried everything, how can I get a new number that is unique for a default of a new record? I currently use AssetID to track an asset and when I create a new asset, I want a new number to be calculated based on the max value or something. now I simply use Max([AssetID]) but that doesn't work in TeamDesk.