Thanks Doug. I was looking specifically for an example of build a filter from the "is one of" results. Below is what I have come up with. This is working, but I'm always open for improvement. Is there a better way for me to be doing this?
BTW the Stonefield Query help file mentions the following about a SFQ message board. Is this still available?
A message board for Stonefield Query users and developers is available at:
http://www.contactreview.com/mb/postlist.php?Cat=&Board=stonefieldqueryloDataSource = toDatabase.CurrentDataSource
lcDirectory = loDataSource.Database
lcFilter = ""
lcCaption = ""
loValues = SQApplication.DataEngine.GetValuesForField('Loan.cStatus','is one of')
if loValues.Count > 0
loValue = loValues.Item(1)
loValue1 = loValue.Value
if len(alltrim(loValue1)) > 0
lcFilter = 'LOAN.cStatus=loValue1'
lcCaption = "Status Is One Of " + loValue1
endif
loValue = loValues.Item(2)
loValue2 = loValue.Value
if len(alltrim(loValue2)) > 0
if len(alltrim(lcFilter)) > 0
lcFilter = alltrim(lcFilter) + '.or. LOAN.cStatus=loValue2'
lcCaption = lcCaption +", " + loValue2
else
lcFilter = 'LOAN.cStatus=loValue2'
lcCaption = "Status Is One Of " + loValue2
endif
endif
loValue = loValues.Item(3)
loValue3 = loValue.Value
if len(alltrim(loValue3)) > 0
if len(alltrim(lcFilter)) > 0
lcFilter = alltrim(lcFilter) + '.or. LOAN.cStatus=loValue3'
lcCaption = lcCaption +", " + loValue3
else
lcFilter = 'LOAN.cStatus=loValue3'
lcCaption = "Status Is One Of " + loValue3
endif
endif
loValue = loValues.Item(4)
loValue4 = loValue.Value
if len(alltrim(loValue4)) > 0
if len(alltrim(lcFilter)) > 0
lcFilter = alltrim(lcFilter) + '.or. LOAN.cStatus=loValue4'
lcCaption = lcCaption +", " + loValue4
else
lcFilter = 'LOAN.cStatus=loValue4'
lcCaption = "Status Is One Of " + loValue4
endif
endif
loValue = loValues.Item(5)
loValue5 = loValue.Value
if len(alltrim(loValue5)) > 0
if len(alltrim(lcFilter)) > 0
lcFilter = alltrim(lcFilter) + '.or. LOAN.cStatus=loValue5'
lcCaption = lcCaption +", " + loValue5
else
lcFilter = 'LOAN.cStatus=loValue5'
lcCaption = "Status Is One Of " + loValue5
endif
endif
loValue = loValues.Item(6)
loValue6 = loValue.Value
if len(alltrim(loValue6)) > 0
if len(alltrim(lcFilter)) > 0
lcFilter = alltrim(lcFilter) + '.or. LOAN.cStatus=loValue6'
lcCaption = lcCaption +", " + loValue6
else
lcFilter = 'LOAN.cStatus=loValue6'
lcCaption = "Status Is One Of " + loValue6
endif
endif
loValue = loValues.Item(7)
loValue7 = loValue.Value
if len(alltrim(loValue7)) > 0
if len(alltrim(lcFilter)) > 0
lcFilter = alltrim(lcFilter) + '.or. LOAN.cStatus=loValue7'
lcCaption = lcCaption +", " + loValue7
else
lcFilter = 'LOAN.cStatus=loValue7'
lcCaption = "Status Is One Of " + loValue7
endif
endif
loValue = loValues.Item(8)
loValue8 = loValue.Value
if len(alltrim(loValue8)) > 0
if len(alltrim(lcFilter)) > 0
lcFilter = alltrim(lcFilter) + '.or. LOAN.cStatus=loValue8'
lcCaption = lcCaption +", " + loValue8
else
lcFilter = 'LOAN.cStatus=loValue8'
lcCaption = "Status Is One Of " + loValue8
endif
endif
loValue = loValues.Item(9)
loValue9 = loValue.Value
if len(alltrim(loValue9)) > 0
if len(alltrim(lcFilter)) > 0
lcFilter = alltrim(lcFilter) + '.or. LOAN.cStatus=loValue9'
lcCaption = lcCaption +", " + loValue9
else
lcFilter = 'LOAN.cStatus=loValue9'
lcCaption = "Status Is One Of " + loValue9
endif
endif
loValue = loValues.Item(10)
loValue10 = loValue.Value
if len(alltrim(loValue10)) > 0
if len(alltrim(lcFilter)) > 0
lcFilter = alltrim(lcFilter) + '.or. LOAN.cStatus=loValue10'
lcCaption = lcCaption +", " + loValue10
else
lcFilter = 'LOAN.cStatus=loValue10'
lcCaption = "Status Is One Of " + loValue10
endif
endif
else
lcFilter = ""
lcCaption = ""
endif
toApplication.AddProperty('CustomFilter', lcCaption)
if len(alltrim(lcFilter)) > 0
SELECT SUM(nloan_amount) as totalloans FROM (lcDirectory + '!loan') WHERE &lcFilter INTO CURSOR c1
else
SELECT SUM(nloan_amount) as totalloans FROM (lcDirectory + '!loan') INTO CURSOR c1
endif
if len(alltrim(lcFilter)) > 0
SELECT clender_loan_number,;
nloan_amount,;
PADR(ICASE(NLOAN_AMOUNT >= 0 .and. NLOAN_AMOUNT <= 24999, "$0 - $24,999",;
NLOAN_AMOUNT >= 25000 .and. NLOAN_AMOUNT <= 49999, "$25,000 - $49,999",;
NLOAN_AMOUNT >= 50000 .and. NLOAN_AMOUNT <= 99999, "$50,000 - $100,000",;
NLOAN_AMOUNT >= 100000 .and. NLOAN_AMOUNT <= 249999, "$100,000 - $249,999",;
NLOAN_AMOUNT >= 250000 , "$250,000 and Above"),20) as cRange ,;
ICASE(NLOAN_AMOUNT >= 0 .and. NLOAN_AMOUNT <= 24999, 1,;
NLOAN_AMOUNT >= 25000 .and. NLOAN_AMOUNT <= 49999, 2,;
NLOAN_AMOUNT >= 50000 .and. NLOAN_AMOUNT <= 99999, 3,;
NLOAN_AMOUNT >= 100000 .and. NLOAN_AMOUNT <= 249999, 4,;
NLOAN_AMOUNT >= 250000 , 5) as nPrint_Order FROM (lcDirectory + '!loan') WHERE &lcFilter INTO CURSOR c2
else
SELECT clender_loan_number,;
nloan_amount,;
PADR(ICASE(NLOAN_AMOUNT >= 0 .and. NLOAN_AMOUNT <= 24999, "$0 - $24,999",;
NLOAN_AMOUNT >= 25000 .and. NLOAN_AMOUNT <= 49999, "$25,000 - $49,999",;
NLOAN_AMOUNT >= 50000 .and. NLOAN_AMOUNT <= 99999, "$50,000 - $100,000",;
NLOAN_AMOUNT >= 100000 .and. NLOAN_AMOUNT <= 249999, "$100,000 - $249,999",;
NLOAN_AMOUNT >= 250000 , "$250,000 and Above"),20) as cRange ,;
ICASE(NLOAN_AMOUNT >= 0 .and. NLOAN_AMOUNT <= 24999, 1,;
NLOAN_AMOUNT >= 25000 .and. NLOAN_AMOUNT <= 49999, 2,;
NLOAN_AMOUNT >= 50000 .and. NLOAN_AMOUNT <= 99999, 3,;
NLOAN_AMOUNT >= 100000 .and. NLOAN_AMOUNT <= 249999, 4,;
NLOAN_AMOUNT >= 250000 , 5) as nPrint_Order FROM (lcDirectory + '!loan') INTO CURSOR c2
endif
SELECT nPrint_Order,cRange, COUNT(clender_loan_number) as NumberOfLoans, SUM(nloan_amount), ((SUM(nloan_amount)/c1.totalloans) * 100) as perdollars group BY 1,2 FROM c2 into cursor (tcCursor)
return .T.