How to use variable default values with a choice column (site column)

Question

I have a column that I need to place on lists in multiple sub-sites for users to enter the account number for the activity of a project. This will be a "choice" column with three options:

1. The default account number for the project
2. An option "No project funds required for this activity"
3. A "Specify your own value field"

The only difference across the sub-sites is the default account number since it's specific to each project. I'd like to use a site column for this but I need to populate the account number option automatically/programmatically so that it recuperates the account number by a lookup on the project code or similar. If I enter the account number manually after inserting the site column on the list I find that any updates done to the site column for which I choose to also update the list columns will also change the list of available values.

Any ideas or advice on how I can do this?

Many thanks Chris

Answer 1

Choice columns values are stored as normal text, so what about the following workaround? Instead of including the default account number within the list of possible choices, use a constant label "default account number". Then, attach a workflow to the list for both the item added and item edited events; if the choice column value is equal to "default account number", then retrieve the actual account number from the lookup list, and replace the choice column value with the account number value.

This way you can freely edit the site column and cascade the changes, without incurring in the issue you described.