AnsweredAssumed Answered

Constrain a found set with ExecuteSQL

Question asked by DanielShanahan on Mar 16, 2013
Latest reply on Mar 16, 2013 by 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?

Outcomes