3 Replies Latest reply on Mar 16, 2013 12:16 PM by DanielShanahan

    Constrain a found set with ExecuteSQL

    DanielShanahan

      I need help constraining a found set using ExecuteSQL. I'm trying to find and tally the top five items from the current quarter. This worked fine:

       

      Let ( [

      _q = "

      SELECT ilgiid, SUM (ilgqty) AS ilh_qty

      FROM itemLedgerHistory

      WHERE ilgiid LIKE 'MS-%' AND ilgdat BETWEEN ? AND ?

      GROUP BY ilgiid

      ORDER BY ilh_qty DESC " ;

      _r = ExecuteSQL ( _q ; "…" ; "" ; firstDayQuarter ( Get ( CurrentDate ) ) ; Get ( CurrentDate ) ) ] ;

       

      List ( GetValue ( _r ; 1 ) ; GetValue ( _r ; 2 ) ; GetValue ( _r ; 3 ) ; GetValue ( _r ; 4 ) ; GetValue ( _r ; 5 ) )

       

      ) // end Let

       

       

      However, I was then asked to constrain the found set to omit any records that are "MS-JA", so I modified the query to this:

       

      Let ( [

      _q = "

      SELECT ilgiid, SUM (ilgqty) AS ilh_qty

      FROM itemLedgerHistory

      WHERE ilgiid LIKE 'MS-%' AND NOT LIKE 'MS-JA%' AND ilgdat BETWEEN ? AND ?

      GROUP BY ilgiid

      ORDER BY ilh_qty DESC " ;

      _r = ExecuteSQL ( _q ; "…" ; "" ; firstDayQuarter ( Get ( CurrentDate ) ) ; Get ( CurrentDate ) ) ] ;

       

       

      List ( GetValue ( _r ; 1 ) ; GetValue ( _r ; 2 ) ; GetValue ( _r ; 3 ) ; GetValue ( _r ; 4 ) ; GetValue ( _r ; 5 ) )

       

       

      ) // end Let

       

      Unfortunately, it returned the dreaded "?".

       

      I've considered doing this natively with the FileMaker query engine. It is fairly straight forward, first finding "MS-" and then omitting "MS-JA" with a Constrain find. However, counting unique items in that found set seems laborious. That's why I went with ExecuteSQL ().

       

      Any suggestions?