Skip to main content
Question

IF Formula Help

  • August 21, 2018
  • 0 replies
  • 22 views
Hello,

I'm super new to quickbase and it's array of functions and commands and I'm getting a lot of info from here and the tech support! So, I'm trying to make an auto generating Job Number based on 3 main things to make it look like this: 18034-20 ; so the "18" is the year the job was created, the "034" is the number, we don't ever get close to 1000 jobs a year, we're a small specialty construction business, and the "-20" is the ID for which department(which is a drop-down selector for either Fabrication or Construction). 

So using the Formula below, when I select Fabrication it returns 18034-10 and when I select Construction it returns -2018034.

Formula:
If([Department ID]="Fabrication",
If([Record ID#]>=1 and [Record ID#]<10, Right(ToText(Year([Quote - Date of Bid])),2)&"00"&[Record ID#]& [Department ID],
[Record ID#]>=10 and [Record ID#]<100, Right(ToText(Year([Quote - Date of Bid])),2)&"0"&[Record ID#]& [Department ID],
Right(ToText(Year([Quote - Date of Bid])),2)&[Record ID#]), "-20")&

If([Department ID]="Construction",
If([Record ID#]>=1 and [Record ID#]<10, Right(ToText(Year([Quote - Date of Bid])),2)&"00"&[Record ID#]& [Department ID],
[Record ID#]>=10 and [Record ID#]<100, Right(ToText(Year([Quote - Date of Bid])),2)&"0"&[Record ID#]& [Department ID],
Right(ToText(Year([Quote - Date of Bid])),2)&[Record ID#]), "-10")


Any help would be greatly appreciated!

edit: oh and as for the reasoning why I have all the additional formatting for the Job Number for the "034" is so it's always 3 digits. 
    This topic has been closed for replies.

    Can you live with the number not starting at 001 each year?  That will be a whole lot simpler if you can accept that.

    If you can live with that, then the formula will be

    (not tested, so please post back if there are any syntax errors)


    var text Dept = Case([Department ID],
    "Construction", "10",
    "Fabrication", "20", "error");

    var text YY = Right(ToText(Year([Quote - Date of Bid])),2);

    var text Number = Right("00" & ToText([Record ID#]),3);

    $YY & $Number & "-" & $Dept


    The formula above uses formula variables to break up the syntax into smaller bite size units.





    Terms & ConditionsAccessibility statement