3 Replies Latest reply on Jan 17, 2014 8:33 AM by erolst

    How to do a combination of find and report the total and subtotal in script

    liyangao

      I tried two ways:

       

      1. Define summary fields in the table. When i do a find on the table, the summary field is automatically updated according to the found subset.

       

      2. I generated individual reports with subtotals and grand totals by creating a reprot layout and saved the automatically generated script from filemaker. I set up an overall report layout so that people can enter the start date and the end date for their report. I added buttons to the report so that people can click on it and found their total and subtotal for the fields they are interested in.

       

      My question is how I can write the script to combine the find step and the report with subtotals and grand totals like in the first way? I tried the following script but it does not apply the time filter. The find part and the report of subtotals are not connected somehow.

       

      Set variable[$start_date; value:tablename::startdate]

      set variable[$end_date; value:tablename::enddate]

       

      enter find mode

      set error capture [on]

      perform find

       

      if [get(FoundCount)=0]

      {close window;

      halt script;}

      else

      {enter browse mode

      go to layout ["REPORT- Age"]}

      end

       

       

      Any hint on how to correct the problem?

      Thanks a lot for your help!

      Liyan

        • 1. Re: How to do a combination of find and report the total and subtotal in script
          Mike_Mitchell

          Exactly what part are you having trouble with? The Find, or the summary fields?

          • 2. Re: How to do a combination of find and report the total and subtotal in script
            liyangao

            The summary fields are showing what I want for overall summary without time restriction.

             

            I commented out the report part and tried the script for the Find part. It seems that the script did not perform find as I think it should.

            • 3. Re: How to do a combination of find and report the total and subtotal in script
              erolst

              1. Perform the find on the report layout itself

              2. We cannot see your search conditions; it usually better to use Enter Find Mode / Set Field (or fields, with your search criteria) / Perform Find, so you don't have to open the dialog to see all option.

              3. If tablename_startDate are globals and you want to use them as is, you don't need to put them into variables (unless you need them more than once), since they're accessible from everywhere.

              4. Don't use Halt Script, unless you explicitly want to prevent the script to return to a calling script; use Exit Script

               

              In brief, try …

               

              (New Window??)

              Go to Layout [ REPORT- Age ; someTO ) ]

              Enter Find Mode [ no pause ]

              Set Field [ someTO::date ; tableName::startDate & ".." & tableName::endDate ]

              # other criteria …

              Set Error Capture [ On ]

              Perform Find

              If [ not Get ( FoundCount ) ]

                Close Window (??)

                Exit Script

              End If

              Sort [ as per sub-summary definition )

               

              In general, it's best to show a complete script, incl. dependencies (calling script / sub-scripts).

              You can print a script by pressing Cmd/Ctr-P; in OS X, you can open a preview in … well, Preview, then copy the script text. This version also shows the “invisible” dialog options mentioned above; great for pasting when posting.