Discussions

Expand all | Collapse all

Syntax error on multiple If statements with variables

  • 1.  Syntax error on multiple If statements with variables

    Posted 09-06-2017 22:39
    I cannot figure out why I have a syntax error in my formula. Everything seems to be correct.

    I am trying to calculate the due date/time based on the start date time field [Date Time]. I have to use workdayadd because I need to account for weekends differently. Essentially if someone submits a task Monday at 5pm, it's due Tuesday at 5pm. On Friday at 5pm, it should be due the following Monday at 5pm. On or after 8pm Friday until 11:59pm on Sunday, it should go to a due date of the following Monday at 8pm. 

    var number day = DayOfWeek(ToDate([Date Time]));
    var bool isFriday = If($day=5,true);
    var bool isSaturday = If($day=6,true);
    var bool isSunday = If ($day=0, true);
    var bool isFriday8pm = If($isFriday=true and ToTimeOfDay([Date Time])>=ToTimeOfDay("8 pm"),true);

    If($isSunday,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),2),ToTimeOfDay("8 pm")));
    If($isSaturday,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),3),ToTimeOfDay("8 pm")));
    If($isFriday,
    If($isFriday8pm,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),4),ToTimeOfDay("8 pm")),ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time])),2)),ToTimeOfDay([Date Time]))));

    This is my error: http://take.ms/7uHcv">http://take.ms/7uHcv">http://take.ms/7uHcv


  • 2.  RE: Syntax error on multiple If statements with variables

    Posted 09-06-2017 22:54
    Try changing the formula variable name to  name to

    isFridayEightPM

    You are not allowed to have numbers or spaces or special characters in a variable name.  Just plain vanilla A-Z  or a-z only.


  • 3.  RE: Syntax error on multiple If statements with variables

    Posted 09-08-2017 16:40
    Thanks. This would probably have come up later, but does not address the syntax error I am currently getting.


  • 4.  RE: Syntax error on multiple If statements with variables

    Posted 09-06-2017 22:57
    Semicolon at end of formula is the one of your problems. I think there are many more as Mark started to point out.

    The general problem is that your formula is not formatted for readability.


  • 5.  RE: Syntax error on multiple If statements with variables

    Posted 09-08-2017 16:41
    I edit my formula in notepad++ which provides color-coding, and it's formatted with tabs which go away when pasting here. This comment isn't helpful.


  • 6.  RE: Syntax error on multiple If statements with variables

    Posted 09-06-2017 23:09
    :)

    Right, I missed that ...

    If(
    $isSunday,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),2),ToTimeOfDay("8 pm"))),

    $isSaturday,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),3),ToTimeOfDay("8 pm"))),

    $isFriday and $isFridayEightPM,ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time]))),4),ToTimeOfDay("8 pm")),

    $isFriday and not $isFridayEightPM,  ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time])),2)),ToTimeOfDay([Date Time])))
    )

    There may be unbalanced brackets.  I did not try to reconcile the closing ))) at the end of each line.

    Note that with Quick Base, unlike Excel, you do not need to next your IFs.  You can just start with one IF( and then list all your tests and results below in sequence.

    That last ) at the end by itself is the closing to the beginning ( of the IF.


  • 7.  RE: Syntax error on multiple If statements with variables

    Posted 09-08-2017 16:45
    I was having trouble understanding how to format multiple if/then statements. I tried without nesting the ifs and the syntax worked but the logic was wrong. I will try again, but my biggest issue is that I am trying to check sequentially first, is it friday after 8pm? Then if it's not, i want to know if it's friday at all (before 8pm). Finally if neither of those are true, i want to check for the other conditions. That's why nested ifs make sense to me but the other structure was confusing.

    I did not know I could do "and not". That is exactly what I needed. Thanks. I think this is the missing link.


  • 8.  RE: Syntax error on multiple If statements with variables

    Posted 09-08-2017 16:50
    OK, post back if you are stuck.  You should be able to do this with just one IF, and then a series of tests and results.  Quick Base will take the first one which is true.

    Here is the help for the IF, which is different from Excel's IF

    https://login.quickbase.com/db/6ewwzuuj?a=dr&r=cm&rl=cwd">https://login.quickbase.com/db/6ewwzuuj?a=dr&r=cm&rl=cwd">https://login.quickbase.com/db/6ewwzuuj?a=dr&r=cm&rl=cwd

    Here is a list of all the functions.

    https://login.quickbase.com/db/6ewwzuuj?a=q&qid=6">https://login.quickbase.com/db/6ewwzuuj?a=q&qid=6">https://login.quickbase.com/db/6ewwzuuj?a=q&qid=6


  • 9.  RE: Syntax error on multiple If statements with variables



  • 10.  RE: Syntax error on multiple If statements with variables

    Posted 09-08-2017 17:08
    It's working! There were other syntax errors, but fixing my understanding of how to properly do the multiple if/then statement allowed me to easily work out the rest of the issues.

    <pre>
    //what day of the week is it? set variables for significant days: Friday, Saturday, & Sunday
    var number day = DayOfWeek(ToDate([Date Time]));
    var bool isFriday = If($day=5,true);
    var bool isSaturday = If($day=6,true);
    var bool isSunday = If ($day=0, true);
    //workday ends at 8pm Friday, so things submitted 8pm and later are considered next day
    var bool isFridayEightpm = If($isFriday=true and ToTimeOfDay([Date Time])>=ToTimeOfDay("8 pm"),true);

    //if a task is submitted Friday 8pm until Sunday 11:59 pm, make it due Monday 8pm
    //otherwise, task is due next day, same time (24 hrs)
    //in order to add the right number of days to the original timestamp, we have to check for Fri, Sat, and Sun separately
    If(
    $isSunday,
    ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time])), 2)), ToTimeOfDay("8 pm")), 
    $isSaturday,
    ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time])), 3)), ToTimeOfDay("8 pm")), 
    $isFriday and $isFridayEightpm,
    ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time])), 4)), ToTimeOfDay("8 pm")), 
    $isFriday and not $isFridayEightpm, 
    ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time])), 2)), ToTimeOfDay([Date Time])),
    ToTimestamp(ToDate(WorkdayAdd(ToWorkDate(ToDate([Date Time])), 2)), ToTimeOfDay([Date Time]))
    )
    </pre>

    And here is evidence the logic also works: http://take.ms/ZGtLG">http://take.ms/ZGtLG">http://take.ms/ZGtLG

    THANK YOU!


  • 11.  RE: Syntax error on multiple If statements with variables

    Posted 09-08-2017 17:37
    :)   // and I love the comments too.


  • 12.  RE: Syntax error on multiple If statements with variables

    Posted 09-06-2017 23:10
    Here is some additional advice:

    1. use an editor such as Notepad++ which has a feature to highlight in red balanced parentheses
    2. place a space after every comma inyour formula
    3. use formula variables for (a) any repeated quantity and (b) any deeply nested quantity
    4. pay attention to case for variable types - eg use Number not number
    5. add extra lines to your formulas to seperate statements into groups - for example formula variables and the final returned quantity
    6. indent If() and Case() statements and use multiple lines . Don't write if() or case() as lowercase
    7. add comments after you have a working formula to help jog your memory when you return to edit the formula later. comments are just lines noise when you are creating your formula initially
    8. Never test if a Boolean is true (eg If([checkbox] = true, ...). Rather just write [checkbox] 
    9. place a space on each side of operators (eg [a] + [b], If([foo] = "bar", ...)
    10. place your formula or code within a pre tag so it shows up in the forum as monospace. This also allows my formula parser to harvest it automatically.
    All these best practices will make it easier to scan your formula for errors.

    Here is a screenshot relevant to point 1 above (note the red highlighting):



    This was just a partial cleanup and discovery of your formula. There are so many issues it becomes unclear what your formula is trying to accomplish.

    Other than that use JavaScript whenever possible.


  • 13.  RE: Syntax error on multiple If statements with variables

    Posted 09-08-2017 16:48
    Thanks for the tips and formatting example. I already use some of these tips but not all. Particularly, I didn't know spaces were required after commas or that the variable type declaration was case-sensitive. Also didn't know the forum had the <pre> option. I will certainly use that in the future.

    I understand that the logic of my formula is not evident from reading the formula itself, which is why I tried to describe it in text before pasting the formula. 


  • 14.  RE: Syntax error on multiple If statements with variables

    Posted 09-08-2017 17:31
    Those are not required syntax rules but rather best practices ro make the formulas more readable.


  • 15.  RE: Syntax error on multiple If statements with variables

    Posted 09-08-2017 16:45
    Rachel, please post your current formula and the syntax error message after taking in to account my comments above.