5 Replies Latest reply on Jan 13, 2012 8:38 AM by bess

    Script for Reports (How to get a new found set for different reports)

    bess

      Title

      Script for Reports (How to get a new found set for different reports)

      Post

       Dear Forum Users

      I have a problem with writing an effective script for a report. 

      In my database, the table which holds the data is called CLIENTS.  I have 2 report problems.  1 report "Referral Source Totals" runs a report on ALL the Clients, the other "Referral Source 2006" runs a report on the Clients of 2006.  Both work but only if I go to the original table (CLIENTS) and click on "Show All (Records)" followed by "Unsort (records)".  I cannot swop from one report to another without going back to the table & carrying this out show/unsort each time; if I fail to do this, the report runs on the previous found set (I think) & produces incorrect information.

      I've done my best to sort this out but failed.  Is there a way of writing a script which wi automate this process?

      I should be very grateful for any help. 

      E M Harvey

        • 1. Re: Script for Reports (How to get a new found set for different reports)
          OwenKlingdon

          There is likely a more elegant solution others will have but in the mean time.  It sounds like you already have 2 scripts set up (one for each report), could you not put the show all records and unsort script steps at the start of your scripts for each report, that way it would not be forgotten and run on the wrong critera?  

          • 2. Re: Script for Reports (How to get a new found set for different reports)
            philmodjunk

            Do you know how to set up a script to perform a find?

            You can use scripted finds or, in some cases, go to related records in scripts to pull up the set of records needed for a report.

            Show All and Unsort should not be necessary to pull up different found sets for your reports.

            • 3. Re: Script for Reports (How to get a new found set for different reports)
              bess

               Dear Owen & Phil

               

              Many thanks for replying.  I've had a lot of experience with an older version of FileMaker Pro, but I'm new to scripts and am feeling pretty stupid right now..

              I have a small database; the major table is called "Clients" (CCS Cients 1). Each client is identified by a number e.g. ##/06 (where 06 = 2006, 12 = 2012, etc).

               

              From the Client information, I want to be able to produce fully automated reports. I am currently looking at 2 reports, both look at HOW the clients heard about us.

               

              One report is called "Referral Source Totals" & should return a result of 34 entries.

              The other is called "Referral Source 2006" & should return a result of 25 entries.

               

              To cut a long story short, at the minute there are 3 major problems:-

              1. if I go from 1 report to the other, in either order, the results will refer to whichever report I looked at first (i.e. "Totals" could return 25 entries, not 34, if I had previously looked at the "2006" report, or vice versa);

              2. If I put in the command “Show All Records”, the report shows me whichever was the previous found se in the previous sort order (hence “Find” = attempt to find all & “Unsort” in an attempt to clear the previous sort order;

              3. sometimes a message flashes up telling me to modify the find???

               

              For the report "Referral Source Totals", I am using a script which goes:

              • Enter Browse Mode [ ]

              • Go to Layout ["Report Referral Source Totals" (CCS Clients 1)]

              • Enter Find Mode [ ]

              • Perform Find [ ]

              • Unsort Records [ ]

              • Go to Record/Request/Page [specify First]

              • Sort Records [Restore; no dialog] [specify Sort Order Client_Referral_Source (ascending), Client_MF (ascending), Client_Year (ascending), Client_Number (ascending)]

              • Enter Preview Mode [ ]

               

              For the report "Referral Source 2006" , I am using the following script:

              • Enter Browse Mode [ ]

              • Go to Layout [Report Referral Source 2006" (CCS Clients 1)]

              • Perform Find [ ]

              • Unsort Records [ ]

              • Go to Record/Request/Page [specify First]

              • Perforn Find [Restore] [specify ACTION Find records CRITERIA CCS Clients 1::Client_Number: [=##/06] ]

              • Sort Records [Restore; no dialog] [specify Sort Order Client_Referral_Source (ascending), Client_MF (ascending)]

              • Enter Preview Mode [ ]

               

               

              Please can someone help! I suspect that I am making a quite basic mistake and hope that someone can tell me (in easy to follow terms!) how to put it right.

              With thanks in advance

              • 4. Re: Script for Reports (How to get a new found set for different reports)
                philmodjunk

                1. if I go from 1 report to the other, in either order, the results will refer to whichever report I looked at first (i.e. "Totals" could return 25 entries, not 34, if I had previously looked at the "2006" report, or vice versa);

                This is expected behavior. Since both layout refer to the same table occurrence, (a "box" in manage | database | relationships), they share the same found set, current record and sort order.

                You can use a new window or a duplicate table occurrence for one of the two reports to have two different found sets from the same table.

                A simpler option is to add navigation buttons for switching layouts. Have these buttons change layout and also perform the needed find to pull up the records appropriate for this report. This can also be done with OnlayoutEnter script triggers if you don't care to add nav buttons to your layouts.

                If I put in the command “Show All Records”, the report shows me whichever was the previous found se in the previous sort order (hence “Find” = attempt to find all & “Unsort” in an attempt to clear the previous sort order;

                Sorry, but you better take a closer look. Show All Records does exactly that, it brings up a found set of all records in your table. Thus, this is a quick way to produce the set of records needed for one of your reports, but not the other.

                3. sometimes a message flashes up telling me to modify the find???

                That's strange, a show all records shouldn't ever produce such a message. Can you report the exact wording of that message?

                When I look at your two scripts, I don't see any Show All Records step in either script, and thus you are probably getting a message that now records were found with the option to cancel or modify the find.

                For your first script, use:

                Go to Layout ["Report Referral Source Totals" (CCS Clients 1)]
                Show All Records
                Go to Record/Request/Page [specify First]
                Sort Records [Restore; no dialog] [specify Sort Order Client_Referral_Source (ascending), Client_MF (ascending), Client_Year (ascending), Client_Number (ascending)]
                Enter Preview Mode [ ]

                For your second:

                Go to Layout [Report Referral Source 2006" (CCS Clients 1)]
                Set Error Capture [on] // this step keeps "no records found" dialog from interrupting the script if no records are found
                Perforn Find [Restore] [specify ACTION Find records CRITERIA CCS Clients 1::Client_Number: [=##/06] ]
                Sort Records [Restore; no dialog] [specify Sort Order Client_Referral_Source (ascending), Client_MF (ascending)]
                Enter Preview Mode [ ]

                Question, Since this is 2012, what is the significance of a layout and script dedicated to just one year, 2006?

                Can't you have one layout, named say... "Yearly Referral Source Totals"? If so, you can also use a single script that uses a year specified by the user prior to running the script to produce this same report, but for any year specified by the user.

                • 5. Re: Script for Reports (How to get a new found set for different reports)
                  bess

                   Dear Owen & Phil

                  This comes with a very big THANK YOU for helping me with these 2 scripts which now are working fine!

                   

                  For the report "Referral Source Totals", I am using a script which goes:

                  • Go to Layout ["Report Referral Source Totals" (CCS Clients 1)]

                  • Show all records

                  • Sort Records [Restore; no dialog] [specify Sort Order Client_Referral_Source (ascending), Client_MF (ascending), Client_Year (ascending), Client_Number (ascending)]

                  • Enter Preview Mode [ ]

                              Script triggers:- OnLayoutEnter Enable in Browse & Find & Preview

                   

                  For the report "Referral Source 2006" , I am using the following script:

                  • Go to Layout [Report Referral Source 2006" (CCS Clients 1)]

                  • Perforn Find [Restore] [specify ACTION Find records CRITERIA CCS Clients 1::Client_Number:[=##/06] ]

                  • Sort Records [Restore; no dialog] [specify Sort Order Client_Referral_Source (ascending), Client_MF (ascending)]

                  • Enter Preview Mode [ ]

                              Script triggers:- OnLayoutEnter Enable in Browse & Find & Preview

                   

                  Again, many, many thanks.  I have been so impressed by the Forum & by your willingness to help.  It would be nice to think that I’d never have another problem, but unrealistic; so if/when those problems arise, I shall certainly ask the Forum again.

                  With best wishes

                  Elizabeth