AnsweredAssumed Answered

Constrain found set based on a different found set: best practices?

Question asked by lokinyc on Jun 28, 2016
Latest reply on Jun 30, 2016 by lokinyc

Hi

 

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:

 

adID

adContent

fk_issueNumber

fk_customerID

 

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...

 

thanks!


Matt

Outcomes