Omit blank records

[Allegheny]

I've been stuck all day trying to build a script that will sort records by date, then omit the records that have no date.

It seems the "ifEmpty" function will do the trick. So my script looks like this:

Go to record/request/page [first]

Loop

If ["isEmpty(year)"]

omit record

End if

Go to record/request/page (exit after last, next)

End loop

Sort (restore)

I've got the sorting part down, but why won't this script omit ALL the blank records?

[LeCates]

Why not omit the empty dates as a Find request before sorting?

Regardless, the problem with your script is that when you Omit a record, FileMaker will automatically proceed to the following record, in which case you are again jumping to the next record after your End If. Thus, you acutally omit one - skip one - omit one - skip one, etc.

You can fix your script as follows:

Go to record/request/page [first]

Loop

If ["isEmpty(year)"]

omit record

Else

Go to record/request/page (exit after last, next)

End if

End loop

Also, you might want to add a qualifier at the beginning of the Loop in case all your records have empty dates, so that it knows to bail from the Loop if Status(CurrentFoundCount) < 1.

Good luck!

[Vaughan]

A find for an empty date will be much, much faster. And easier to script.

[LeCates]

Hi Allegheny,

I agree with Vaughan that this is probably true, but not always so.

quote:

---

Originally posted by Vaughan:

A find for an empty date will be much, much faster. And easier to script.

---

So for the record, if the field to be omitted is for some reason unstorable, then it might slow the Find process moreso than a looping omit.

For example, a few years back I had a 200,000 record database and wanted to commit a 5 attribute search. 4 of the attributes were indexed, 1 was not. The 5 attribute search took somewhere between 5 and 10 minutes. Searching on just the 4 indexed attributes took about 30 seconds, and left me with 500 records.

Looping through just the 500 records, and omitting records that failed against the unstored attribute took about 15 seconds.

Net result: Find4 + Loop was a lot faster than Find5.

In any case, looping omit scripts are useful in many scenarios, so it's worth figuring out how to make them work.

HTH. Good luck