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

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

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?
Photo of Andrew

Andrew

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Andrew,

I built a quick test app to help you here:

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
Photo of Andrew

Andrew

  • 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.
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!
Photo of Andrew

Andrew

  • 0 Points
That helps and is much less cumbersome than using Year-2010 to get the last digit.
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