Greetings all:
I’m looking for some guidance on how to create a formula whereby:
For example: 2020-001, 2020-002, …2020-1234 then on January 1 of 2021 the formula would change to 2021-001, 2021-002, …2021-1234, etc.
The context of this is for security incident reports and the “incident number” that is auto assigned for tracking purposes.
Thanks in advance,
~Adam
CHAT MESSAGES FROM TADABASE SUPPORT - EXCELLENT SOLUTION!!!
Alright, so here I have two tables in my structure. The one on the left IDs holds the generated ID and other fields needed while the one on the right Seq Counter exists to hold the count of the number of records in the IDs table for the given year.
Starting from the IDs table:
Num - Holds the numeric sequence value for each record to be used in the ID. Upon record creation, this value is automatically generated using conditional table rules.
Equation - Generates the desired ID YYYY-####
Seq Counter - Connects to the Counter Record in the Seq Counter table. This field is automatically set within our conditional table rules and establishes the connection so that the Num value can then be pulled.
Create Date/Time - Automatically set via conditional table rules, the year is pulled from this field to be used in the ID.
Next, the Seq Counter table:
Seq Counter Name - This is here for display purposes only for the display field in the Seq Counter field on the IDs table.
Complex Formula - This field is a Complex Formula counting the records in the IDs table. This will only count records in the current year. I’ll show specific configurations in following messages.
Last Reset Date/Time - This is a date marker I created to work around a known issue with our complex formulas not updating (we are working on fixing this). If needed, you’ll only need to run an update once a year. We can use a task to automate this so you won’t have to perform any manual updates. We’ll cover this further in further messages as well.
Next, I’ll share information about the conditional table rules set up for the IDs table and how to get the data to pull from the Seq Counter table accordingly.
Here are the conditional table rules (these can be found in the rules tab of your tables). The first one establishes the connection from the ID to the Counter Record (only one record will be on the Seq Counter table so this will remain the same).
The second rule sets the Num value in the IDs table to the Complex Formula value in the Seq Counter table.
I forgot to mention, the first rule also sets the Create Date/Time to the current date/time.
Returning to the Seq Counter table, here are the details on the configuration of the Complex Formula. This field is the field responsible for generating the sequence number that is pulled to the Num field and used in the Equation field to generate your IDs.
Using conditions, we can create a filter based on the year. This means that each year, the sequence will refresh.
Here’s a short preview showcasing how the rules behave when records are added. As you can see the values are automatically populated through rules and the equation generates the IDs automatically as well.
Ah, almost forgot to share the details for the equation that I built out. Please be sure to verify your field names if you utilize this example.
CONCAT(YEAR({Create Date/Time}), ‘-’, IF({Num}>1000, ’ ', IF({Num}>100, ‘0’, IF({Num}>10, ‘00’, ‘000’))), IF({Num}>0,ROUND({Num}), ‘0’))
There is a slight update needed on the equation function changing all the ‘>’ to “>=”. Please update that on your end as well to ensure the proper number of 0s are added to your sequences values
CONCAT(YEAR({Create Date/Time}), ‘-’, IF({Num}>=1000, ’ ', IF({Num}>=100, ‘0’, IF({Num}>=10, ‘00’, ‘000’))), IF({Num}>0,ROUND({Num}), ‘0’))
One last thing… The remaining factor that may need consideration at the very beginning of the year is regarding the complex formula and its current behavior. As mentioned in previous messages, we are working on an update to the functionality so this may not be needed, but I included the Last Reset Date/Time field on the Seq Counter table to create an easy workaround if it is needed. Essentially, there is a possibility that at the beginning of the year, the complex formula does not automatically update to return to 0. Under the case that this occurs, the easiest way to get the value to update is to set up a task (you can have it run automatically or run it manually).
Here’s an example of one I have set to run quarterly starting from the beginning of the year. As long as it updates a field, this will trigger the record to also update the complex formula.
