There is a function called DayOfWeek that uses a date supplied to give you the number of the day. For example, if I was to do DayOfWeek(4/8/2014), I would get 3, since today is Tuesday and Tuesday is the 3rd day in a week.
Using this function, you could check if you are on Friday and then skip over Saturday and Sunday.
Hope this helps,
I believe this will accomplish what you're looking for.
Go to Layout [ layout ]
Set Variable [ $oldDate ; Date Field ]
Enter Find Mode [ ]
Set Field [ Date Field ; (calculation below) ]
Perform Find [ ]
Here's the calculation to use:
let ( [
numericDay = DayOfWeek ( $oldDate )
$oldDate + case ( numericDay < 6 ; 1 ; 9 - numericDay )
Note: It's best not to use Copy / Paste for performing Finds in this manner. When you do, you erase the user's Paste buffer and can also introduce the possibility of error in your script should the user press Ctrl- (or Cmd-) C during script execution. Better practice is to use Set Variable / Set Field to preserve the Paste buffer.
Edit: Corrected error in formula.
It worked....Thank you so much.....
Beginning I had a bit trouble, thanks for correcting the formula
PS: Any book you suggest that have plenty of "Let" and "Variable" examples..
Glad it worked for you.
I really don't know of any books off hand. I suggest you try the FileMaker Training Series.
Thank you Mike....
I just thot about the previous day search as well...
I changed the plus sign to minus...
$oldDate - case ( numericDay < 6 ; 1 ; 9 - numericDay )
Not working as the Next day search...
I am sure it is a piece of cake for you...
Now that you’ve discovered that doesn’t work, let’s discuss why.
The “numericDay” variable is the result of the DayOfWeek ( ) function. This function returns a number from 1 to 7 based on what day of the week the date you pass it would be (1 is Sunday, 2 is Monday, and so forth). The original calculation basically says, “If the day of the week is earlier than Friday, just add 1 to the date. Otherwise, add the difference between the day of the week and 9.” So how does that work?
Friday is 6. 9 - 6 = 3. So I add 3 to the date if it’s a Friday, which gives me the following Monday.
Saturday is 7. 9 - 7 = 2. So I add 2 if the date is a Saturday, giving me, again, the following Monday.
Now, if I just subtract the same clause, what happens?
If it’s earlier than Friday, I just subtract 1. So on Monday, I just subtract 1 and get … Sunday. Oops. So the function breaks.
How do we fix it? We work in reverse. What I want to do is subtract 1 from the date if it’s a Tuesday through Saturday. If it’s a Sunday, I want to subtract 2. If it’s a Monday, I want to subtract 3.
Tuesday - Saturday is DayOfWeek = 3 - 7. So my Case statement becomes:
Case ( numericDay > 2 ; 1 ; … )
That covers all my Tuesday through Saturday cases. (Note: DayOfWeek will never return anything greater than 7, so it won’t be a problem.) Now we have to figure out how to deal with Sunday and Monday. Sunday is DayOfWeek = 1. Monday is 2. I want to subtract 2 if it’s a Sunday, 3 if it’s a Monday. So my final calculation looks like this:
$oldDate - Case ( numericDay > 2 ; 1 ; numericDay + 1 )
Hopefully, that makes sense.
Mike, I am not sure how I thank you for educating me thru your very detailed and thourugh explanation of the calculation you wrote for me.
I am very happy to say that, now I can browse thru the records by next day and previous day and I can proudly say to my boss that I accomplished what he asked for.
THANKS A MILLION...........
You can thank me by helping someone else some day.