AnsweredAssumed Answered

Converting a date to text to use in a search function script

Question asked by MattMinde on Jun 8, 2018
Latest reply on Jun 10, 2018 by MattMinde

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:

Screen Shot 2018-06-08 at 5.22.15 PM.png

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):


  1. (GetAsText(Circulation::DateAdjust) + 8) & "..." & (GetAsText(Circulation::DateAdjust) + 14)
  2. (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:

Screen Shot 2018-06-08 at 4.41.32 PM.png

The error for the second formula looks like this:

Screen Shot 2018-06-08 at 5.01.17 PM.png

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:


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

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



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