Date alarm script

[Razzman]

I have a Sales Lead contact DB (FMP v6.0.3). I have created a field which I can set a Follow-up Date which could be two weeks or a year from the record's modification date. I am trying to find a Script that will 1) Find all of the contacts in my file where the current (Today's) date is greater than the "Follow Up Date". Basically create a list view of all of the contacts that require follow-up. I know that I will kick myself because it sounds easy. I just can't figure it out.

thanks.

[kenneth2k1]

Hi:

One simple script would be

Enter Find Mode [ ]

Go to Layout [A layout that the Follow Up Date is in]

Insert Calculated Result ["Follow Up Date", ""<" & DateToText(Status(CurrentDate))]

Perform Find [ ]

Go to Layout [A List View Layout]

Have fun!

Ken

[Razzman]

Thanks. It works, except that it return every contact. That's because most of the Follow Up Date fields are empty, but I can work on this. BTW-How's Junior Seau's knee going to be?

[kenneth2k1]

You could put this in there to omit blank date field records:

New Record/Request

Set Field ["Follow Up Date", "="]

Omit Record

<<then perform the find>>

I heard earlier today that Seau has a sprained ankle and will most probably play against New England.

Ken

[cmartin]

I am wanting to do something very similar to this:

New Record/Request

Set Field ["Follow Up Date", "="]

omit Record

<<then perform the find>>

Is this "Omit Record" script step available in FileMaker Pro 4.0? Or is it new to 5.0 because I cannot seem to find it.

Thanks in advance.

[kenneth2k1]

Sorry, a slight error here. It should be like this:

Enter Find Mode [ ]

Go to Layout [layout date field is on]

Insert Calculated Result ["Follow Up Date", "="]

Omit Record

Perform Find [ ]

Or, you can perform the find manually:

1. Enter Find Mode.

2. Select your date field and put a = sign in there.

3. Check the Omit box on the Status Bar.

4. Click Find or press Enter.

5. Go to ScriptMaker and make a script that goes like this:

Perform Find [Restore]

This will perform the last find you did.

As far as the Omit Record step, I cannot remember if 4.0 has it or not, but in 5.5, it is found under the Sort/Find/Print steps in ScriptMaker.

Ken

[cmartin]

Thanks for the info. I looked and looked and couldn't find it so I'm thinking it is not available in 4.0. Just for the record, I was trying to avoid using perform find because I was performing multiple finds throughout the script:

1. First finding and clearing all flags from any record that had them set

2. Finding on a certain set of criteria and flagging those records

3. Performing a relookup only for those records that are found (wanted to limit the number of records I had to perform this step for as the relookup was slowing down the script when it was checking all records)

4. Finding on the records that were flagged in step 2 while omitting those where the looked up field contained a specific value

I ended up just writing a bunch of subscripts for each of these and placing the perform find(restore) in each

Omit Record would have saved me one script because I could have combined step2 and 4 as one script and then just added on the omit request but oh well, thanks for the help!

[CobaltSky]

The 'Omit Record' script step was present in v4 but it was simply called 'Omit'. You'll find it listed in the Sort/Find/Print group of steps between the 'Find Omitted' and 'Omit Multiple' steps.

If there is any chance that your database will ever be accessed in multi-user mode, you should consider that the record flagging technique you are using could lead to some unintended outcomes if the script is run by more than one user at once (eg flags set by one script will be cleared by the other while the relookup is running).

BTW, as regards the Perform Find [restore] script step, it would seem that a more suitable technique for your purposes would be to use the "Enter Find Mode [Restore]" step, which would enable you to restore a two-request find with the "=" and Omit flag set for the second request, and then have your subsequent script steps set the appropraite criteria up for the first request.

A further options you might wish to consider would be the creation of an unstored calculating field which flags dates which are passed (but not empty), and produces an appropriate text warning. A single request 'find' on this field would then locate the records in one script step.

Hope the above suggestions give you some more ideas to work with

[cmartin]

Such a helpful response!

In regards to the Omit in v4, in the explanation of this script step from the help index, it said that this script step could only be used in browse mode and only to omit one record, so I didnt think I could use it to omit a request in find mode, but i will most certainly try it, as i have a feeling that you know a little more than me (which, not to knock you, is not saying much)

Then, there is the flagging in multi-user mode which I did not even consider. Now I see that this can most definitely lead to big probelms. EEK! Just when I thought I had it all figured out in regards to this script. *******.

Finally, you are exactly right that the enter find mode (restore) would be far more appropriate but again something i didnt consider. will most definitely implement!

It looks as though going forward, I will eliminate the mark strategy. Instead in step4 I will simply refind on the original criteria with the relooked up values and the addition of the omit. the mark is not really necessary, i suppose. Just thought it would speed up the find.

Thanks a bunch!

-Courtney

[cmartin]

btw-

i think that the final paragraph of your reply relating to the date field was actually not directed at me but at the original poster of this thread (which i guess i kind of inadvertently hijacked). i was not attempting to do what he was doing exactly just something similar. i will start a new thread next time, sorry!

[kenneth2k1]

Once again Ray saves the day with thoughtful insight.

Another thing I like to do when scripts are used to analyze a db is to find on calc fields. However, this is not always useful. If you are so inclined, you can tell us what your setup is, what you are flagging, and what you are trying to accomplish. Then the experts like Ray can advise on the best route.

Ken

[cmartin]

Kenneth-

i am most definitely so inclined and I actually posted in Finding and Sorting (see Speeding up Find on a Related Record) a brief synopsis of what I am trying to accomplish. no time right now to recap but tomorrow I will do so (and try to explain a bit better). the gist of why this all started was because I was doing find on calc fields but since the relationship they were based on was based on a calculated field, i could not use them in find mode (I think that was what was going on). Also I could not store or index on the calc field and this led to extremely poor speed performance, sooooo this is what developed instead...using a lookup field and relooking up as needed.