Discussions

 View Only
Expand all | Collapse all

Formula Syntax Error

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training02-24-2019 01:39

  • 1.  Formula Syntax Error

    Posted 02-14-2019 15:46
    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?


  • 2.  RE: Formula Syntax Error

    Posted 02-14-2019 15:49
    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.


  • 3.  RE: Formula Syntax Error

    Posted 02-15-2019 01:46
    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),
    )




  • 4.  RE: Formula Syntax Error

    Posted 02-15-2019 12:12
    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.


  • 5.  RE: Formula Syntax Error

    Posted 02-15-2019 12:19
    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
    )


  • 6.  RE: Formula Syntax Error

    Posted 02-15-2019 15:28
    Dan:

    This worked perfect.  Thank you!

    Dirk


  • 7.  RE: Formula Syntax Error

    Posted 02-23-2019 23:26
    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!!!


  • 8.  RE: Formula Syntax Error

    Posted 02-15-2019 12:26
    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.


  • 9.  RE: Formula Syntax Error

    Posted 02-15-2019 12:27
    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. ;)


  • 10.  RE: Formula Syntax Error

    Posted 02-15-2019 12:32
    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.


  • 11.  RE: Formula Syntax Error

    Posted 02-15-2019 19:57
    my skillset has some "developmental opportunities."

    Brilliant


  • 12.  RE: Formula Syntax Error

    Posted 02-23-2019 23:39
    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 




  • 13.  RE: Formula Syntax Error

    Posted 02-23-2019 23:55
    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


  • 14.  RE: Formula Syntax Error

    Posted 02-23-2019 23:59
    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?


  • 15.  RE: Formula Syntax Error

    Posted 02-24-2019 00:08
    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.


  • 16.  RE: Formula Syntax Error

    Posted 02-24-2019 00:15
    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] 



  • 17.  RE: Formula Syntax Error

    Posted 02-24-2019 00:17
    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] 






  • 18.  RE: Formula Syntax Error

    Posted 02-24-2019 00:41
    Got it!  Yes... problem solved.  

    Thank you so much!


  • 19.  RE: Formula Syntax Error

    Posted 02-24-2019 01:39
    Great, thx for letting me know.


  • 20.  RE: Formula Syntax Error

    Posted 09-12-2019 11:47
    I keep receiving a "expecting boolean but found text" syntax error for the following bold part  of the formula. Can anyone assist?

    If(not IsNull([Date of Assignment]),"<div style=\"width:70px;padding:3px;background-color:lime;color:black\"> Open </div>",
    "<div style=\"width:70px;padding:3px;background-color:yellow;color:black\"> Pending Engagement </div>",
    [Type of Closure] = "Early Closure" and (not IsNull([Closure date])),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Early Closure </div>",
    [Type of Closure] = "Termination" and (not IsNull([Closure date])),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Termination </div>",
    [Type of Closure] = "Completion" and (not IsNull([Closure date])),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Completion </div>")

    ------------------------------
    Wallace (DHS) Yeboah
    ------------------------------



  • 21.  RE: Formula Syntax Error

    Posted 09-12-2019 11:59
    There is a problem with your logic here

     you are saying

    If(not IsNull([Date of Assignment]),

     then show this, else show that, and then you have another condition.

     you need to have a series or pairs of conditions and results, but you have

    condition, result, result,
    condition, result,
    condition, result
    condition, result

    there is a problem with the extra result

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 22.  RE: Formula Syntax Error

    Posted 09-12-2019 12:13
    Thanks for the quick response Mark. I was able to save the formula by creating a new condition for that result. However, the logic is still not providing the results I expected.

    I would like the Status field to show green "Open" status if the Date of Assignment is not blank and show a yellow "pending engagement" if date created is not blank. 

    In addition, I would like  a pink highlighted text if there is a value entered for type of closure and then show the type of closure (Early Closure, Termination, Completion)


    If(not IsNull([Date of Assignment]),"<div style=\"width:70px;padding:3px;background-color:lime;color:black\"> Open </div>",
    If(not IsNull([Date Created]), "<div style=\"width:70px;padding:3px;background-color:yellow;color:black\"> Pending Engagement </div>",
    [Type of Closure] = "Early Closure" and (not IsNull([Closure date])),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Early Closure </div>",
    [Type of Closure] = "Termination" and (not IsNull([Closure date])),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Termination </div>",
    [Type of Closure] = "Completion" and (not IsNull([Closure date])),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Completion </div>"))

    ------------------------------
    Wallace (DHS) Yeboah
    ------------------------------



  • 23.  RE: Formula Syntax Error

    Posted 09-12-2019 12:21
    The IF statement will process the conditions and stop at the first one that is true.  So, you need to think about the sequence of the conditions and if the condition needs to be enhanced.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    markshnier2@gmail.com
    ------------------------------



  • 24.  RE: Formula Syntax Error

    Posted 09-12-2019 14:33
    Got it to work. Thanks alot!

    ------------------------------
    Wallace (DHS) Yeboah
    ------------------------------



  • 25.  RE: Formula Syntax Error

    Posted 09-16-2019 13:53
    So it seems I am still having additional problems with this formula. 

    Every part of the formula works except for the (not IsNull([Related Staff Assignment3 - Date Assigned])),"<div style=\"width:70px;padding:3px;background-color:lime;color:black\"> Open </div>") where ([Related Staff Assignment3 - Date Assigned]) is a reference field from another table. 

    The formula seemed to work when I was pulling date assigned directly from the field within the table, however when I changed it to the reference field from another table the formula stopped working. Is there something I'm missing?

    ------------------------------
    Wallace (DHS) Yeboah
    ------------------------------



  • 26.  RE: Formula Syntax Error

    Posted 09-16-2019 13:54
    Here is the formula by the way:

    If([Type of Closure] = "Early Closure" and (not IsNull([Closure date])),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Early Closure </div>",
    [Type of Closure] = "Termination" and (not IsNull([Closure date])),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Termination </div>",
    [Type of Closure] = "Completion" and (not IsNull([Closure date])),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Completion </div>", (not IsNull([Related Staff Assignment3 - Date Assigned])),"<div style=\"width:70px;padding:3px;background-color:lime;color:black\"> Open </div>"
    ,(not IsNull([Date Created])), "<div style=\"width:70px;padding:3px;background-color:yellow;color:black\"> Pending Engagement </div>")

    ------------------------------
    Wallace (DHS) Yeboah
    ------------------------------



  • 27.  RE: Formula Syntax Error

    Posted 09-16-2019 14:10
    Your IF statement is not well formulated.

    I recommend changing from this IF statement to a Case() statement.

    Case(true,
    [Type of Closure] = "Early Closure" and not IsNull([Closure date]),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Early Closure </div>",
    [Type of Closure] = "Termination" and not IsNull([Closure date]),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Termination </div>",
    [Type of Closure] = "Completion" and not IsNull([Closure date]),"<div style=\"width:70px;padding:3px;background-color:pink;color:black\"> Completion </div>", 
    not IsNull([Related Staff Assignment3 - Date Assigned]),"<div style=\"width:70px;padding:3px;background-color:lime;color:black\"> Open </div>",
    "<div style=\"width:70px;padding:3px;background-color:yellow;color:black\"> Pending Engagement </div>"
    )​


    ------------------------------
    Laura Thacker (IDS)
    laura@intelligentdbs.com
    (626) 771 0454
    ------------------------------



  • 28.  RE: Formula Syntax Error

    Posted 09-16-2019 14:20
    Thanks for the quick response Laura.

    I tried using the case format and it's still not working. For some reason the ([Related Staff Assignment3 - Date Assigned]) value is not being recognized. I tried creating a "Date Assigned Captured" field within the table, which would pull in the current date of assignment from ([Related Staff Assignment3 - Date Assigned]), and still no luck :(

    ------------------------------
    Wallace (DHS) Yeboah
    ------------------------------



  • 29.  RE: Formula Syntax Error

    Posted 09-16-2019 14:22
    Can you call me, so we can do a quick screenshare; it's probably a super-simple issue to resolve - most likely faster over the phone.

    ------------------------------
    Laura Thacker (IDS)
    laura@intelligentdbs.com
    (626) 771 0454
    ------------------------------



  • 30.  RE: Formula Syntax Error

    Posted 09-16-2019 14:32
    Wallace- check that your records are showing a value in that field across your records.  Is the parent-relationship properly populated for the record you are looking at?  Does that parent record have a date value in that field?  Check your Lookup field is pointing to a field, even!

    ------------------------------
    Laura Thacker (IDS)
    laura@intelligentdbs.com
    (626) 771 0454
    ------------------------------



  • 31.  RE: Formula Syntax Error

    Posted 09-16-2019 14:02
    It looks like your formula has evolved since Mark pointed you in the right direction of If, statement, then, else.  Can you post what your whole formula looks like now so we can pinpoint more clearly what might be wrong.

    ------------------------------
    Laura Thacker (IDS)
    laura@intelligentdbs.com
    (626) 771 0454
    ------------------------------