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?

Got it to work. Thanks alot!------------------------------Wallace (DHS) Yeboah------------------------------

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.

Hi:Here is what I have.Thank you,DirkFormula syntax errorThere are extra characters beyond the end of the formulaIf([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),)

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.

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)

Formula Syntax Error | Qrew Discussions

QuickBaseCoachD
Qrew Captain
7 years ago
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.
DirkRuana
Qrew Captain
7 years ago
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),
)
QuickBaseCoachD
Qrew Captain
7 years ago
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.
DanLadner1
Qrew Trainee
7 years ago
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
)
- DirkRuana
  Qrew Captain
  7 years ago
  Dan:
  
  This worked perfect. Thank you!
  
  Dirk
- DirkRuana
  Qrew Captain
  7 years ago
  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!!!
QuickBaseCoachD
Qrew Captain
7 years ago
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.
DanLadner1
Qrew Trainee
7 years ago
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. ;)
QuickBaseCoachD
Qrew Captain
7 years ago
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.
AlexCertificati
Qrew Cadet
7 years ago
my skillset has some "developmental opportunities."

Brilliant
QuickBaseCoachD
Qrew Captain
7 years ago
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
DirkRuana
Qrew Captain
7 years ago
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
- QuickBaseCoachD
  Qrew Captain
  7 years ago
  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?
- DirkRuana
  Qrew Captain
  7 years ago
  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.
- DirkRuana
  Qrew Captain
  7 years ago
  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]

Forum Discussion

Formula Syntax Error

Related Content

Delete Formula Button Error

Syntax Error

Jinja If else statement syntax

404 Error on new nav

Formula Query with custom key