Data in reference tables can differ across environments, which directly leads to have different primary key values of same reference data in the tables across environments. But, knowing this fact, still some Appian applications use primary keys of reference data as constant, for example primary keys of workflow statuses are referenced using constants rather than their text value. If while deploying or during data migration, primary key changes, then code written using those constant references may break.
What is the best approach to code for above use case?
Discussion posts and replies are publicly visible
I am a huge fan of storing such values in constants. My approach is:
- Each value is one constant
- To get a list of these, I create an expression returning a list of the constants in the desired order
I never had any issues with this approach. But, as soon as you need to allow the user to adjust values, like country codes or other semi-static things, a reference table in the DB can be the right solution.
First of all, you can try to keep the primary key of reference table data consistent across enviornment. This can be easily achieved by bit of discipline while developemnt and deployment process. Even then if you have doubt that Primary keys will not be consistent, you can have environment Specific constant so that values of these constants can be modified using properties file while deployment.
If we are using reference table to store such values, then would it be fine using their primary keys as constants like the screenshots attached because these primary keys may differ across environments especially when reference data is huge and keeping track of changes is difficult.
That is exactly the problem I have with reference tables. You still need some sort of constants to be able to assign or compare values.
I do not have a good solution for you, except keeping that reference table the same for all environments.
I can understand, these days I write code in a way so that if I have to perform some logic like comparisons in showwhen and store corresponding primary key values in DB like above usecase. I either use index with wherecontains if I already have data, if not I query to fetch on the basis of text or vice versa. But, doing this is mostly tedious and increases unnecessary coding.
I just use environment-specific constants for this. It causes us no major problems. I still remember trying to deal with this back before environment-specific constants were even an option - that could be a real nightmare. In comparison, now it's a breeze.
In this case, I will utilize my own manually-created key value to reference the values. I will still have a primary key on the table, but I don't care what it is unless I am grabbing it to update a value. I maintain a 1-1 relationship of myKey to myValue manually. E.g.:
primaryKey (identity, auto-increment)myKey (int)myValue (string)
All code references values by myKey, deployments to new environments will insert/setup the data source as:
(primaryKey,myKey,myValue)(null,1,Value1)(null,2,Value2)(null,3,Value3)
All code to reference the values utilizes the myKey column as the 'key'. If I need to update this list programmatically, I will return the primaryKey at runtime based on the myKey value.