Enter Find Mode [ ]
Set Field [ Appointments::Date = GetAsText ( Date ( Month ( Get ( CurrentDate ) - 3 ) ; Day ( Get ( CurrentDate )) ; Year ( Get ( CurrentDate )))) & "..." & GetAsText ( Get ( CurrentDate )) ]
At least for a starting point. You'd probably want to use the Let function to keep from parsing out the Get ( CurrentDate ) function multiple times, but you get the idea.
1 of 1 people found this helpful
But this would not include Clients without an Appointment at all. I would instead reverse the find, searching for all Clients who have appointments within the timeframe and then Show Omitted. Also, it wasn't mentioned and I know you know this - it can be ran from Clients - no need to go to the Appointments table. The date in Appointments is stored so the search will be fast.
Also no need to wrap with GetAsText() since you are setting a date field.
Grr ... I meant to say "Omit". You're right. I had it that way when I was writing it as a simple Find request and it got lost in the transition to a script step. What you really want is to Find [Omit Records] (insert calculation above). That will omit all records that do not have a date within that range.
And yes, you can run this from either context; but I agree that it should be run from the Clients context since you're looking for clients, not appointments.
Good tip on the GetAsText. Used to be, if you just used the Date function, it would give you the raw number value if you parsed that out in a calculation with the range characters (...). Seems they've fixed it.
Using the data viewer i put together your example but i'm not sure i'm doing something correct. The calculation i am attempting is:
cdate = Get ( CurrentDate )
Date ( Month ( cdate - 3 ) ; Day ( cdate ) ; Year ( cdate )) & "..." & cdate
According to the data viewer, this evaluates to:
One thing i probably should have mentioned before but i'm not sure if it has any effect on the calculation would be the date format. This database uses a date format of dd\mm\yyyy on all date fields. Would that have any ill effects on this search?
Syntax error. :-)
Month ( cdate - 3 ) gives you the month of the current date, minus three days. What you want is the month of the current date, minus 3. Try:
Month ( cdate ) - 3
P.S. Date format is irrelevant; that's why we use the Date function. It tells FileMaker we're dealing with dates; it'll handle figuring out the particulars. Gotta love it.
Apparently one cup of coffee this morning wasn't enough, DOH! Made the change and not it is evaluating properly.
As stated above, this shows all records with appointments in that time frame. Where does the Omit come into play. I've never used the omit function before so i want to make sure i do it properly.
It seems using the Omit Record script step locks me to one record which does have an appointment in the range. Would i use the Omit Multiple Records step?
Thanks for all of your help! Greatly appreciated.