Find that returns no results

[daddioja]

I have a script set up in my database to run on startup. It performs a find on the records entered in the last seven days from the current day and runs a calcuation on the results. If no records have been added in that time, a message stating "No records match this request" pops up. I have to hit continue. I would like to automate it so that it returns a value of zero on the calculation if no records exist in that set of dates. Any suggestions? Thanks.

[Ugo DI LUCA]

Hi,

Why not use a relationship here.

- g_DateRange (global field)

- YourdateField

- relationship 'RelByRange' with g_DateRange matching YourDateField

Then the script.

SetField[g_DateRange,

DateToText(Status(CurrentDate)-1)&"

[daddioja]

Thanks for your help, but I am still not there yet. I don't understand the relationship by range thing. I was unable to find it in my help file. Is it a FM7 thing? Or can you explain it a little for me? I am also not sure what character that is between the quotes. Thanks a lot.

[MoonShadow]

A relationship is a good way to go; however, all you need in your Find script is Set Error Capture [On] before your find. This will stop the 'No records found' FM message and allow you to handle it yourself.

Then add a test immediately after your Perform Find [] of:

If [not Status ( CurrentFoundCount )]

... this produces a 1 (boolean) if 0 records found.

... perform your script action if none found here.

End If []

[Ugo DI LUCA]

Hi again,

This technique just takes advantage of how handy Mulitline fields are when used as a key for a relationship.

If structured with carriage returns in it, a key field will be a good candidate for an "Or" find, as each of its lines will become an index matching the other side of the relationship.

As relationships are much faster than finds, it make sense to use relationships of this kind.

The only restriction is that the result must be "Text ", which is why the above formula used DateToText ( ) in it.

If today's date is September 4th, the formula suggested will return a cDateRange key of type :

04/09/03

04/09/02

04/09/01

04/08/31

04/08/30

04/08/29

04/08/28

If linked to your DateEntered field through a relationship, you'll find that you'd be matching all records with a 'DateEntered' comprised within the range of the cDateRange above.

You can draw a portal with this relationship and see the result, or use a calculation, or use the relationship enclosed in a SetField[ ] script step, according to what better fit your needs.

Hope this make sense.

[daddioja]

Thank you both. I will try out these suggestions and see what I can get to work with my db.

[MoonShadow]

Ugo? Instead of using the pipe character, didn't you mean

[Ugo DI LUCA]

Hey,

That's what it does when I post from the office with I.E., without using the Forum shortcut for the Carriage return.

As the later post was made a few minutes after yours, please consider I wasn't insisting about a relationship vs your Find script, but rather giving some enlightment about my first post.

"Yep, I like relationships myself but lately I've only had self-relationships. Bummer!"

LOL