Showing unexpired date fields only

[Bikeman17]

Hi

I got two fields: Date 1 is a calc field (Creation date + 180), result is date.

And Date 2 is also a calc field (Today), result is date.

I created this script to get the list of expired dates:

If (Date 1 > Date 2)

Show omitted

End if

But something's wrong. Any help would be appreciated.

[CobaltSky]

Hi Bikeman17,

There are a couple of issues.

First of all, the Today function calculates once each time the database is opened (so if you keep the database open for several days, it falls behind the times...). I suggest that you use the Status(CurrentDate) instead, and make sure that the calculation field holding it is set to be unstored.

Now to the crux of the problem... Your scripted If statement is fine in itself - the problem is that the step that follows it is not one which acts to locate the records you require. There are several different approaches (such as a calculated comparison field and a scripted find) however, based on the approach you've been working towards, your script would need to look like the following:

[color:"green"]Allow User Abort [Off]

Show All Records

Go to Record/Request/Page [First]

Loop

If ["Date 1 > Date 2"]

Omit Record

EndIf

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

End Loop

If ["Status(CurrentFoundCount) < 1"]

Show Message ["Sorry, there are no expired records at present."]

Show All Records

EndIf

---

This should leave you with a list of the records for which the Date 1 calculation result has passed (if there are any - or if not, a message to that effect...).

[Todd Dignan]

Try this:

Elements:

1. two fields

Creation Date: datefield, auto enter creation date

Expired: Calcfield, Case((creation date+180) < Status(CurrentDate),1,"")

2. One Script

find expired: Allow User Abort [ Off ]

Set Error Capture [ On ]

Perform Find [ Request 1: expired date 1 ]

[ Restore find requests ]

If [ Status(CurrentError) = 401 ]

Show Message [ Buttons:

[Keith M. Davie]

tdignan, you have offered the line:

Perform Find [ Request 1: expired date 1 ]

I'm using FMPro 5.0.v3 and do not understand this. Could you please elucidate.

[mborgens]

You could do a 3rd calc field that subtracts the fields so you can track how expired each record is. Then create a script that is based off of a find of overdue records. mb

[Keith M. Davie]

Perform the find on the field (Creation date + 180).

See attached.

dates.zip

[Keith M. Davie]

FWIW, the attachement is in FMPro 5.0.v3

[Keith M. Davie]

Final caveat:

The attached solution addresses only the immediate problem as posited. It does not address design issues such as security, the running of another script at the time of request, nor record locking.

[Todd Dignan]

This is the preform find script step. There is a check box restore find request. This is generally fast than go to find mode set some field then preform find. The way it works is you manually do the find you want your script to preform. Use the preform find script step and check the restore find box. When your done with your script, click on the done button. Open the script again and close it. A window will appear asking you if you want to Keep or replace your restore find. Click replace. If you open the script after you close it this time, make sure you chose keep. When you chose replace it remembers the last find preformed no matter what the last find was. I hope this helps. Also check the attachment.

[Keith M. Davie]

tdignan, I just flat out missed your attachment. Oh.

Works great. But why do you Restore the find?

[Keith M. Davie]

I guess I was posting my Restore question as you were posting your response to my earlier.

The reason I inquired was that when I unchecked Restore, the find still worked. And yes, I clicked Replace when I unchecked.

I also wrote that script into my copy of dates.fp5 which I posted before seeing your solution. And when I wrote it, I wrote the Restore unchecked. It ran fine there as well.

[Keith M. Davie]

Then I re-read your response. And I then read the fine manual. Ok. I get it. I guess I've been concentrating too much on cdml lately and have gotten a bit rusty with the db's. Phew!

[Todd Dignan]

A restore find is faster than a regular find.