Date calculation

[SteveS]

I want to make a script perform a calculation based on date fields. Example, an individual registers for a course on (date field 1). This individual's date of birth is (date field 2). Everyone who registers for this course must re-register annually within one year based on the aniversery of their birth date (date field 2). I want to make a script that will find each individual record that exceeds 365 days.

Could anyone help me with this tough one?

[DanBrill]

If I understand correctly, you just want to find the day that is 365 days after their birthday if they have registered for the course. ?

If that's the case, find everyone that has a value for date field 1. Then just create a loop that checks each record to see if it is 365 days past their last birthday and tags it:

Find [date field 1 > 1/1/1000] (since you can't find * in a date field just put in a really early date)

Go To Record/Request/Page [First]

Loop

If ["date(month(date field 2), day(date field 2), year(status(currentdate))-1)+365 < status(currentdate)"]

set field ["Tag" , "1"]

End If

Go To Record/Request/Page [Exit after last, Next]

End Loop

I think that should do it.

Good luck,

Dan

[ESpringer]

SteveS,

I'm confused about "must re-register... within one year based on... birth[day]"... Surely if someone registers TODAY for a course and has a birthday NEXT WEEK, you're not going to require re-registering because of this birthday, right? So, there's a bit more complexity than what you described... What if my first registration is two months before my birthday? Where do you draw the line? Also, if I re-registered TWO weeks before my birthday last year, surely it's OK to re-register ONE week before my birthday this year, right? So, it would not be strictly speaking true that they must always re-register "within 365 days" (because then with phase shift over time, re-registering would have to happen long before the birthday)... I'm just guessing based on common sense...

My guess is that the logic has to be: the first time someone signs up, they pay (if there's payment?) only for a pro-rated part of the year -- the fraction that will pass before their next birthday -- or they can tackle the next year together with the initial registration if it's almost birthday time. From then on, their registrations count for the year from birthday to birthday, or something like that. You don't *really* care what day they re-register on; re-registering counts as if it occured on their birthday, and they have up 'til their next birthday to do it again. Is that right? (otherwise, there's a disincentive for re-registering promptly).

What do you want your script to do? Do you want it to flag people whose birthday is, say, within five weeks, so as to send a notice inviting re-registration? In that case your script just needs to see whether

Date(BirthMonth,Birthday,ThisYear) - Status(CurrentDate) < 35

Perhaps I haven't understood your situation. But it seems to me that if an individual always must re-register within 365 days of the last registration, the birthday becomes irrelevant.

[SteveS]

Thank you for your reply Dan, I thing this will work!

And thank you for your reply ESpringer too!

Just to clear up some confusion, the original registration is based on other factors. They must re-register annually on their birthday, I wanted to find all records that exceeded this time frame.

Steve

[ESpringer]

SteveS, my apologies for not remembering (once I went to reply screen) that you wanted to see which birthdates had already passed... but it seems Dan figured that out, and beat my post by 5 minutes...

[SteveS]

Dan,

I tried the calculation as suggested and I keep coming up with all the "Tag" fields showing number one, no matter what date shown in the registion (date field 1).

I am far from an expert at this and do not know where I went wrong after following your detailed example. I performed a "find" in the original registration date field (date field 1) and all records showed up "> 1/1/1000." After this step something went wrong.

I want to find the total amount of days that have elapsed since the individual registered for the course, starting annually, when their birth date arrives (date field 2) and ending at (satus(currentdate). Then I can perform a "find" and locate all records of people that need to re-register >365.

I appreciate your time and patience.

Steve

[DanBrill]

So Tag is being set to 1 for every record in the database? Even if there is nothing in the date field 1? It sounds like there is something wrong with this find.

From Browse mode perform the find date field 1 > 1/1/1000. Then go to script maker. The first step of your script will be to perform the find you just executed in Browse mode: Perform Find [Restore, Replace Found Set]. By having the Restore option set, it will restore the find option in place when you made the script. As you exit script maker it may ask you if you want to keep or replace the find options. Choose replace this time so it will clear out anything that was in there and replace it with the good information that you just produced in Browse mode. (Next time you edit this script, select Keep so that it keeps the find as you wanted it.)

Once we get that cleared up we can see if we're actually tagging the records that you are trying to find.

Dan

[-Queue-]

I would think all records with a date should show up as greater than Jan 1, 1000, but empty ones shouldn't. It's much easier, however (I think), to Insert Text [select, "date", "="] and Omit the request to find all non-empty dates, seeing as there may be regional issues involved here, and 1/1/1000 is not what you think it is. If that's the case, then you'll need to use Insert Calculated Result [select, "date", "">" & DateToText( Date( 1, 1, 1000 ) )"].

In either case, make sure the date field is on the layout or it will fail automatically.

[SteveS]

Dan,

I performed the Perform Find (Restore) in the script and had the same results, showing "tag" field as number 1. I defined the "tag" field as a number feild, could that have anything to do with it?

Steve