Syntax error on multiple If statements with variables

  • 1
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
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
Photo of Rachel

Rachel

  • 192 Points 100 badge 2x thumb
  • annoyed

Posted 1 year ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
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.
(Edited)
Photo of Rachel

Rachel

  • 192 Points 100 badge 2x thumb
Thanks. This would probably have come up later, but does not address the syntax error I am currently getting.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,302 Points 20k badge 2x thumb
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.
Photo of Rachel

Rachel

  • 192 Points 100 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
:)

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.
Photo of Rachel

Rachel

  • 192 Points 100 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
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

Here is a list of all the functions.

https://login.quickbase.com/db/6ewwzuuj?a=q&qid=6
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Here is an example of an IF with several tests.  You will see that the IF is not nested.

https://login.quickbase.com/db/bcgahn76w?a=dr&r=bm&rl=cwe
Photo of Rachel

Rachel

  • 192 Points 100 badge 2x thumb
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

THANK YOU!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
:)   // and I love the comments too.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,302 Points 20k badge 2x thumb
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.
(Edited)
Photo of Rachel

Rachel

  • 192 Points 100 badge 2x thumb
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. 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,302 Points 20k badge 2x thumb
Those are not required syntax rules but rather best practices ro make the formulas more readable.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Rachel, please post your current formula and the syntax error message after taking in to account my comments above.