FIND/OMIT/ADD TO FIND - on and on, how to script?

[vanderark1]

I guess I am an intermediate user. First, let me say that this Forum is absolutely indispensable to me!!

I don't post often, because I usually find just the answer I needed by searching past posts.

However, this one has me stumped. I am using FM 5.5.

I have a db of about 100,000+ records. Important fields are: Store#, Vendor# and Date. A single record in the db equals a single store visit on behalf of a single vendor.

There are only 150 different Store#'s and 90 different Vendor#'s and of course, dates going back two years.

I need to run a report that shows the last time EACH store was visited on behalf of EACH vendor. Sounded simple, but it's got me confused.

How do I script to find this?

Let's assume every store was visited at least once, on behalf of every vendor.

I can manually find a single vendor, look at the found set and manually omit all but the most recently dated record for each store. Now I have a found set of 150 records (150 stores x oldest dated visit for that single vendor).

Now I need to add on to this found set, the latest 150 for the next vendor, and so on.

This means when I complete this for all 90 vendors, I should have a found set of 13,500 records (150 Stores x 90 Vendors).

This is the most I would need to return. Once I figure this out, I can adapt it to find only certain vendors, only certain stores, etc.

But I can't get past this one.

Help?

[BobWeaver]

I would create a VendorXStore key field that is the concatenation of Vendor# and Store#.

I.E.:

Vendor# & "-" & Store#

Then create a selfjoin relationship on this field "sjVendorStore"

Then you can have a calculation field that shows whether the current record is the last one for that vendor/store.

cLatest = (Date = Max(sjVendorStore::Date))

cLatest will give a value of 1 for the record you are looking for, and 0 for the others. So, you can search for records where cLatest = 1. This will unfortunately be an unindexed search, so it won't be fast.

[cjaeger]

why not use export (with sorted "grouped by" report?) This will give you a copy of the data only with the last entries. Just experiment with the little checkbox in the export dialog ... but remember to sort the data first.

[vanderark1]

I will try the selfjoin route. I have created the script in only a few lines, using a loop, and globals, looking for and omitting the newer records and it works beautifully. Alas, very,very slow. And, as we are adding 2000+ records a week, it will only get slower.

I am still hoping for a FIND solution.

[vanderark1]

I used Bob's selfjoin option, and it worked great. While not "instant", it is infinitely faster than my looping script. Thanks Bob!