# Formula Syntax Error

• 1
• Question
• Updated 3 months ago
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.

• 728 Points

Posted 3 months ago

• 1

QuickBaseCoach App Dev./Training, Champion

• 67,448 Points
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.
• 728 Points
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),
)

QuickBaseCoach App Dev./Training, Champion

• 67,448 Points
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.
• 1,452 Points
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)`
• 728 Points
Dan:

This worked perfect.  Thank you!

Dirk
• 728 Points
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)

QuickBaseCoach App Dev./Training, Champion

• 67,448 Points
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.
• 1,452 Points
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. ;)

QuickBaseCoach App Dev./Training, Champion

• 67,448 Points
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)
• 3,226 Points
my skillset has some "developmental opportunities."

Brilliant

QuickBaseCoach App Dev./Training, Champion

• 67,448 Points
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

• 728 Points
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
• 728 Points
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.
• 728 Points
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]

QuickBaseCoach App Dev./Training, Champion

• 67,228 Points
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]

• 728 Points
Got it!  Yes... problem solved.

Thank you so much!

QuickBaseCoach App Dev./Training, Champion

• 67,228 Points
Great, thx for letting me know.