3 Replies Latest reply on Jun 30, 2016 7:51 PM by lokinyc

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

    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

        • 1. Re: Constrain found set based on a different found set: best practices?
          siplus

          Filemaker has arrays (and I'm a big fan of them - we call them repeating fields) but in your case you don't need them.

           

          What you need is a function we've been asking for a lot, available only as custom function or in plugins.

           

          This function takes 2 lists and returns the first list without the elements that exist in the second list. Instead of this very useful function, we got FilterValues, which... ok, no ranting.

           

          Basically, you will get the  2 lists of client pk's with ExecuteSQL's, take out the pk's existing in both lists from the first list via this function and be left with what you want in the first list, usable as a key in a relationship that can reveal the customer records via a GTRR.

          1 of 1 people found this helpful
          • 2. Re: Constrain found set based on a different found set: best practices?
            Extensitech

            If you were to create a self-relationship from ads, to later ads for the same customer (let's call this Ads_Ads_cus_later), could you do...

             

            Enter Find Mode

            Set field [ Issue Number ; "20...30" ]

            New Request

            Set field [ Ads_Ads_cus_later::pk ; "*" ]

            Omit Record

            Perform Find

             

            ?

             

            HTH

            Chris Cain

            Extensitech

            1 of 1 people found this helpful
            • 3. Re: Constrain found set based on a different found set: best practices?
              lokinyc

              Thank you!

               

              I'm familiar with SQL, but hadn't use ExecuteSQL before so I've been playing with it for a few days. I also don't have Advanced so have been staring at the ? symbol quite a bit before I ironed out the bugs...

               

              I love the BE plugin! It's very cool and fantastic that it's free.

               

              Thanks for your reply too, Chris, I ended up using the first solution though.

               

              Matt