I am using a Formula field as my Primary Field for a schedule table (each record is a time slot).
Among my formula for naming each time slot, I would like to add a number (ie. 001, 002) which is generated based on the Date field.
ie. Record X: March 5, 2022 = 001, Record Y: May 7, 2022 = 002, Record Z: February 9, 2024 = 003
So, I need a formula that creates a number based on the record's Date field, in relation to the rest of the records.
On top of that, I would like to have separate sequences of numbers based on another field. I don't want all records named in sequential order, I want all Screening (option in single select field named "Type") records named 001>, and all Volunteer Shift records named 001>, etc.
ie. Screening A is named 'Screening 001' (although it may be the 10th record in the sorted-by-date table) and the volunteer shift for that screening might be named 'Volunteer Shift 005' (because there are 4 volunteer shifts scheduled before that one).
(The 001 number format isn't necessary, I can work with 1, 2, etc.)