One of the key limitations to using ExecuteSQL is the fact that the results are all crammed into a single field. With careful use of tab characters as field separators and returns as record separators, you can get a tabular display and use the Inspector to set appropriate tab stops to manage different width fields, but the result is strictly read only and you can't do much with data formatting either.
You may want to use ExecuteSQL to generate a return separated list of IDs to use in a match field to TimeCards so that you can then display this data in a portal or even use Go to Related Records to pull up the records in a found set for use with a list view.
They real issue is sorting... I have a script that performs a find.. and then sorts the data accordingly..
The find goes fast.. and the sort is taking forever.. and growing at hundreds of records a day..
Do know of a better way to speed up sorting?
Does the sorting specify a field that is an unstored calculation or that is from a related table?
Sorry for the delay.. Yes it is from a related table...
I have also tried a calc field but same results.. I also tried to use the Lookup and pull the data in but this only seems to work on new records only..
Sorting on a field from a related table or an unstored calculation that references the same data from the related table will be much slower than an indexed field in the layout's table referenced via the layout's specified table occurrence. Doing that sort on a large number of records on an iOS device with it's slower CPU and limited memory will make this even more noticeable.
Physically copying the data into the layout's table is an option that can work, but as you have noted, the challenge then is to be able to keep that field properly updated in every record of the table. Scripts--especially trigger performed scripts--can do that, but you have to nail down every possible way that a user might modify data in a way that requires field to update to make sure that the correct update then takes place--which can be a far from simple undertaking.
Thanks Phil- I was pretty sure that's what the answer was going to be but went ahead and asked anyway.. :)