I've done a little of this and I've always just managed the data the way I needed to in FMP. Why not just sort it once it's in FMP?
We have 70 or so Citrix Users, and once the data comes across, they have to wait even longer. Yes, we can do that, but hoping for some solution to getting the data the way the "View" in SQL was set up to send it to us.
Are you using the Actual ODBC driver and making a direct connection to the SQL Server, or some other connection?
Thanks for the response..
Yes, we are using the ODBC driver and making the connection thru to the SQL server.
I think the real question that we are trying to find out is how to alter the sort coming from SQL, without having to go thru the FileMaker sort. That is, we want the data to be sorted somehow, but SQL views don't allow sorting as an option. So, we have been making a key that includes last name and such.... and most of the time it gather the records correctly. However, it does not seem to be consistent. So, we are trying to figure out the rule. The data coming from an SQL database seems to always want to sort by the KEY.
So if the key is
regardless of entry order, it will always return the James first.
Is this the rule, or are we misunderstanding how it works?
We are trying to find out why ocassionally it is wrong, or for portals with time related data, we want it to reverse sort.
Just using the FileMaker sorts once the data is retrieved, just takes too long.
Center for Elders
From your posts, I see you are importing the data into Filemaker. Have you tried ESS and if so does it give the same result??
Please clarify the result is creation order not the sorted order in the view table.
Also I am sure the first thing you checked was to verify that the import was indeed coming from the view table and not the base table. However, if not I would verify this also.
Hope this helps,
tim, James' comment "add a SQL table to our solution, that all the data comes across unsorted" seems like they ARE using ESS. James may need to clarify. If it were using the IMPORT [ ] script step, I'd say make the ORDER BY clause in the SELECT statement.
What happens if you connect directly to the table in question rather than using the SQL view table?
This is definately an issue with Filemaker if you are using ESS. I've tried every trick I know to get the data to come into Filemaker sorted reliably, and have not found any way to do it. There is no facility for specifying a sort order, and most databases will not guarantee a sort order even when querying a view without an explicit sort.
If you use the Import from ODBC source command, then I believe you specify an explicity Order by clause as part of your select and it will be respected. Aside from that you are stuck with doing the sort in the scope of Filemaker and this is very slow with large ESS tables because you don't get the benefit of using the SQL Databases Indexing.
If someone has found a way to make this work with ESS, I would LOVE to hear about it.