Non-Unique Sequential ID numbers field

Question

My assigned task is to create a field in a table that shows a unique number for each record created but isn't the Record # ID field (as this field number is unique to the table) and we need a number that is unique to each task but associated with a single initiative.  So the sequential number will be associated with a unique initiative name and if the user selects a different initiative name then the sequential number will start at 1.

MarkShnierYou · Answer

There are different ways of doing this with varying complexity. If you're starting with a brand new database where you are able to start with no Tasks, then what you can do is make a summary field on the relationship where one initiative has many tasks to count up the number of Tasks. Then you look up that summary field down into the Task and then create a Snapshot Field. The snapshot field will freeze in the value at the time the Task is created. If you use this method you have to be sure that nobody, not even the admin, can delete records, otherwise you won't get duplicate task numbers.

Another level of complexity might be to have an override field on the Task sequence number so you're able to assign tasks numbers manually if you have existing Tasks or somehow the sequence gets messed up.

It would create a formula field which would use the override if present else it would use the snapshot field. The snapshot field would be made by first doing a summary maximum of that formula field and then looking up the maximum sequence number down to the children Tasks and then making snapshot of that look up field.

Sign up

Login to the community