8 Replies Latest reply on Feb 23, 2012 7:55 AM by LSNOVER

    FileMaker 11 and SQL importing

    jtoland

      Maybe this is a common understanding, but we are finding that when we add a SQL table to our solution, that all the data comes across unsorted, and not based on the view we setup in SQL. IS there any way around this? Or this is common behavior when dealing with SQL.

       

      To get around this we created a concatenated key field using last name/firstname/userID. This seems to make the data com3e across alphabetized, but does not always product expected results.

       

      Any Ideas? Plugins?

       

      James Toland

      Contract FileMaker Software Developer, Information Technology

      Center for Elders' Independence

      510 17th Street, Suite 400

      Oakland, CA 94612

      Phone: (510) 318-7130

      Cell: (510) 432-7470

      mailto:jtoland@cei.elders.org

        • 1. Re: FileMaker 11 and SQL importing
          techt

          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?

          • 2. Re: FileMaker 11 and SQL importing
            jtoland

            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.

             

            Jim

            • 3. Re: FileMaker 11 and SQL importing
              techt

              Are you using the Actual ODBC driver and making a direct connection to the SQL Server, or some other connection?

               

              Tim

              • 4. Re: FileMaker 11 and SQL importing
                jtoland

                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

                 

                ID,Toland,James

                or

                ID,Toland,Scott

                 

                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.

                 

                Jim Toland

                Center for Elders

                • 5. Re: FileMaker 11 and SQL importing
                  timwhisenant

                  Hi Jim,

                   

                  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

                  • 6. Re: FileMaker 11 and SQL importing
                    beverly

                    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.

                     

                    Beverly

                    • 7. Re: FileMaker 11 and SQL importing
                      techt

                      What happens if you connect directly to the table in question rather than using the SQL view table?

                      • 8. Re: FileMaker 11 and SQL importing
                        LSNOVER

                        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.

                         

                        Cheers!

                        Lee