I'm trying to write a script to present a subset of a table, and am not sure of the best logical approach.
The table is ads in a magazine. Each ad has an associated issueNumber and customerID. I want to show all ads that fall within a single issueNumber range (eg 10 ... 20) but omitting any records when the associated customer has an ad in a later issue (eg, issueNumber = 21 ... currentIssue). The point is to create a "lapsed customer" report where the customer hasn't advertised in a while, but I want to see their history within that period.
I can easily show ads 10-20, but it's the omit rule that has me stumped. Here's the ad table:
fk_issueID and fk_vendorID join out to the associated issue and customer tables.
My first thought is to first run a find on the later issues (21 ... currentIssue), get all the vendorIDs and put them into an array. Then run the find on the earlier issues and omit any where the fk_vendorID is in that array. But it's not clear to me if Filemaker even has arrays. Then I wondered if I could store the first find results and just run a match and omit directly without using an array.
Any advice welcome...