# How can I create a job number generator that includes the last digit of the project year?

• 0
• Question
• Updated 3 years ago

I am using this formula:

If(
[Office]="Spokane","S",
[Office]="Bellevue","B",
[Office]="Portland","P",
[Office]="Eugene","E") &

ToText(ToNumber(Year([Bid Date]))-2010)

& [Snapshot]

The snapshot is summing how many jobs each office has. We need the sum to restart everytime the year of the Bid Date Changes. Is there a way to do this?
• 0 Points

Posted 3 years ago

• 0
• 110 Points
Andrew,

https://cirrusops.quickbase.com/db/bkgtm9nm7

Here is the formula I used:

Case([Office],
"Spokane","S-"&Right(ToText([Year]),2)&"-"&[# Bids Snapshot],
"Bellevue","B-"&Right(ToText([Year]),2)&"-"&[# Bids Snapshot],
"Portland","P-"&Right(ToText([Year]),2)&"-"&[# Bids Snapshot],
"Eugene","E-"&Right(ToText([Year]),2)&"-"&[# Bids Snapshot],
null)

I placed dashes "-" between each element to show how it is working.

My best stab at this is that you would need a years table with a summary count of how many bids in that year. You may already have this.

Take a look. Hope it helps!

Eric
CirrusOps
• 0 Points
Eric,

Thank you for the help! I will build the years table and see if I can get it to work that way.
• 110 Points
Sure. Also, I didn't realize until after the fact that you were only looking for the last digit of the year, and not the more common last TWO digits of the year. Here is an updated formula, also without the dashes:

Case([Office],
"Spokane","S"&Right(ToText([Year]),1)&[# Bids Snapshot],
"Bellevue","B"&Right(ToText([Year]),1)&[# Bids Snapshot],
"Portland","P"&Right(ToText([Year]),1)&[# Bids Snapshot],
"Eugene","E"&Right(ToText([Year]),1)&[# Bids Snapshot],
null)

Good Luck!
• 0 Points
That helps and is much less cumbersome than using Year-2010 to get the last digit.
• 110 Points
One more quick note - On the years table, I made the numeric field 'year' the key field. After I built the relationship, on the bids table I changed the field type of 'related year' to a formula numeric, and renamed it 'year'. Look at that formula for the rest. You should be good to go.

Cheers!

Eric