I’m searching on a Foundset, and one of the terms is:
transFoundset.reference1 = thisTx.reference1;
thisTx.reference1 is a string, and the value is ‘10668…’
The search should return one record, however it doesn’t, and inspection of the foundset SQL reveals that the SQL being used to search contains:
'and customertransactions79.reference1 between ? and ?'
and the parameter value for reference1 has become ‘10668’.
So it appears that the ‘…’ is being treated as the ‘between’ operator.
So my question is - is there an easy way to handle that and all other instances where similar search operators might be included in a search term? Escaping or similar?
:edit: By easy way I mean without having to write a function to check for each special operator.
I don’t think you can escape the operators that Servoy uses.
In your case I think you do 2 things.
use a LIKE by making the search criteria loose some of the periods and add a percentage at the end.
// drop the last character and add a percentage sign to trigger the LIKE operator
transFoundset.reference1 = utils.stringLeft(thisTx.reference1, thisTx.reference1.length-1) + '%';
use SQL so you have full control over how search is done:
foundset.loadRecords("SELECT pkColumn FROM tableName WHERE reference1=?", [thisTx.reference1]);
Thanks … it needs to be an exact match so the first option isn’t useable, but the second option there looks interesting for future reference.
What I ended up doing was a function that takes a string and just escapes any of the special search operators, so that “10668…” ended up as “10668...” and when that’s used in a find() it works perfectly.