Discussions

Expand all | Collapse all

IF Formula Help

  • 1.  IF Formula Help

    Posted 08-20-2018 20:10
    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. 


  • 2.  RE: IF Formula Help

    Posted 08-20-2018 20:20
    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.







  • 3.  RE: IF Formula Help

    Posted 08-20-2018 20:29
    Returned this: 
    An identifier cannot be the same as a type name.


    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

    __Also when I pasted the formula into the box, it highlights "Number" and says "An Identifier must follow the type in a variable declaration."


  • 4.  RE: IF Formula Help

    Posted 08-20-2018 20:31
    OK, it did like me calling the formula variable "Number". 
    Try this


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

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

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

    $YY & $SeqNumber & "-" & $Dept



  • 5.  RE: IF Formula Help

    Posted 08-20-2018 20:35
    Worked!

    Now... How much difficulty will there be to have the formatting put back? lol, I will ask accounting and my bosses what they think of just having it go up from 1 digit, but cannot guarantee anything.


  • 6.  RE: IF Formula Help

    Posted 08-20-2018 20:44
    You know what, I just realized a flaw in my initial formula, it uses the Quotes Year as the designation, we need it to go off of the current year the job is created instead. Would you be an amazing person, again and update to use the CURRENT year rather when it was quoted? A large oversight on my part, sorry about that._


  • 7.  RE: IF Formula Help

    Posted 08-20-2018 20:45
    Scratch that, fixed it myself! :D Thank you so much for the help!


  • 8.  RE: IF Formula Help

    Posted 08-20-2018 21:03
    OK, that you have it fixed, but you do not want it to be based on the current year, if that means the year of Today() as that will change the numbering for historical quotes when the Year of Today changes to 2019.


  • 9.  RE: IF Formula Help

    Posted 08-20-2018 22:24
    I already got help with a QB engineer on the Quote number generation. However we have many cases where we quote something and get the job a year or two later. If I change [Quote - Bid Date] to [Today] it'll only change for the job number, right? I don't see how it'll change the quotes date as well.


  • 10.  RE: IF Formula Help

    Posted 08-21-2018 11:03
    I�m not sure now what formula you are using and if you are using the bid date or the quote date.


  • 11.  RE: IF Formula Help

    Posted 08-21-2018 19:04
    If you change the [Quote - Bid Date] to Today() are you wanting a quotes date to change to Today() as well?
    If so you could use the date change as a trigger for a Quick Base Action then use the Action to change the date.