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

[Sacramento_Tena]

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!

[Justin_Barrett]

Welcome to the community, @Sacramento_Tenants_U! :grinning_face_with_big_eyes: 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)

[kuovonne]

Justin_Barrett:

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)

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.

[Justin_Barrett]

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.

[ScottWorld]

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.

[Justin_Barrett]

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.

[Justin_Barrett]

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.

[ScottWorld]

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! :grinning_face_with_big_eyes:

Best,
Scott

[Justin_Barrett]

ScottWorld:

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.

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.

[kuovonne]

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.