Rolling Customer Number Based On Year

[Shaun_Hughston]

I need to create a customer number that includes the year, and an accruing customer number.

E.g. 2020-0001, 2020-0002, etc.

The thing is, every year, the year has to change in the customer number, and the four numbers after it have to reset.

So if the last number of 2020 is 2020-1234, the first number of 2021 will be 2021-0001 again.

I can get the year from the Created Time field

The problem is the numbers after the year… I figure I can’t use an autonumber because it won’t reset each year.

I’m stuck on this part, and would appreciate any direction.

Thanks!

[ScottWorld]

Yeah, the problem here (as you mentioned) is that the auto numbering can’t be set or reset.

Here’s a clunky (but simple) workaround for this:

If you know what the highest Autonumber is for this year (at the end of the year), then next year, you can just subtract that number from next year’s formula to “fake reset” the number for next year.

So it’s sort of a “fake reset” every year. You would be required to manually update your formula once per year. Your formula would subtract a different number each year (based on the year), and those numbers would be hard-coded by you into your formula.

So, in plain English. your formula‘a logic would be something like this:

• if this record’s year is 2020, just use the normal autonumber.
• If this record’s year is 2021, then subtract X from the autonumber.
• If this record’s year is 2022, then subtract Y from the autonumber.
• And so on. You would manually update the formula once per year.

I’m sure that someone will be able to come up with a more automated trick involving linking tables & rollups & lookups, but this was just an easy solution that popped into my head.

[Shaun_Hughston]

I hadn’t thought of that approach…thanks! :grinning: