A common issue has occurred over the years when it comes to Item Based management of data in Autodesk Vault. Oftentimes users want to have a single common numbering scheme for both Files and Items. Until recently this has not been possible, and users would have two options available to them for Item based numbering.
Firstly, users could set up the numbering schemes to be the same but change the limits for items and files. And example of this would be to have the sequential numbering scheme for Items to range from 100001-130000 and the files to range from 130001-999999. This ensures both Items and Files will be created in the same numbering scheme.
Alternatively, users can create two numbering schemes for Items and Files. As an example, users might create a file numbering scheme that is prefixed with “FIL” and an Item numbering scheme that is prefixed with “ITM”. This solution is not ideal if the user wants to have a consistent numbering scheme.
However, In Vault 2020 a new feature was added to the Numbering schemes where users could choose the Number Generator. The standard options are Simple Number Generator (Default) and Centralized Number Generator. The Centralized Number Generator was added for Vaults that are replicated across multiple sites. This allows companies operating from the same vault data across multiple servers the ability to ensure the same number is not taken out at two different sites.
Added to this functionality Autodesk allowed users to create a Custom Number Generator.
The custom number generator is a database that is created in MySQL and every time a new number is requested a new line is added to the database and increments by 1 integer. This means that users can ensure the number they are requesting is always unique irrespective of whether they are requesting a file number or an Item number.
In this article I want to discuss how to set up the Custom number generator, explain some of the code used in the creation and detail how to use the custom number generator in the file naming schemes. Users that want to do this should download the Zip file from the following location as it contains the .dll file they will need in step 2:
To utilize the custom number generator there are five steps to creating a custom number generator.
- Set up a new database in the AUTODESKVAULT SQL sever.
- Place a new dynamic library in the Program files directory for the ADMS console
- Update the web configuration file
- Restart the Internet Information Services
- Create the numbering schemes and direct them to the new database
Step 1. Setting up the new database
NOTE: To create a new database the user will need access to the Autodesk SQL Sever through Microsoft SQL Server Manager Studio. If the user does not have adequate permissions to do this the Server administrator will need to complete these steps.
To create a database the user needs to open Microsoft SQL Server Manager Studio and log into the AUTODESKVAULT server. Once connected the user will need to perform 2 queries. The first query creates a new database and the second query sets up the numbering scheme. The first query is as follows:
/* MyNumbering Sample
Step 1 - Create sample data base 'MyNumberingDB' */
CREATE DATABASE MyNumberingDB
/*Continue executing Step 2 to complete the SQL setup for this sample*/
Everything in this code snippet that starts with
/* is a comment and as such is not actioned by the query. So there is only one line of code here which is:
CREATE DATABASE MyNumberingDB
This is fairly self-explanatory and simply creates a database called MyNumberingDB. Once this query has been run a new database will appear in the Object Explorer panel:
Now a new database has been created the user will need to define how the numbers are generated. This is done with another Query shown here:
/* MyNumbering Sample
Step 2 - Add table to database created in Step 1 */
Create table autonumber (id int identity, number as (right('000000'+convert([varchar], [id], 0), (6))), value varchar (10))
Again, the comments are ignored so there are two lines of code here. The first line (
use [MyNumberingDB]) tells the program which database to use when running the following lines of code. As we named the database MyNumberingDB this is what goes in between the square brackets.
NOTE: Due to the dynamic library that we add to the ADMS Program files this should always be named MyNumberingDB.
The next line if code is where the user defines their numbering scheme. I will work through this to explain what each section of it is doing so users can modify the code to suit their specific numbering scheme.
The first section of this code is the Create table autonumber () command. This creates a new table in the MyNumberingDB database and the new table will be called autonumber.
The parentheses for this command contain all the columns that will be created in this table and what those columns can/will contain. In this case there are three columns that will be created. The three columns are
The first column that is creating an id. This is specified as an integer value (
int) and will be managed by the database to auto increment every time a new row is created. This functionality comes from the
identity command after the data type. However, the auto increment feature is only an integer and will start at 1 and increment by 1 as new rows are created. This is not the format most people like to use when numbering Files or Items. In most cases the number should consist of a set number of digits padded with zeros (e.g. 000012). This is where the second column comes into play.
The second column being creating is called “number”. The values in this column will also be generated but they will be generated by a formula rather than by the database. This formula is defined by two functions and will create our zero padded number for use in our Autodesk Vault naming schemes.
Unless you have experience programming, trying to read this formula is a little confusing so I will break it down into its 2 functions, explain both functions and then give an example of how it works.
The “right” function is used on string data types. It requires two parameters which are an initial string and an integer. The purpose of the function is to return the last ‘x’ number of characters from the initial string where ‘x’ is given by an integer. The following is an example of how the function works:
You can see from this example that the initial string parameter was “Hello” and the integer parameter was 3. Starting from the right most character of the initial string and counting back 3 places the result is a 3-character string “llo”
The next function to consider is the convert function. The convert function consists of three parameters
In this article I will not go into much detail regarding these parameters but only point out the key elements which are:
- The data type being specified is [varchar]
- The expression is [id]
What this tells us is that the convert feature will take the expression [id] and convert it to a [varchar] data type.
NOTE: A varchar is a data type that stands for variable character and is defined as an indeterminate length string data type
Now, when we look at this as a whole it starts to make more sense. Let us consider an example of this:
- A new row is created
- The id column is populated with an auto incremented number, in this case 12
- Convert is used to convert the integer value 12 to a string value “12”
- The string value “000000”is added to the string value “12”
- It is important to understand that when two strings are added together in a program it will literally append one string with the other. So, in this case the new string value is “00000012”
- Then the expression takes the last 6 digits from this string and populates the number column with this value (i.e. 000012)
It is important to understand this flow as the very first number created in this case will be 000001. If the user wants to change how the numbers are created they will need to modify the expression used for the number column. As an example, recently there was a need to create a number generator that stared at 1000001. For this scenario the expression was changed to the following:
number as (convert([varchar], (1000000 +[id]), 0))
Notice that with this expression the id is added to 1000000 before being converted. As this is two numbers this addition is done mathematically and hence will produce the result 1000001.
The last column on this table is a string based column with a temp value and can be ignored for the purposes of this article.
Step 2: Adding a dynamic library to the program directory
Unzip the folder downloaded earlier and inside there will be a MyNumbering.dll file. The user should copy this file and paste it in the following location:
C:\Program Files\Autodesk\Vault Server 2021\Server\Web\Services\bin
NOTE: This location is different from Vault Professional 2020 where it was located here:
C:\Program Files\Autodesk\ADMS Professional 2020\Server\Web\Services\bin
Step 3: Update the Web.config file
For this step it is important to create a copy of the file we will be editing incase an error occurs. Navigate to C:\Program Files\Autodesk\Vault Server 2021\Server\Web\Services (remember this location is different for Vault Professional 2020) and locate the Web.config file. Copy the file to an easily accessible location (I recommend the desktop). Open the new file created on the desktop and navigate locate the following segment of code.
NOTE: To edit the file use either notepad or SQL Server Manager Studio
It is close to the end of the document. Paste in the following line of code after one of the other number providers but before the </NumberProviders> tag.
<numberProvider name="CustomNumberingProvider" type="MyNumbering.MyNumbering, MyNumbering" canCache="false">
<!--<initializationParm value="Your Value Here" />-->
The config file should look like this:
Save the file and leave it on the desktop for now.
Navigate back to the original web.config file location:
C:\Program Files\Autodesk\Vault Server 2021\Server\Web\Services
Rename the original web.config file to a new name (e.g. web-OLD.config) so, in the case of an error, you can restore this file as the default and copy the modified web.config.xml that is on the desktop in to the services directory. It should look something like this:
Step 4: Reset Internet Information Services
Open a command prompt on the server as an administrator. To do this, search for “cmd” in the windows search bar and when Command Prompt shows up right click and select “Run as Administrator”.
In the command prompt type IISreset and hit Enter. Once complete the Command Prompt should show the following:
Step 5: Create a new numbering Scheme that uses the Custom Number Generator
Now all the background work has been done a new numbering scheme can be created. Open Vault professional 2021 Client and navigate to Tools>Administration>Vault Settings
In the settings dialogue box go to the behaviors tab and select “Define” in the “Numbering pane
When creating a new scheme select the Custom Numbering Provider in the Numbering Generator drop down box.
Set up the same naming scheme for Items and Files and you are all set. Now every time a new number is requested it will always be unique whether being requested from the file side or the Item side.
The ability to tie together File and Item numbering from a single source is a big boon for Vault professional users that utilize the Item Master environment. However, this process is quite involved and requires some high-level permissions. So, my recommendation is to only implement this process if you have the backing of the other users and it is an absolute necessity.
NOTE: Users perform this process at their own risk and IMAGINiT cannot be held accountable for any issues arising from following the steps laid out in this guide.
About the AuthorMore Content by Chris Williams