I’ve created a Recurring Client Database. The client shows up weekly in the view based upon the day assigned using the WEEKDAY expression. If I have a client that isn’t weekly and is every other week is there a way to add this to my expression. I’ve been looking at the WEEKNUM expression, but I was wondering would an expression be possible using whether it was an even or odd WEEKNUM? Then I could assign the client odd or even based upon what week they are assigned?
For detecting odd and even values, you can use the
MOD() function (short for modulo or modulus) to yield a binary result that you can then handle with an IF() statement. See the following examples (*each line represents a standalone expression):
MOD(1, 2) = 1 MOD(2, 2) = 0 MOD(3, 2) = 1 MOD(4, 2) = 0 ...etc
This is a good way to check against what week of the month it is. Notice that each odd week will output a 1 and each even week will output a 0.
Like I said above, you can then just handle the output above with an IF() statement and replace the first input of the MOD() function with a column determining which week of the month it is.
See the documentation below related to the