Forum Discussion

FLSI-DrewFLSI-D's avatar
FLSI-DrewFLSI-D
Qrew Trainee
7 years ago

Another Custom Auto-Generated Number Request

Hello, really liking this platform a lot and we're currently stuck between this an Zoho One, on Zoho it's extremely easy to accomplish what I'm trying to accomplish, but on here it's a bit more extensive I feel. Anyways, we use a format for our quotes(F18109) and jobs (18172-20DR) based on what department creates the quote and/or job. I've read a few other threads and tried to use their formulas but haven't had much luck getting what I', looking for. 

So for Fabrication -
1. Quotes are: F-YEAR-AUTO GENERATED 3 DIGIT NUMBER or F18109
2. Jobs are: YEAR-AUTO GENERATED 3 DIGIT NUMBER-DEPARTMENT ID-ESTIMATOR or 18172-20DR

And for Construction - 
1. Quotes are: B-YEAR-AUTO GENERATED 3 DIGIT NUMBER or B18111
2. Jobs are: YEAR-AUTO GENERATED 3 DIGIT NUMBER-DEPARTMENT ID-ESTIMATOR or 18172-10PR

We're thinking, if this system can auto-gen our numbers, we may just do away with the two separate quote number variations, but keep the dept. ID's for the jobs the same, however that's at another time. 

Any help would be greatly appreciated, and I do really enjoy this software!
Ask about Quickbase functionality
  • QuickBaseJunkie's avatar
    QuickBaseJunkie
    Qrew Commander
    7 years ago
    Below is an example formula you can use for the Quotes. For the Jobs, what is the Department ID-estimator? Is this a static number/text or is it based on the person who did the estimate? Also, is there a reason for the three digit format? Is it possible to have more than 999 quotes for a single department?
     
    Quotes (Formula text field)

    var text ThreeDigit = Case(Length(ToText([Record ID#])),1,"00",2,"0");
    var text Department = Case([Department],"Fabrication","F","Construction","B","-");

    $Department & Right(ToText(Year(ToDate([Date Created]))),2) & $ThreeDigit & ToText([Record ID#])




    • FLSI-DrewFLSI-D's avatar
      FLSI-DrewFLSI-D
      Qrew Trainee
      7 years ago
      Thank you for the help! I did receive this back when I clicked save: Formula error -- Bad or missing arguments in function call

      The types of the arguments or the number of arguments supplied do not meet the requirements of the function Case.

      The function is defined to be Case ( x, val1, result1, ..., else-result).

      You may need to do one of the following to fix the problem:
      • Choose the right number and type of arguments.
      • Use a type conversion function to convert the arguments you are using to the correct type.
      • Choose a different function."

      For Jobs, Department ID is 20 for fab and 10 for construction. Estimator would be based off of who is selected from a drop-down or something along those lines. The three digit format is fine, because we honestly don't bid that many projects. Our projects alone take weeks and or months, so our work is chosen somewhat carefully. 

      I do greatly appreciate your help though! :)
    • QuickBaseJunkie's avatar
      QuickBaseJunkie
      Qrew Commander
      7 years ago
      Interesting, did you copy all three lines of the formula as they show above? I do not get any errors. You would need to have a multiple choice field called Department with the vales of Fabrication and Construction. If you don't have a field like that you can use the following, but the "F" would be hard coded and not dynamic.

      var text ThreeDigit = Case(Length(ToText([Record ID#])),1,"00",2,"0");

      "F" & Right(ToText(Year(ToDate([Date Created]))),2) & $ThreeDigit & ToText([Record ID#])

      You can take a similar approach for Jobs. Here you would need to have multiple choice fields for Department and Estimator ID. This is just an example to show that what you're trying to accomplish is possible. If Estimator is a User field and you want initials, that's possible, but more complex.

      var text ThreeDigit = Case(Length(ToText([Record ID#])),1,"00",2,"0");
      var text Department = Case([Department],"Fabrication","20","Construction","10","--");

      Right(ToText(Year(ToDate([Date Created]))),2) & $ThreeDigit & ToText([Record ID#]) & "-" & $Department & [Estimator ID]


      If you're still getting an error with the first formula, please paste what you've entered here, so I can see what might be causing the issue.