3 Replies Latest reply on Apr 12, 2010 5:32 AM by Phrogz

    AppleScript to get the value of a Summary cell (sum)

    Phrogz

      Title

      AppleScript to get the value of a Summary cell (sum)

      Post

      I have a legacy DB running on FM7 with a single table ("Main"). One of the fields in this table is "Price" (a Number), and another field is "Total Price" (a "Total of" the Price field).

       

      When I use the Total Price field in a Layout with a Trailing Grand Summary section, it shows me what I want: the total value of the Price field for all found records. However, when I use AppleScript to ask for the value of this cell, it consistently gives me a wrong answer:

       

      tellapplication "FileMaker Pro"

         getcell "Total Price" ofdatabase "FooBar"

           --> "212617448.616"

      end tell

       

      This same number is returned regardless of the layout used, whether in Browse or Preview mode, and regardless of what the Total Price cell is actually showing on screen.

       

      This mechanism used to work, under some combination of previous OS X release/FM version. I rather urgently need to get it fixed again. More exactly, I need some automated way to query the Main table to find all records matching four criteria and get the sum of the Price field for the matching ones, and then copy that value into an Excel file. (I need to do this 70 times for each month to be transferred, usually transferring several months at a time every couple of weeks. This is why it needs to be automated.)

       

      Thanks in advance for any help you can give me.

       

      OS X 10.6.3

       

      FileMaker Pro 7.0v2 (local only: not networked, not published to web); willing to upgrade if someone feels rather certain the problem is inherent to FM7 and fixed as of FM11.

       

      Oh, and finally, I will note that I have found a way to get the correct value:

      tellapplication "FileMaker Pro"

         telldatabase "FooBar"

           setltolayout "Bob"

           setmtofirstitemofl

           setntolastitemofm

            --> "$54,696.49"

         endtell

      endtell

       

      However, this value needs a small amount of massaging to turn it into a number (not a showstopper), and does not work when the result of querying for the four criteria returns no records (making it useless).

        • 1. Re: AppleScript to get the value of a Summary cell (sum)
          Phrogz

          FWIW, I've just discovered that the large value always returned is the total value of all records. So I suppose the question here really is: how do I get the summary field accessed through script to respect the found record set?

          • 2. Re: AppleScript to get the value of a Summary cell (sum)
            FentonJones

            You should download this documentation. FileMaker no longer distributes is, and seems to want to hide it for some reason. People have trouble with AppleScript and FileMaker mostly because they do not know how it sees the structure. The documentation tells you how, and has a diagram showing it.

             

            http://fmdl.filemaker.com/MISC/fmp10/fp/apple_events_reference_wwe.zip

             

            A "database" or "table" does not have a found set. A "document" does, a "window" does, and a "layout" does. It is this way within FileMaker also, we just don't really notice, as we seldom deal with the "database" directly; we're always in a window.

             

            cell "Note" oflastrecordoflayout "cl_Notes"

            • 3. Re: AppleScript to get the value of a Summary cell (sum)
              Phrogz
              A "database" or "table" does not have a found set. A "document" does, a "window" does, and a "layout" does.

              Thank you! Simply changing tell database "Foo" to tell document "Foo caused the value of the cell to be limited to the found set, and also allowed me to detect the count of records to determine if any were found or not. In case someone else ever needs something similar, here's code that works to find records based on specific criteria and get the value of a Summary cell based on them:

              tell application "FileMaker Pro"
                tell document "My Filemaker File Name"
                  delete every request
                  tell create new request
                    set cell "Month" to 1
                    set cell "Year" to 2010
                    set cell "Category" to "Stuff"
                  end tell
                  find
                  if (count of records) > 0 then
                    set catTotal to cell "Total Price"
                  else
                    set catTotal to 0
                  end if
                end tell
              end tell