What is the relationship of the portal you're filtering? A portal filter works on top of a relationship, so unless you have a Cartesian relationship, the result set is determined by relationship predicate(s) AND portal filter predicate(s).
An SQL query is always performed against all table records.
(Which of course would result in the portal filter returning a smaller result set, so it's not clear where your problem lies … still, something to be aware of.)
Wenn (MusterAnzahl ( Start | Firmenliste::cal_Firma_und_Firmenzusatz ; Start | Firmenliste::gString_Firmensuche ) > 0 ; 1 ; 0)
can simply be expressed as
MusterAnzahl ( Start | Firmenliste::cal_Firma_und_Firmenzusatz ; Start | Firmenliste::gString_Firmensuche )
Your calculation effectively says
If ( True ; True ; False )
Thanks for hinting to a shorter expression!
And: yes, it is a Cartesian relatinship that would show all records but the portal filter uses a search string (global field "gString_Firmensuche") to bring the list down.
As this is not very efficient (as all data is loaded but just shown or not, as I understood it) I want to change my searches to the eSQL "pre-search", putting IDs of the result records in another global field and link this the data table to show headlines...
But as I mentioned - the eSQL does show no or less records than the portal filter...
Totally forgot: LIKE is case-sensitive, PatternCount() isn't.
ExecuteSQL ( "
SELECT ID FROM SQL_DT_Firmen
WHERE LOWER ( cal_Firma_und_Firmenzusatz ) LIKE ?
" ; "" ; "" ; "%" & Lower ( SQL_DT_Firmen::gString_Firmensuche ) & "%"
I tried - but unfortunately I still do not get the same amunt or any records. The lowering does not make any difference. Zhe eSQL seems not to care for upper/lower cases anway.
I am really confused. If I do the portal filtering or a Filemaker search on the field in a straight layout with any relationsships and so on - I get e.g. for sthe string "wdr" 3 results, but with the eSQL none at all.
For me it looks like not explanable as I think to do everything right...
Well, stop, it DOES make a difference if I search for "wdr" or "WDR", Now I see the "missing" records. Perfect as a start. Now I will have a lok why your sugestion did not work at my place... Let' see...
Hm, obviously the "WHERE LOWER ( cal_Firma_und_Firmenzusatz )" does not lower the field contents - as the results do not show up. Can it be that this is not implemented correctly in Filemaker (very unprobable...).
No, sorry - my fault (as it was obviously that it must be...). I just used the wrong script. Everything works - and THANKS A LOT! No I can get a peaceful sleep!