Hi all!

First and foremost, I'm using **FMPro 9 on a Mac, with OS X 10.8 – macOS Sierra** (yes, it's old, but it still works on a wide range of Mac OS!)

This may be a bit complicated. We operate a tiny newspaper and need to generate renewal emails. So here are the ugly details:

- Our paper comes out each Thursday.
- I need to generate a range of renewal dates based on the issue date, i.e., Thursday's date.
- The issue date is generated using the current date, and is called
**DateAdjust**, so that if someone's creating a list of renewals on Monday, the**DateAdjust**is generated using Current Date + 3. On Tuesday, it's Current Date +2, and so on, per below:

**DateAdjust = **

Case(Day of week ="Monday"; (Get ( CurrentDate ) +3);

Day of week ="Tuesday"; (Get ( CurrentDate ) +2);

Day of week ="Wednesday"; (Get ( CurrentDate ) +1);

Day of week ="Thursday"; (Get ( CurrentDate ));

Day of week ="Friday"; (Get ( CurrentDate ) -1);

Day of week ="Saturday"; (Get ( CurrentDate ) -2);

Day of week ="Sunday"; (Get ( CurrentDate ) -3))

So that we get this, for example, where "**6/7/2018**" is the output of the field **DateAdjust**:

What I need to do to now is generate a subscriber renewal date range based on the current issue date. That's where the problem seems to lie.

In a script I wrote that generates a range of dates one to two weeks out from the current date, I've tried the following calculations, neither of which work (note "Calculation result must be Date", per note at bottom left of calculation window):

- (GetAsText(Circulation::
**DateAdjust**) + 8) & "..." & (GetAsText(Circulation::**DateAdjust**) + 14) - (Circulation::
**DateAdjust**+ 8) & "..." & ( Circulation::**DateAdjust**+ 14)

The script then pastes the result of the calculation into a field, and attempts to search for subscribers with renewal dates in that range.

The error for the first formula looks like this:

The error for the second formula looks like this:

What does work is:

GetAsText(Get(**CurrentDate**) + 8) & "..." & GetAsText(Get(**CurrentDate**) + 14)

But because the above formula only works off the current date, the range of dates it generates will vary with the day of the week that the script is run. Basically, it generates the correct range of renewal dates for an issue only on Wednesdays.

No matter what I do, I cannot seem to get FMPro to use the modified date, **DateAdust**, for the calculation. What am I doing wrong here? Will I be doomed to make a kludgy conditional for the range of dates based on each day of the week, like I did with **DateAdjust**, as in:

Case(

Day of week ="Monday"; (GetAsText(Get(**CurrentDate**) + 11) & "..." & GetAsText(Get(**CurrentDate**) + 17));

Day of week ="Tuesday"; (GetAsText(Get(**CurrentDate**) + 10) & "..." & GetAsText(Get(**CurrentDate**) + 16));

etc.

I have not tried this and think it might work, but it's not elegant, and we have four sets of renewals to generate: 1st Notice, 2nd Notice, Final Notice and "Please Come Back" notice. That's a lot of kludging.

Is there another way?

Sincerely, MM

You haven't posted your full script, but it appears that you are using a script with this basic structure:

Enter find mode

use set field steps to specify search criteria

perform the find.

This is a good approach and the preferred method that I myself use. Notice how it follows the same basic pattern as a manual find where you would a) click the find button, b) enter search criteria and c) click perform find. This is not why I prefer this method, I like the fact that I can easily see all the needed search criteria without opening another dialog. But it can make it easy for people learning how filemaker works to understand how the script functions.

You have to pay attention to what happens in your solution when a window enters find mode. At that point, all fields except those with global storage specified become empty so that the user can enter search criteria into them. Thus this step:

(Circulation::DateAdjust + 8) & "..." & ( Circulation::DateAdjust + 14)

Doesn't work as expected in Find mode because in find mode, DateAdjust is empty. Thus you get a value of 8 .... 14 as you add 8 and 14 to null respectively.

So you either put the value of this field into a variable or a global field BEFORE you enter find mode in order to get your script to work.

Example:

Set Variable [$FindRange ; value: Circulation::DateAdjust + 8 & "..." & Circulation::DateAdjust + 14 ]

Enter Find Mode [pause: off ]

Set Field [YourTable::YourDateFIeld ; $FindRange ]

Perform Find [ ]

FileMaker date calculations work quite well. The criticisms that you have read are likely from those that want more built in functions so that the developer doesn't have to build a calculation each time they need a particular result. The calculations that you can do, calculate accurate results as long as you understand how they work. The basic concept you seem to understand already, that dates record time as the number of days from an arbitrary early date. Thus you can calculate a new date by adding the number of days to that date.

For more examples of scripted finds, see the first part of this thread: (The latter part got hijacked into a major debugging session for why one user's script didn't work.)

Scripted Find Examples