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.
Qrew LegendThere 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.
Thanks Mark, The override field on the Task sequence number solution would be the route I need to take. Would you be able to provide the steps I would need to take? Thank you again.
Qrew LegendOK, the setup is a bit circular so let try the steps.
Try setting that all up and then we can touch base again. :)
On your Initiatives Table, you will have to set [Initiative Name] to Unique
Here is a picture of Mark's solution with one more feature, you should Snapshot the Name as well. Even though you want Unique Names, that does not guarantee you that they do not change. Someone can easily put in SAP Deploymnet and then correct it to SAP Deployment.
