5 Replies Latest reply on Jan 17, 2014 8:36 AM by erolst

    How to write a script to do automate summary report

    liyangao

      We have some progress report for our study. We need to do a series of find to get the summary statistics for the report every two month. Is it possible to write a script to automate the finds and capture the result and display it on the report so that we do not have to repeat all the finds every time.

       

      Basically we need the summary table for the participants enrolled during the designated two month period, such as age groups, race, gender and enthnicity, etc.

       

      Any suggestion or ideas are appreciated!

      Liyan

        • 1. Re: How to write a script to do automate summary report
          Mike_Mitchell

          Liyan -

           

          Yes, you can certainly script a Find. The correct script step is Perform Find. You can store the Find requests in that script step and then have the found set returned to the appropriate layout.

           

          I suggest you use Set Error Capture to trap the case if no records are found and take appropriate action if that is the case.

           

          HTH

           

          Mike

          • 2. Re: How to write a script to do automate summary report
            liyangao

            Mike,

            Thank you very much for your prompt response. I have many questions abouth the details in scripting a find.  The time priod for the report will be defined as start date and end date. Does the perform find allow for parameter variables in criteria so that the visit date is between the start date and the end date?   After the perform find, how do I capture the number of records satisfying the find requirement, is Get(FoundCount) the function to use?

            Thank you very much for your pointers.

            Liyan

            • 3. Re: How to write a script to do automate summary report
              Mike_Mitchell

              Liyan -

               

              If you need to insert a parameter into a scripted Find, there are a couple of ways to do it:

               

              1) You can set a variable equal to the parameter, then use the variable in the stored Find request.

              2) You can use a series of steps like this:

               

              Set Variable

              Enter Find Mode

              Set Field

              Set Error Capture

              Perform Find

              Set Error Capture

              If

                { do stuff appropriate if there are no records }

              Else

                { do stuff appropriate for the found set }

              End If

               

              To your second question ... see above.   

               

              Mike

              1 of 1 people found this helpful
              • 4. Re: How to write a script to do automate summary report
                liyangao

                Hey, Mike,

                 

                Thank you for your answers.

                 

                I tried two ways:

                 

                1. Define summary fields in the table. When I do a find on the table manually, 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 follwoing your advice 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

                • 5. Re: How to write a script to do automate summary report
                  erolst

                  You shouldn't spread the same question over several threads; it's hard to keep track if someone already delivered a good/correct anwer.

                   

                  https://fmdev.filemaker.com/message/136317