12 Replies Latest reply on Mar 4, 2014 11:14 AM by BruceHerbach

    SQL Help

    m.mcdonell

      I have heavily modified the Invoice template that comes with Filemaker 13 Advanced and have a problem with the SQL scripts that open at the beginning. I do not know much about SQL and can likely figure it out with troubleshooting and hours and hours of time but am hoping someone can direct me in the right direction.

      Here is the script that faults, it is a loop that doesn't stop. Any ideas what I should look for as to why the loop doesn't stop?

      Thank you in advanceMachine generated alternative text: pt Name: Execute SQL I Top 5Customers t Set Variable SSQL RE SULT; Value:Le ([ year = Company Dashboard: :Current Year; dedmal = Left (Evaluate ( 1/2); 1); SQL = ExecuteSQL( SELECT a. Company, SuMb. lotalV) as InvoiceTotal FROM ‘CustomersV a ...] t If [SSQL_RESULT =01 t Halt Scipt t End If t Loop t Set Field [Company Dashboard: :Company for Customer Chart; List ( Company Dashboard: :Company for Customer Chart; ParseSQLQuery (SSQLflESULT; ValueCount (Company Dashboard: :Company for Customer Chart) + 1; 0; 1’; T) ... t Set Field [Company Dashboard: :Total for Customer Chart; List ( Company Dashboard: :Total for Customer Chart; ParseSQLQuery ( $SQL..RESULT; ValueCount (Company Dashboard: :Total for Customer Chart) + 1; 1; T; ¶))] t Exit Loop If [ValueCount (Company Dashboard: :Company for Customer Chart) = ValueCount ( SSQL_RESULT)] t End Loop

        • 1. Re: SQL Help
          erolst

          Can't see anything in your screenshot(?).

          • 2. Re: SQL Help
            m.mcdonell

            sorry about thatScreen Shot 2014-03-04 at 9.32.04 AM.png

            • 3. Re: SQL Help
              erolst

              Why don't post the file? It's impossible to read the complete calculation, and of course one cannot copy and tweak it …

              • 4. Re: SQL Help
                m.mcdonell

                Since the file is fully in use and populated, it is too large. 57 MB. I did not have this problem during the beta testing, it only occured recently. The scripts have not changed but initially I used a received date rather than the defaulted invoice date that the system came with.

                Upon thinking this was the problem, I corrected it and now seem to have the error.

                • 5. Re: SQL Help
                  erolst

                  m.mcdonell wrote:

                  Since the file is fully in use and populated, it is too large. 57 MB

                  Then just copy the calculation from the Set Variable step and post it here. Barring that …

                   

                  1. Check that your field name doesn't use invalid SQL characters; if so, escape the field name (like in the sample code).

                   

                  2. Show and check the actual result of $SQL_RESULT in a Custom Dialog, or better: take the calculation, go to the same context the script line is performed in and test and tweak it in the Data Viewer.

                  • 6. Re: SQL Help
                    wimdecorte

                    copy and paste the calc for the ExecuteSQL and put it in the data viewer and inspect the result.  See if comes back with "?" which typically means a syntax error in the SQL query itself.

                    • 7. Re: SQL Help
                      m.mcdonell

                      Let          (

                                      [

                                      year = Company Dashboard::Current Year ;

                                      decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;

                                      SQL = ExecuteSQL            (

                                                                                                      "

                                                                                                      SELECT a.\"Company\", SUM(b.\"Total\") as InvoiceTotal

                                                                                                      FROM \"Customers\" a

                                                                                                      LEFT JOIN \"Invoices\" b ON a.\"CUSTOMER ID MATCH FIELD\" = b.\"CUSTOMER ID MATCH FIELD\"

                                                                                                      WHERE b.\"Year\" = ?

                                                                                                      GROUP BY a.\"Company\"

                                                                                                      ORDER BY InvoiceTotal DESC FETCH FIRST 5 ROWS ONLY

                                                                                                      "

                                                                                                      ; "|" ; ¶ ;

                                                                                                      year  

                                                                                                      )

                                      ] ;

                                     

                                      Substitute ( SQL ; "." ; decimal )

                       

                                      )

                      • 8. Re: SQL Help
                        LSNOVER

                        You really just have to break out the pieces of the calc and try to work through it.  Takes some trial and error.  I wish there was an easier way.

                        • 9. Re: SQL Help
                          erolst

                          The only thing that comes to mind without actually seeing your file is that the year field in invoices is calculated from a date field; make sure it is using the correct date field (especially after the changes back and fro you hinted at).

                           

                          Also, if you have Advanced, use the Data Viewer; you can e.g. modify the calculation like so …

                           

                          Let ( [

                          year = Company Dashboard::Current Year ;

                          decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;

                          ~sql =

                          "

                          SELECT a.\"Company\", SUM(b.\"Total\") as InvoiceTotal

                          FROM \"Customers\" a

                          LEFT JOIN \"Invoices\" b ON a.\"CUSTOMER ID MATCH FIELD\" = b.\"CUSTOMER ID MATCH FIELD\"

                          WHERE b.\"Year\" = ?

                          GROUP BY a.\"Company\"

                          ORDER BY InvoiceTotal DESC FETCH FIRST 5 ROWS ONLY

                          " ;

                          SQL = ExecuteSQL ( ~sql ; "|" ; ¶ ; year )

                          ] ;

                          ~sql // Substitute ( SQL ; "." ; decimal ) ; comment back in when it works

                          )

                           

                          i.e. add an intermediate variable and use the comment syntax to be able to see and inspect the actual SQL query string, or its result.

                          • 10. Re: SQL Help
                            wimdecorte

                            m.mcdonell wrote:

                             

                                                                                                            WHERE b.\"Year\" = ?

                                                                                                           

                             

                            Pretty sure that "Year" is a SQL reserved keyworkd so you should not use it as a field name.

                            (it's also the name of a FM function, try not to use field names anything that is also a function)

                            • 11. Re: SQL Help
                              m.mcdonell

                              the entire formula is a default from filemakers template 'invoice'. I could edit the year info but figured it was alright since it came on the template.

                              Should it be adjusted or leave it under those circumstances?

                              • 12. Re: SQL Help
                                BruceHerbach

                                The FMS developers of the script did not abstract the field names used in the SQL statements. So if you changed table occurrence names or field names the SQL will break.

                                 

                                It shouldn't be to hard to update these if you did.  Another approach is to get Seedcode's Sqlexplorere and recreate the SQL with that tool. I like to use the abstracted versions of the code it provides. It works well and takes the worry away that I'll break something down the road with a rename.