Make a primary field of the formula [YEAR]00autonumber

Ask the community
#1

Hi there,

I am trying to set up a system to make a primary field that references unique case numbers. We do the cases by year, so for example, the first case of this year will be 2022001, the next one will be 2022002, etc. But, we want to use the same table from year to year, so ideally I will have a forumla that pulls the year the record was created, and then adds a 4-digit autonumber. Then in 2023 we will automatically start with 2023001, etc. Any help/advice is greatly appreciated!

#2

Welcome to the community, @Sacramento_Tenants_U! :smiley: I was adding something like that to a personal issue-tracking base earlier today:

  • Add a number field (integer) to contain the count for the year. Iā€™ll call this {ID}
  • Add a {Created Year} formula field that outputs the year of the recordā€™s creation as a string:
    YEAR(CREATED_TIME()) & ""
  • Create an automation that triggers when a new record is created.
  • Add a ā€œFind recordsā€ action that uses a condition with dynamic input to search for records with a year matching the triggering recordā€™s {Created Year} field.
  • Add an ā€œUpdate recordā€ action to update the triggering recordā€™s {ID} field with the count of the found records from the ā€œFind recordsā€ action (the ā€œlengthā€ property)

The first time a record is created this year, that will become record 1, the next will be record 2, and so on. When the year rolls over, so will the count.

The primary field then becomes this formula:

{Created Year} & RIGHT("000" & ID, 4)
1 Like
#3

Keep in mind that this will only work if you have less than 100 records per year. The ā€œFind recordsā€ action will return a maximum of 100 records. You will also have problems if a record is deleted.

#4

Good points. My preferred method is using a script, but I was playing with this alternate setup earlier and it was fresh on my mind. I havenā€™t used the ā€œFind recordsā€ action much, so I wasnā€™t aware of the 100-record limit.

1 Like
#5

I wonder if thereā€™s a way to automate this without scripting by creating a separate table where each record keeps track of a particular year along with its latest ā€œserial numberā€ used.

1 Like
#6

Good thinking, Scott! I just tested it and it works. Hereā€™s how I set it up. In my case, the main table is ā€œIssuesā€:

  • Created a [Years] table and deleted all records. In that tableā€¦
    • Created an {Issues} link field to link to the [Issues] table.
    • Created a {Next Issue} rollup field, pulling the values from the {ID} field in the [Issues] table and using this aggregation formula: MAX(values) + 1
  • In the Issues table, I created a {Next Issue} rollup field using the SUM(values) aggregation formula to bring over the next issue number for the linked year (using the reciprocal link field to the [Years] table).
  • Created an automation triggered by a new record.
    • Added an ā€œUpdate recordā€ action to insert the year from the {Created Year} formula field into the triggering recordā€™s link field pointing to the [Years] table. If a linked record with that name isnā€™t found, itā€™s created and linked; otherwise itā€™s just linked.
    • Added another ā€œUpdate recordā€ action to take the value from the {Next Issue} rollup field and put it into the {ID} field.

When the first record is created in [Issues] the {ID} field is empty, so the {Next Issue} value becomes 1 and is copied over for the ID. The second record gets an ID of 2, and so on. If a record is deleted, the highest ID doesnā€™t change, so the count continues from that ID.

#7

With a little variation, that setup could be adjusted to reset the ID count at other intervals: each month, each week, etc. Itā€™s just a matter of changing the formula that formats the created time. That format is what drives the linked records, and those linked records drive the sequence.

Would anyone mind if I shared this setup in either my newsletter or YouTube channel? Everyone here contributed to the result, and I want to credit you for your contributions.

#8

Hi @Justin_Barrett,

Thank you so much for spending the time of doing all the hard work of what I was envisioning! :stuck_out_tongue:

I was actually thinking that the newest ID wouldnā€™t even be based on summing the maximum linked values, because it could result in duplicate IDā€™s if an issue gets deleted. I was thinking that it could just be a normal number field that gets incremented by 1 with each run of the automation.

Yes, you can definitely publish this in your newsletter or your YouTube channel! I loved your very first issue of your newsletter yesterday! It was incredibly well-written and fun to read, too! :smiley:

Best,
Scott

#9

The only chance of a duplicate is if the most recent issue is deleted. In that case, the original issue no longer exists, so thereā€™s no chance of confusing the new issue assigned to that ID with the old one. I donā€™t necessarily see that type of ID reuse as a problem, but your solution would take care of it if someone wants to avoid that possibility.

1 Like
#10

Why link the table at all? That just slows down the base and adds extra fields.

Have the [Years] table have with the following fields

  • An editable field for the {Year}
  • An editable field for the {next issue number}
  • A formula field that adds 1 to the {next issue number}

Pre-create enough years for the next decade or so so you donā€™t have worry about it, and set the {next issue number} to 1 for future years (and whatever is the desired start number for the current year).

In the automation for the new issue, have a find records action that looks for the record in the years table with the matching year. The action will find exactly one record.

Then use an update action to copy the {next issue number} from the found record. You will have to make a list of the numbers, but since there is only one number this should not be a problem.

Finally, in yet another action, update the year record to move the {next issue number plus one} value into the {next issue number} field. Again, since only one record was found, you can use the record id and field values from the find records action without a problem, because there is only one value in the list.

For a finishing touch, set the permissions on the table so that no-one can delete records in the [Years] table and only automations (no-one else) can edit field values in the [Years] table and the {issue number} in the [Issues] table. Oh, and add descriptions all over the place explaining the system and warning that in a decade it will need maintenance.

2 Likes
#11

Yes, that was precisely the method that I was thinking of in my head. Thanks, @kuovonne, for taking the time to do the hard work of typing it all up for me!! :cowboy_hat_face:

1 Like
#12

But overall, I still prefer a script because

  • you donā€™t have the extra table and fields filling up your base that you never really need to ever see

  • all the logic is stored in one place (the script) versus scattered across various tables and fields in addition to the automation itself. When you inherit a base and need to perform maintenance, donā€™t underestimate the benefit of having all the logic in one place. And with a well written/documented script, you can figure out the scriptā€™s purpose, tables, and fields without knowing how to code.

  • I happen to like scripts in generalšŸ˜Ž

One main advantages I see to a non-scripting option is that you can change your field names without breaking the system.

2 Likes
#13

That and the system will work in bases that are in sub-Pro-plan workspaces.

2 Likes