Formula Syntax Error

  • 1
  • 1
  • Question
  • Updated 4 months ago
  • Answered
Hello:

I am having difficulty with a formula syntax.  I am pretty green, and sadly my skillset has some "developmental opportunities."

I am trying to do some calculations for equipment rental based on days utilized (creating a minimum, daily, weekly, and monthly rates).

Attached is my formula.  I know I have made a bonehead mistake.

Help please?
Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb

Posted 4 months ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,824 Points 50k badge 2x thumb
Happy to help, but please post a copy and paste of the actual code and not just a screen shot.  I can't edit a screen shot.  Also please do the same for the error message.
Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb
Hi:

Here is what I have.

Thank you,

Dirk


Formula syntax error

There are extra characters beyond the end of the formula


If(
[Days]=0.5, ([Equipment Rate - Base Cost]/96),
([Days]>0.5 and [Days]<=4), ([Days]*[Equipment Rate - Base Cost]/24),
([Days]>=4 and [Days]<=7), ([Equipment Rate - Base Cost]/24)*3)
([Days]>=8 and [Days]<=32), ([Equipment Rate - Base Cost]/24)*8),
)


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,824 Points 50k badge 2x thumb
You do not need the very last comma.that will fix the syntax error.

But you can also simplify your formula like this

If(
[Days]=0.5, [Equipment Rate - Base Cost]/96,
[Days]>0.5 and [Days]=4 and [Days]=8 and [Days]<=32,[Equipment Rate - Base Cost]/24*8
)

It is not a syntax error to have those extra brackets but you can decide if it makes the formula more or less readable.
Photo of Dan

Dan

  • 1,558 Points 1k badge 2x thumb
Shouldn't it be more like this?
If(
[Days]=0.5, [Equipment Rate - Base Cost]/96,
[Days]>0.5 and [Days]<=4, ([Equipment Rate - Base Cost]/24)*[Days],
[Days]>=4  and [Days]<=7, ([Equipment Rate - Base Cost]/24)*3,
[Days]>=8  and [Days]<=32, ([Equipment Rate - Base Cost]/24)*8
)
Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb
Dan:

This worked perfect.  Thank you!

Dirk
Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb
Dan:

Kind of a similar situation.... but I am stymied again:

Formula:
If([Associate - Labor Category]= "Carpenter",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Carpenter",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Welder",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Welder",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Superintendent",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),


Error Message:
Formula syntax error
A closing parenthesis is missing.

