Hello,
I would like to create a CDT with a primary key which must be an Auto-generated value but with a prefixed value.
Ex: "APP_01",
Where the number 01 must be an auto incremented value. Is it possible to do so? If yes please suggest me a possible way to do it.
I was unable to understand the following:
When using a sequence to generate ids (such as @GeneratedValue(strategy=GenerationType.SEQUENCE)), we recommend allowing the system to name the sequence instead of providing a name with the generator attribute. If you choose to define a sequence generator with the generator attribute in conjunction with the @SequenceGenerator annotation, then you must provide a name, and that name must be unique across all sequence generators in the system to avoid uniqueness constraint violations
@GeneratedValue(strategy
=
GenerationType.SEQUENCE)
generator
@SequenceGenerator
https://docs.appian.com/suite/help/22.2/Database_Schema_Best_Practices.html
Please help me with the above.
Discussion posts and replies are publicly visible
You could roll your own solution to this. You could create a database table that has a column to hold the prefix e.g. 'APP' in your example, and then another column that holds the latest sequence number e.g. '01' in your example, and then write a Stored Procedure that takes those two values, increments the number part of it and writes that back to the same table, and then returns the concatenation of those two values. You can then call that Stored Procedure from Appian and use the returned value as your custom key for use where you intend to use it.
Thank you. Will try.
Stewart gave a good solution, but I'd also be curious how you plan to use this. Like do you actually need the value in the database or are you just hoping to have a friendly way of displaying the PK in Appian? If it's the latter, I'd recommend just storing the integer in the database and then have some method of formatting the result in Appian. For example, you could create a custom field that concatenates an app name in front of the identifer and it should work perfectly.
Hi Peter,
Since I am new to Appian I've been exploring multiple ways to make a combination of Alpha-numeric values to display the User-Id. I wanted it to be stored as a combination of alphabets and numbers in the database. I have already tried the way which you have mentioned. I tried using the Alphabets and & concatenation using the PK value in the interface. Just wanted to know if there is any possible way to generate it automatically in the DB with no multiple columns.
Hi Chaitanya,
The below link will help you.
www.sqlteam.com/.../custom-auto-generated-sequences-with-sql-server
Hi Manish,
Thank you for the link!
Keep in mind that you can expose other columns to end users and use the PK only "behind the scenes", There are pros and cons in DBs to not using integers as PKs. Relational DBs will implicitly build indexes on PKs to make lookups quicker and then whole numbers will generally be quicker and less resource consuming when querying by the PK.