1 2 Previous Next 17 Replies Latest reply on Aug 10, 2016 11:13 AM by RogerBirch

    Monthly Summery

    RogerBirch

      I want to be able to see monthly totals for a set of records based on when my holiday clients depart. At the moment I have written a script for a button to create a table showing all income for (say) month of May, but the period of May is written within the script, i.e "Depart Date = */05/2016". Which is fine BUT if I want to see the totals for (say) April, I have to go into the script and change the script to read "Depart Date = */04/2016".

      This seems unnecessary work and what I really want to be able to do, is to have a field on my database named "InvRange" that I can insert a search time, i.e "*/04/2016" and the script related to the button, looks at this field and gives me the result in my table.

      So far I have been unable to create part of the script as "Depart Date = InvRange".

      I am attaching a screen shot of the button script and Perform Find script.

      Any suggestions will be gratefully received.

        • 1. Re: Monthly Summery
          electon

          You can use a global field, or two ( one for month, one for year ) on your layout or custom dialog to use in your script.

           

          Fields:

          g_Month, g_Year

           

          Convert these to a variable in your script.

           

          SetVariable [$MonthYear ; "*/" & g_Month & "/" & g_Year]

           

          Use the variable in your search criteria  Depart Date = $MonthYear

          • 2. Re: Monthly Summery
            SteveMartino

            You could make it dynamic by adding a global search field to your layout attached to a value list.  You could use custom values (the 12 months).

            Create an OnObjectModify script trigger and attach it to the field.

            The script steps will be something like:

            Set a variable based on the global field

            Enter find Mode

            Set the Depart Date to the variable

            Perform Find

            Clear the global field

            or

            You could use a Show custom dialog and use an Input field to capture the month, then do the rest with the script.

            • 3. Re: Monthly Summery
              RogerBirch

              Many thanks electron for your response and ideas. As I am not too conversant with this type of scriting, would you be able to expand your suggestion a bit more. Does your suggestion allow me to enter the "month" (presumably as a number 1 - 12) in a field on my datadabase and what would that field be.? Any help gratefully received. Thanks.

              • 4. Re: Monthly Summery
                RogerBirch

                Many thanks SteveMarino for your response and ideas. As mentioned to "electron" I am not too conversant with this type of scriting, would you be able to expand your suggestion a bit more. Once again, does your suggestion allow me to enter the "month" (presumably as a number 1 - 12) in a field on my datadabase and what would that field be.? I attempted your second suggestion "Show custom dialog" but was at a loss as to what I should write into this and how it affected the script.

                In your first suggestion, you say to create an OnObjectModify script triffer and attach it to a field. I am afraid I couldnt find this script trigger and what field should I attach it to?

                Once again, many thanks for any little help.

                • 5. Re: Monthly Summery
                  electon

                  Indeed the purpose of the global fields is so you can enter the search criteria manually.

                  You can define a custom value list for months 1-12 for a dropdown if you like.

                   

                  After you make your selections, convert the combination of month and year in a variable and use the variable in your search. Attach the script to a button on the layout.

                   

                  The criteria in Perform Find [Restore] can be a field but can also be a variable of your own making.

                  • 6. Re: Monthly Summery
                    siplus

                    Here is your help.

                     

                    See attached.

                    • 7. Re: Monthly Summery
                      RogerBirch

                      Thanks siplus, I will try and work with this. May I assume that your script is not visible because of "copyright" or is it not necessary to have scripts? What you have given me could be adapted to my database very well - just a case of getting my head around it.Thanks

                      • 8. Re: Monthly Summery
                        siplus

                        My scripts are not selectable from the Script menu, because them being utility scripts there's no need to execute them by picking an entry from the said menu.

                         

                        By selecting "Manage scripts" from the ditto menu, you can access them via script editor

                        • 9. Re: Monthly Summery
                          RogerBirch

                          OK, many thanks, understood, sort of!!

                          I notice that the scripts are not available on the TopMenu so I looked at File>Manage>Scripts but this script selection is greyed out and not available. Perhaps you are suggesting a different location?

                          • 10. Re: Monthly Summery
                            BruceRobertson

                            Say what?

                            manage scripts.png

                            • 11. Re: Monthly Summery
                              RogerBirch

                              Hi BruceRobertson, thanks for your contribution.I dont recognise that screen shot you have sent me. Maybe I am using a diffrent version of FileMaker where there is a difference. I am using FileMakerPro14 for Windows V.14.0.6

                              • 12. Re: Monthly Summery
                                siplus

                                what happens when you type CTRL- SHIFT - S ?

                                • 13. Re: Monthly Summery
                                  RogerBirch

                                  Yes, tried that but I dont get anything!!

                                  Also, when I go into Edit Layout and click on a field I get a message saying that "This action cannot be performed because this file is not modifiable"!??

                                  • 14. Re: Monthly Summery
                                    siplus

                                    Quit Filemaker, copy the file to your Desktop, open it from there.

                                    1 2 Previous Next