Formula Syntax Error

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,

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),
)

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
)

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.

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. ;)

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.

my skillset has some "developmental opportunities."

Brilliant

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 

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