What results are you getting when you attempt to use this method?
// not sure if this should be the table name or the TO name
In ExeuteSQL, like any other calculation, you can only refer to a table occurrence name, but you can refer to any table occurrence for a given data source table and you will be able to reference the same data as you are building your own relationships etc.
While I am aware of techniques that use an unstored calculation field that can return the results 1 of several user selectable fields in order to facilitate "on the fly" portal sorting, I don't quite see how this calculation is supposed to facilitate sorting on a portal.
Are you sure that your example doesn't use an unstored calcluation field to completely replace the portal? (An unstored calculation field with ExecuteSQL, sized may rows tall and given a scroll bar will result in something very similar to a read only portal.)
Basically, nothing happens.
The above step is part of a script that "sets a global variable with list of IDs for portal sorting in the order that the records will sort." The variable is $$gIDs. The value is the above ExecuteSQL calc. Based on your comment about referring only to TO names, I changed the "FROM" part of the calc to:
which is the TO name instead of the Table name. Didn't fix anything, though. I put a merge varible of $$gIDs on my layout and it always (and only) shows "?"
Regarding the technique itself, I have never used SQL before so can't comment on that. The sample file works well, though. And it is using a regular portal sorted by the global variable ($$gIDs)
The ? result indicates that you have a syntax error in your SQL query. You may want to experiment with your query using SeedCode's SQL Explorer to debug the syntax until you get the desired results from your query.
I can understand how a return separated list of values will produce a multivalue key that matches to a set of related records, but am not aware of how that will affect the order of the records displayed in the portal. That's intriguing. Time for a little experimenting to see if/how that might work...
Must be missing some detail here. My tests indicate that the order of the values in the multi-key do not affect the order of the related records shown in the portal as I thought was the case.
So I'm still scratching my head and how such an ID list can affect the order displayed in the portal.
Unless by "sort" you really mean "search"?
For what it's worth, the sorting is working now. Turns out I had two errors:
1. A missing space in the ExecuteSQL statement ( ORDER BY" instead of ORDER BY ")
2. Since the portal was using a cartesian join, including the WHERE in the ExecuteSQL statement was causing just 1 record to be returned instead of all of them.
Care to share the link to the other forum where you found this example? I'm curious as what little you've shared here about this method makes no sense for getting a portal to sort in different orders or on different fields in the portal table.
Thanks. "Mr_Vodka" was once a long time poster here in this forum and he really knows his stuff. The calculation field specified as the sort field for the portal that takes the resulting sorted list of ID's and returns the position number of the portal record's ID in that sorted list is the missing part of the process that I was looking for.
It is indeed a quite clever way to use ExecuteSQL() to sort a portal.
I have seen several examples, I like this one the best, but I don't remember what website I downloaded it from. I was researching the same topic a month or so ago. Here is a link to download another example https://www.sugarsync.com/pf/D9559058_78149901_734642
You click next to the field title to select which field to sort on and ascending or descending.
Well, this may not be the correct example with ExecuteSqL(). I have several different example.
This is the correct example
here is a another link talking about ExecuteSql
Thanks, PhilModJunk. Happy that this topic was benificial for you, too. Mr_Vodka's solution is very fast and (if you don't get stuck on the SQL like I did) very easy to implement.
S Chamblee. Thanks for the link. It looks like it downloads the same file as the link on fmforums. (I guess it's unanimous; Mr_Vodka has a great sorting method!)
Well I didn't verify your link. Sorry. I was thinking that was a different example on that website. I download several examples and then lost track where I downloaded them from. Thanks for the information.
S Chamblee - No worries at all. I really appreciate your help in this forum!