1 2 Previous Next 26 Replies Latest reply on Jan 13, 2012 9:39 AM by Stephen Huston

    PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS

    bess

      Dear FileMaker Developer Community,

       

      I am hoping that someone out there can help me with a script problem. I've done a lot of work with a much older version of FileMaker Pro, but I'm new to scripts and I am doing something wrong with scripts to produce Reports.

       

       

      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 2 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. 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

      Bess

        • 1. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
          rhlilienkamp

          See my comments in your scripts

           

          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 [ ]    Why?
          • Perform Find [ ]  Why?  You haven't anything to find
          • Unsort Records [ ]  Why?  You are going to sort latter
          • Go to Record/Request/Page [specify First]  Why?
          • Sort Records [Restore; no dialog]  [specify Sort Order Client_Referral_Source (ascending), Client_MF (ascending), Client_Year (ascending), Client_Number (ascending)]
          • Enter Preview Mode [ ]

           

          It looks like to want to find a set of records but you don't tell FM what to look for.  Maybe you want a show all records step.

           

           

           

          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 [ ]  Why?  You preform a find later
          • Unsort Records [ ] Why?
          • Go to Record/Request/Page [specify First]  Why?
          • 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 [ ]
          • 2. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
            bess

            Dear rhlilienkamp

             

            Thank you for looking at this. 

             

            The problem seems to be that if I put in "Show all records" I am shown the PREVIOUSLY FOUND SET SORTED in that set's sort order (whatever that may be), not all the records. 

             

            That's why I was trying to get it to find everything and to unsort.

             

            How would you write the script to get the whole set of records (& not a previous found set)?  What mode should I be in?

             

            Any help from anyone would be very much appreciated!

            Thank you

            Bess

            • 3. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
              fmchris

              It depends on whether your Find set is to be done manually first, or you want the find as part of the scripting.

               

              Either way, you can perform multiple scripts, some of which can be embedded in other scripts (a script step may be "Perform [named script]").

               

              In your case, I might have a script that first Finds All the records ("Show All records"), then Finds the set you want, then within that script perform an Unsort step, (or Sort if that's what you actually want), then goes into Preview mode. You don't actually need the Go to Record steps if you've already found them and sorted them.

               

              If you have two such scripts - one for each report - and each begins with the Find All, then the actual Find, you should get the results you need.

              • 4. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                rhlilienkamp

                I would do it like this:

                • Enter Browse Mode [ ]
                • Go to Layout ["Report Referral Source Totals" (CCS Clients 1)]
                • Show All Records

                 

                From FM Help:

                "Use this script step to redisplay all the records after working with a found set.

                Use Show All Records in Browse mode or Preview mode. If you perform this script step in Find mode or Layout mode, FileMaker Pro switches to Browse mode after the records have been found."

                 

                If you want to sort do it after the Show All Records.

                 

                Ralph

                1 of 1 people found this helpful
                • 5. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                  Stephen Huston

                  Hi Bess,

                   

                  You definitely have steps in your scripts which, as others have said above, are unnecessary or redundant, or could even leave you where you really don't want to be. For example:

                   

                  • Unsorting or sorting when you intend to sort again later is a wasted effort unless you are trying to capture some value from a record to use at another point in the script. You don't appear to use the resulting sort until the final sort.
                  • Show All Records is similarly wasterd effort unless you are capturing a value before continuing the script, or using it as a really awkward way to force the file into browse mode. You don't appear to need it for anything here.
                  • Perform Find without a restored find request will not return anything predictable, but can produce an error. You don't appear to be capturing errors and handling them, (i.e.  if the find request finds no records?).

                   

                  Let's look at one of your scripts in detail again -- for the report "Referral Source 2006":

                  • 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 [ ]

                   

                  The first 5 steps are all unnecessary if you are going to perform the last 3 steps as written.

                  • The final Perform Find [Restore} will change your found set and remove any sorting imposed earlier.
                  • The final Sort will impose a new order on the newly-found set
                  • Enter Preview Mode will show you the report of the restored Find results in the order of the final sort

                   

                  If you are not getting what you want from just the final 3 steps, there is probably something wrong with your saved Perform Find [Restore] request within that script step.

                   

                  When programming finds, remember that "less is more" -- the more concise the single piece of info you are searching for in the field, the better the chance of a match.

                   

                  For instance, if the unique piece of data you are searching for is the pattern "06" you should probably store only the search string

                  *06

                  to maximize the chance of finding all possible matches.

                   

                  This is especially important if your  field has data entry done via check boxes, because the order in which multiple check boxes are ticked will result in different field contents for records with the same boxes checked but checked in a different order!

                   

                  For example: If you check boxes options are A, B, and C, you might get these results depending on the order in which the boxes are ticked:

                  • A  B  C
                  • A  C  B
                  • B  A  C
                  • B  C  A
                  • C  A  B
                  • C  B  A
                  • A  B
                  • A  C
                  • A
                  • B  A
                  • B  C
                  • B
                  • C  A
                  • C  B
                  • C

                  If what you want is any and all records which include any "A", your restored find request should be simplified as:  *A

                   

                  Try simplifying the script, including the Restored Find text string (the asterisk enables it to find result even if there are other characters before those which follow the asterisk).

                   

                  StephenHuston

                  1 of 1 people found this helpful
                  • 6. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                    bess

                    Dear Ralph

                     

                    Many thanks for your reply.  I have simplified the scripts & now have he following.

                     

                    For the Report "Referral Source Totals" I am using a script which now reads

                     

                  • Go to Layout ["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

                     

                    For the Report

                    "Referral Source 2006" I am usng a script which now reads

                     

                    • Go to Layout ["Referral Source 2006" (CCS Clients 1)]
                    • Perform Find [Restore] [specify ACTION Find Records CRITERIA  CCS Clients 1::Client_Number:[=##/06] ]
                    • Sort Records [Restore; no dialog] [specifySort Order Client_Referral_Source (ascending), Client_MF (ascending)]
                    • Enter Preview Mode

                     

                     

                     

                    If I use the Referral Source Totals Report first, it gives me the correct 34 records, correctly sorted.  If I then go to the Referral Source 2006 Report, it gives me the same 34 records (which is an incorrect result).

                    Conversely, if I  use the Referral Source 2006 Report first, it gives me the correct 25 records, correctly sorted. If I then go to the Referral Source Total Report, it gives me te same 25  records (which is an iicorrect result).

                     

                    I am feeling very stupid - I must be doing something wrong, but what????

                     

                    Any more help welcomed

                    With best wishes

                    Bess

                    • 7. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                      bess

                      Dear Stephen & Ralph & the other gentleman who was kind enough to reply

                       

                      I am having a lot of problems posting; my machine keeps flashing up a message to say I need to post again, then there seem to be multiple messages from me, then when I tried to

                       

                      delete the duplicates I may have gone too far.  Please could you keep helping me nonetheless!

                       

                      I have simplified the scripts and am now using the following:-

                       

                      Script for

                       

                      Report for "Referral Source Totals"

                      • 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_Number (ascendng)]
                      • Enter Preview Mode

                       

                      Script for

                       

                      Report for "Referral Source 2006"

                      • Go to Layout ["Report Referral Source 2006" (CCS Clients 1)]
                      • Perform 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

                       

                      If I use Referral Source Totals Report first, it gives me the correct 34 records correctly sorted.  If I then go the the Referral Source 2006 Report, it gives me the same 34 records (an incorrect result).

                      Conversely, if I go to the Referral Source 2006 report first,  it gives me the correct 25 records correctly sorted. If I then go to the Referral Source Totals Report, it produces the same 25 records (an incorrect result).

                       

                      I am feeling very stupid here -I must still be dong something wrong - please help!

                      With best wishes

                      Bess

                      • 8. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                        bess

                        Dear Stephen & Ralph & the other gentleman who was kind enough to reply

                         

                        I am having a lot of problems posting; my machine keeps flashing up a message to say I need to post again, then there seem to be multiple messages from me, then when I tried to

                         

                        delete the duplicates I may have gone too far.  Please could you keep helping me nonetheless!

                         

                        I have simplified the scripts and am now using the following:-

                         

                        Script for

                         

                        Report for "Referral Source Totals"

                        • 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_Number (ascendng)]
                        • Enter Preview Mode

                         

                        Script for

                         

                        Report for "Referral Source 2006"

                        • Go to Layout ["Report Referral Source 2006" (CCS Clients 1)]
                        • Perform 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

                         

                        If I use Referral Source Totals Report first, it gives me the correct 34 records correctly sorted.  If I then go the the Referral Source 2006 Report, it gives me the same 34 records (an incorrect result).

                        Conversely, if I go to the Referral Source 2006 report first,  it gives me the correct 25 records correctly sorted. If I then go to the Referral Source Totals Report, it produces the same 25 records (an incorrect result).

                         

                        I am feeling very stupid here -I must still be dong something wrong - please help!

                        With best wishes

                        Bess

                        PS I am getting the Message "An error occurred while trying to submit your post.  PLease try again."  I hope his doesn't result in multiple posts - apologies in adance if it does ...

                        • 9. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                          bess

                          Dear Stephen & Ralph & the other gentleman who was kind enough to reply

                           

                          I am having a lot of problems posting; my machine keeps flashing up a message to say I need to post again, then there seem to be multiple messages from me, then when I tried to

                           

                          delete the duplicates I may have gone too far.  Please could you keep helping me nonetheless!

                           

                          I have simplified the scripts and am now using the following:-

                           

                          Script for

                           

                          Report for "Referral Source Totals"

                          • 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_Number (ascendng)]
                          • Enter Preview Mode

                           

                          Script for

                           

                          Report for "Referral Source 2006"

                          • Go to Layout ["Report Referral Source 2006" (CCS Clients 1)]
                          • Perform 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

                           

                          If I use Referral Source Totals Report first, it gives me the correct 34 records correctly sorted.  If I then go the the Referral Source 2006 Report, it gives me the same 34 records (an incorrect result).

                          Conversely, if I go to the Referral Source 2006 report first,  it gives me the correct 25 records correctly sorted. If I then go to the Referral Source Totals Report, it produces the same 25 records (an incorrect result).

                           

                          I am feeling very stupid here -I must still be dong something wrong - please help!

                          With best wishes

                          Bess

                          PS I am getting the Message "An error occurred while trying to submit your post.  Please try again."  I hope this doesn't result in multiple posts - apologies in advance if it does ...

                          • 10. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                            bess

                            Dear Stephen & Ralph & the other gentleman who was kind enough to reply

                             

                            I am having a lot of problems posting; my machine keeps flashing up a message to say I need to post again, then there seem to be multiple messages from me, then when I tried to

                             

                            delete the duplicates I may have gone too far.  Please could you keep helping me nonetheless!

                             

                            I have simplified the scripts and am now using the following:-

                             

                            Script for

                             

                            Report for "Referral Source Totals"

                            • 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_Number (ascendng)]
                            • Enter Preview Mode

                             

                            Script for

                             

                            Report for "Referral Source 2006"

                            • Go to Layout ["Report Referral Source 2006" (CCS Clients 1)]
                            • Perform 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

                             

                            If I use Referral Source Totals Report first, it gives me the correct 34 records correctly sorted.  If I then go the the Referral Source 2006 Report, it gives me the same 34 records (an incorrect result).

                            Conversely, if I go to the Referral Source 2006 report first,  it gives me the correct 25 records correctly sorted. If I then go to the Referral Source Totals Report, it produces the same 25 records (an incorrect result).

                             

                            I am feeling very stupid here -I must still be dong something wrong - please help!

                            With best wishes

                            Bess

                            PS I am getting the Message "An error occurred while trying to submit your post.  Please try again."  I hope this doesn't result in multiple posts - apologies in advance if it does ...

                            • 11. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                              bess

                              Dear Stephen & Ralph & the other gentleman who was kind enough to reply

                               

                              I am having a lot of problems posting; my machine keeps flashing up a message to say I need to post again, then there seem to be multiple messages from me, then when I tried to

                               

                              delete the duplicates I may have gone too far.  Please could you keep helping me nonetheless!

                               

                              I have simplified the scripts and am now using the following:-

                               

                              Script for

                               

                              Report for "Referral Source Totals"

                              • 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_Number (ascendng)]
                              • Enter Preview Mode

                               

                              Script for

                               

                              Report for "Referral Source 2006"

                              • Go to Layout ["Report Referral Source 2006" (CCS Clients 1)]
                              • Perform 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

                               

                              If I use Referral Source Totals Report first, it gives me the correct 34 records correctly sorted.  If I then go the the Referral Source 2006 Report, it gives me the same 34 records (an incorrect result).

                              Conversely, if I go to the Referral Source 2006 report first,  it gives me the correct 25 records correctly sorted. If I then go to the Referral Source Totals Report, it produces the same 25 records (an incorrect result).

                               

                              I am feeling very stupid here -I must still be dong something wrong - please help!

                              With best wishes

                              Bess

                              PS I am getting the Message "An error occurred while trying to submit your post.  Please try again."  I hope this doesn't result in multiple posts - apologies in advance if it does ...

                              • 12. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                                rhlilienkamp

                                Bess,

                                Are you using the # as a replacement for a digit or is that your client identifier?   I don't understand why this is doing what you are reporting.  What version of FM are using?

                                 

                                When you switch layout is this by running the script or are you manually selecting them?

                                • 13. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                                  bess

                                  Dear Ralph & Stephen

                                   

                                  This comes with a big THANK YOU to you & everyone out there for all your help with my 2 problem scripts -  or ex-problem scripts as they are now working fine!

                                   

                                  For the "Report Referral Source Totals" I am using the following script:-

                                  • 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 Sorce 2006" I am using the following script which goes:-

                                  • Go to Layout ["Report Referral Source 2006" (CCS Clients 1)]
                                  • Perform Find [Restore] {specify ACTION Find Records CRITERIA CCS Clients 1::Client_Number[=##/06] ]
                                  • Sort Recods [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 woud be nice to think I'd never have another problem, but unrealistic, so if/when those problems arise, I shall certainly ask the Forum again

                                   

                                  With best wishes

                                  Bess

                                  • 14. Re: PROBLEMS WITH WRITING SCRIPTS TO PRODUCE RELIABLE REPORTS
                                    bess

                                    Dear Ralph & Stephen

                                     

                                    This comes with a big THANK YOU to you & everyone out there for all your help with my 2 problem scripts -  or ex-problem scripts as they are now working fine!

                                     

                                    For the "Report Referral Source Totals" I am using the following script:-

                                    • 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 Sorce 2006" I am using the following script which goes:-

                                    • Go to Layout ["Report Referral Source 2006" (CCS Clients 1)]
                                    • Perform Find [Restore] {specify ACTION Find Records CRITERIA CCS Clients 1::Client_Number[=##/06] ]
                                    • Sort Recods [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 woud be nice to think I'd never have another problem, but unrealistic, so if/when those problems arise, I shall certainly ask the Forum again

                                     

                                    With best wishes

                                    Bess

                                    1 2 Previous Next