1 of 1 people found this helpful
Use the parenthesis:
( a AND b AND c )
( d AND e AND f )
( h AND i )
Sent from miPhone
2 of 2 people found this helpful
4 ORs all have 2 ANDs, then
SalesPersonSort = SalesPerson AND TimeZoneSort = TimeZone AND
SkyColorSort = SkyColor OR
WallColorSort = WallColor OR
FloorColorSort = FloorColor OR
WallShapeSort = WallShape
Awesome. I had a feeling it had to do with paranthasis but wanted to confirm with the genius squad. Thanks so much for the help guys it is much appreciated.
I have been implementing ExecutiveSQL to count and establish my portal relationships using lists. I have noticed a substantial increase in speed and performance. I have read that the speeds should be comparable to native FM but so far this method seems far superior.
Are you replace a portal filter with a list produced by your ExecuteSQL query?
If so, then you are comparing apples to oranges as Relationship level filtering (with your return separated list) is generally more efficient than a portal filter.
Yes I am. My SQLStatement creates a list of ID's with I compare agains my ID in the portal relationship. What used to take almost 10 seconds to update is now almost instantaneous.
I'm then able to sort the portals using a calculation field in the table that determines where the key is in the list. Here's the calc
ValueCount ( Left ( _fkSQLFilter ;Position ( "¶"& _fkSQLFilter & "¶" ; "¶" & _pkLeads & "¶" ; 1 ; 1 ) + Length ( _pkLeads )) )
_fkSQLFilter is obviously the List field that the SQL Statement creates and _pkLeads is the Primary Key.
I then installed a timer that counts the records in the portal every 30 seconds and compares them against the last count. If the count is different it rebuilds the list again to update the portal.
It's impressive how much faster this method is.
As I said before, it's not that ExecuteSQL is fast, it's that a return separated list of values as your match field is much faster than a portal filter expression. With a portal filter expression, you basically have an unstored calculation that has to evaluate once for every related record in order to determine which will be allowed to appear in the portal. Your ExecuteSQL expression on the other hand, only has to evaluate once. (There are a number of other techniques that could be used to create a return separated list of values for the relationship and once they've put that list into the match field, you'd see the same improvement in how fast the portal updates.)
Got it and good to know. That makes sense actually. Thanks for explaining