If([Associate - Labor Category]= "Carpenter",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours]  ,
[Associate - Labor Category]= "Carpenter",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Carpenter",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Carpenter Foreman",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Welder",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Welder",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Welder Foreman",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]),
If([Associate - Labor Category]= "Superintendent",[Staff Status]= "Dedicated",[Hour Category]= "Standard",([Labor Rate - Dedicated Standard Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[Staff Status]= "Dedicated",[Hour Category]= "Premium",([Labor Rate - Dedicated Premium Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[[Staff Status]= "Variable",[Hour Category]= "Standard",([Labor Rate - Variable Standard Rate]*[Hours],
[Associate - Labor Category]= "Superintendent",[Staff Status]= "Variable",[Hour Category] = "Premium",([Labor Rate - Variable Premium Rate]*[Hours]


If certain fields have certain content, a specific field is used (in the same table) to calculate labor fees.

Any insight is greatly appreciated!!!
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,824 Points 50k badge 2x thumb
I was just trying to help with the syntax error. I did not try to think through the intent of the formula. So you may be right in your 2nd line where you multiply by [Days], but then the last two lines would have to be similarly adjusted.
Photo of Dan

Dan

  • 1,558 Points 1k badge 2x thumb
Gotcha. I'm not clear on the intent, either...but the "[Days]>0.5 and [Days]=4 and [Days]=8 and [Days]<=32" bit in your example stuck out to me. ;)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,824 Points 50k badge 2x thumb
Ok sorry, now I see that when I posted from my ipad some of my formula got lost.


So Dirk, just get rid of your last comma and your formula will work from a syntax point of view.


But whether you get the calculated results you were hoping for is another matter.


If that is the case and you can’t figure it out then you’ll have to say in words what your formula is supposed to do and we can help you construct the logic to get there.
(Edited)
my skillset has some "developmental opportunities."

Brilliant
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,824 Points 50k badge 2x thumb
When you have a formula this long there is a good indication that your app should be redesigned to have a table of rates and lookup the rate from the table.

My suggestion is to make a table with a hyphenated Key field like
Labor Category-Status-Hours Category.  It will get populated by a form rule which refers to a formula which calculates that Hyphenated value from the drop down multiple choice fields.

But I can try to help you debug this.  

You would be better served with more spacing of your formula.  I first use a formula variable to calcuate the Rate.


var number Rate = 


If(
[Associate - Labor Category]= "Carpenter"
and [Staff Status]= "Dedicated"
and [Hour Category]= "Standard",[Labor Rate - Dedicated Standard Rate],


[Associate - Labor Category]= "Carpenter"
and [Staff Status]= "Dedicated"
and [Hour Category]= "Premium",[Labor Rate - Dedicated Premium Rate],

[Associate - Labor Category]= "Carpenter" and
[Staff Status]= "Variable"
and [Hour Category]= "Standard",[Labor Rate - Variable Standard Rate],

etc


[Associate - Labor Category]= "Superintendent"
and [Staff Status]= "Variable"
and [Hour Category] = "Premium",[Labor Rate - Variable Premium Rate]);

// now we refer to the rate and multiply by the hours

$Rate * [Hours]


~~~~~~~~~~~~~~~~~

btw, the other advantage of using a table is that once you lookup the rate you can use a snapshot field to freeze it so that next year when the rates may change, you will not affect the historical records.

https://help.quickbase.com/user-assistance/setting_up_snapshot_fields.html 


Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb
I can appreciate that.... I am just in a crunch to get things operational.  I'm just a regular working guy (construction guy), so I am swimming a bit above my depth.

I do have a contract based rate system.  So labor rates are entered under a contract number.  So this should not impact rates on previous rate plans.  

Its nice to know about snap shot fields, I will try to incorporate into my working knowledge.

Thank you so much

Dirk
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,764 Points 50k badge 2x thumb
OK, but this rate here

[Labor Rate - Dedicated Standard Rate]

When that rate changes next year, wont it mess up your historical records?  ie restate history?
Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb
I don't think so.  This comes from another table that is part of this specific 2019 contract.  The 2020 contract would have a different value.
Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb
I must be doing something wrong:

Formula syntax error

Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets.

var number Rate =
If(
[Associate - Labor Category]= "Carpenter" and [Staff Status]= "Dedicated" and [Hour Category]= "Standard",[Labor Rate - Dedicated Standard Rate],
[Associate - Labor Category]= "Carpenter" and [Staff Status]= "Dedicated" and [Hour Category]= "Premium",[Labor Rate - Dedicated Premium Rate],
[Associate - Labor Category]= "Carpenter" and [Staff Status]= "Variable" and [Hour Category]= "Standard",[Labor Rate - Variable Standard Rate],
[Associate - Labor Category]= "Carpenter" and [Staff Status]= "Variable" and [Hour Category]= "Premium",[Labor Rate - Variable Standard Rate],
// now we refer to the rate and multiply by the hours
$Rate * [Hours] 

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,764 Points 50k badge 2x thumb
np, you just need to close the final bracket to match the opening bracket on the IF

[Hour Category]= "Premium",[Labor Rate - Variable Standard Rate])

// now we refer to the rate and multiply by the hours
$Rate * [Hours] 




Photo of Dirk Ruana

Dirk Ruana

  • 788 Points 500 badge 2x thumb
Got it!  Yes... problem solved.  

Thank you so much!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,764 Points 50k badge 2x thumb
Great, thx for letting me know.