7 Replies Latest reply on Nov 26, 2015 7:09 AM by dtcgnet

    Dear FMP Experts and Fans,

    lohonyaimm

      I am very new in the world of FMP 14, as You will see it below....

       

      I've saved the Starter Solution ( or template ) "Invoices" within FMP 14 under a new file name:  It was working....

       

      Soon, I have changed successful the name of two Tables, (Company dashboard and Invoices) and the new file got into an endless loop,

      it does not allow either to close the file or to exit FMP.  

       

      After switching off / turning on my desktop computer and opening FMP again, the recovering  of the renamed file could not help,

      also the recovered file remained in loop, without any error message during recovery.

       

      I have tried a similar File-name and Table-names changing procedure with the templates “Inventory” and “Resource Scheduling”,

      both of them worked under the new File-name and new Table-names perfect.

       

      Has anybody tried a similar renaming of the template “Invoices” under FMP 14 ?

       

      Please, advice. (Enclosed the renamed "Invoices")


      Regards,
      Miklós M. Lohonyai
      Hungary

        • 1. Re: Dear FMP Experts and Fans,
          RickWhitelaw

          I would check to see if there is a script running on Last Window Closed.

          • 2. Re: Dear FMP Experts and Fans,
            dtcgnet

            There is a trigger firing OnFirstWindowOpen. Then OnEnter Dashboard. Then an "ExecuteSQL | Current Year Invoices" script fires, and that's where you're having a problem. In that ExecuteSQL script, there is a loop. That loop gets entered and never exited because:

             

            "ValueCount ( Számlakibocsátó::Month Name for Invoice Chart ) = ValueCount ( $SQL_RESULT )"

             

            always fails because $SQL_RESULT is "?" and Számlakibocsátó::Month Name for Invoice Chart is empty.
            (? isn't the same as empty)

             

            Modify the script step on Line 2 from:

            $SQL_RESULT = 0

             

            to:

            $SQL_RESULT = 0 or $SQL_RESULT = "?"

             

            That will halt the script and you can use your database.

             

            Your ExecuteSQL statement in the script (shown below) is not evaluating as it should, which is causing the "?". There are better experts on SQL than me, so hopefully they'll see a flaw in the following:

            Let(
            [
            year = Számlakibocsátó::Current Year ;
            decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;
            SQL = ExecuteSQL(
            "
            SELECT a.\"Month\", a.\"Month Name\", SUM(a.\"Total\")
            FROM \"Invoices\" a
            WHERE a.\"Year\" = ?
            GROUP BY a.\"Month\", a.\"Month Name\"
            "
            ; "|" ; ¶  ;
            year
            )
            ] ;

             

             

            Substitute ( SQL ; "." ; decimal )

             

             

            )
            • 3. Re: Dear FMP Experts and Fans,
              dtcgnet

              I realized you may not have a debugger, so you may not be able to halt the loop. Use the attached version of your database. I've modified ONLY the step as I've described above.

              • 4. Re: Dear FMP Experts and Fans,
                lohonyaimm

                Thank You very much, it seems to be working.

                To avoid similar bugs, I suggest to change the template "Invoices" too, according to Your advice....

                regards

                Miklós

                • 5. Re: Dear FMP Experts and Fans,
                  dtcgnet

                  I took a deep look at that ExecuteSQL statement, and put together the rest of your items.

                   

                  I neglected to pay attention to when you mentioned you changed the name of the Invoices table.

                   

                  I've bolded the place you need to change. Replace it with whatever you changed the name of the Invoices table to.

                  Let(
                  [
                  year = Számlakibocsátó::Current Year ;
                  decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;
                  SQL = ExecuteSQL(
                  "
                  SELECT a.\"Month\", a.\"Month Name\", SUM(a.\"Total\")
                  FROM \"Invoices\" a
                  WHERE a.\"Year\" = ?
                  GROUP BY a.\"Month\", a.\"Month Name\"
                  "
                  ; "|" ; ¶  ;
                  year
                  )
                  ] ;

                   

                   

                  Substitute ( SQL ; "." ; decimal )

                  )

                  Just changes "Invoices". Be aware that wherever that is hard-coded, you'll need to fix.

                  • 6. Re: Dear FMP Experts and Fans,
                    lohonyaimm

                    Many Thanks again,

                     

                     

                     

                    The Chart is working now.

                     

                     

                     

                    Unfortunately I am not familiar with SQL/ExecuteSQL,  but: Is it not possible to give  „Invices” (or anything else) as a calculated Value in the FROM \"Invoices\" a ? 

                     

                     

                     

                    Regards,

                     

                    Miklós

                    • 7. Re: Dear FMP Experts and Fans,
                      dtcgnet

                      Well...you could. You'd need to modify the Let statement.

                       

                      Notice how "year" is a variable which is specifically defined in the Let statement. Same with "decimal" and "SQL". To achieve what you're after, you'd add another variable definition. After the "decimal" line, add:

                       

                           tablename = Table ( Invoices::primarykey )

                       

                      Then where Invoices is bolded, you'd replace Invoices with tablename. I picked ANY field from Invoices, by the way, since all I want is the name of that table. Choose a better variable name than "tablename". I picked that just for illustration. If you did this, then if you ever changed the name of that table, FileMaker would make the appropriate replacements for you. But where Invoices is currently is in quotes, and FileMaker won't help you in those instances. Something to be aware of, something to take advantage of. Good luck!

                       

                       

                      Let(
                      [
                      year = Számlakibocsátó::Current Year ;
                      decimal = Left ( Evaluate ( 1/2 ) ; 1 ) ;
                      SQL = ExecuteSQL(
                      "
                      SELECT a.\"Month\", a.\"Month Name\", SUM(a.\"Total\")
                      FROM \"Invoices\" a
                      WHERE a.\"Year\" = ?
                      GROUP BY a.\"Month\", a.\"Month Name\"
                      "
                      ; "|" ; ¶  ;
                      year
                      )
                      ] ;

                       

                       

                      Substitute ( SQL ; "." ; decimal )

                      )