6 Replies Latest reply on Mar 19, 2011 10:46 AM by LeoB

    Get found count of records by year

    LeoB

      Title

      Get found count of records by year

      Post

      The solution I'm working on has a main menu that takes the user to various types of records, reports, search layouts, etc.  This is to run without any FM toolbars, so all movements are scripted.

      The solution has two main types of records (Activities and Issues).  There are scripted links to take the user to All Years, Current Year, and Previous Year.  Those all work fine.

      However, next to the scripted links I am trying to put in a number field that shows how many records are in each category.  The only way I could figure out how to do this was to run a series of scripts on the main menu layout load that performed finds for each category, wrote the found count to a global field, and then the global field is displayed on the main menu.  Probably not the most elegant method, but it worked.

      My problem is that in doing the find, I could not figure out how to get it to find records in the current year without specifying the year, something like */*/2010.  Thus, every year on 1 Jan I would have to modify the find to change the calendar years.

      I know there has to be a more elegant way to do this, but I just cannot figure it out.

      Any guidance to put me in the right direction would be appreciate.

      Thanks.

        • 1. Re: Get found count of records by year
          philmodjunk

          You can use Year ( Get ( currentdate ) ) to get the year of the current date.

          You can also use a summary field and some filtered portals to compute these counts without using a script:

          This method requires FileMaker 11. For other versions, there's a different method that also works, but requires a bunch of relationships.

          Define a summary field in your Activities table as the "count of" some field that is never empty such as a serial number field or your date field.

          Define this relationship so that any record in Main matches to all records in the activities table:

          MainMenu::Anyfield X Activities::AnyField

          For each total, you'll put a one line portal on your layout (you can hide the portal borders by specifing 0 width borders ) and put the summary field in this portal row.

          For All records you are done. This portal will report the count of all records in the Activities table
          For Last year's records add a portal filter defined as: Year ( Activities::DateField ) = ( Year ( Get ( CurrentDate ) ) - 1 )
          For This year's records use:  Year ( Activities::DateField ) = Year ( Get ( CurrentDate ) )

          • 2. Re: Get found count of records by year
            LeoB

            Phil:

            You can use Year ( Get ( currentdate ) ) to get the year of the current date.


            Where would this go?  I found a reference to this when I searched the forum, but I cannot figure out how to use it in the Find request.

            In the "Specify Find Requests" I have:

            Find Records                Activity::EndDate: [*/*/2011]

            That gives me the number of records for that year, which is then written to a global field and displayed on the layout.  I tried typing in the Year ( Get ( current date )), but it says that it must be a number.

            Sorry to be so obtuse about this.

            Thanks.

            • 3. Re: Get found count of records by year
              philmodjunk

              You could assign Year ( get ( CurrentDate ) ) to a variable and then use the variable in your restored find request, but I prefer this method:

              Enter Find Mode[] //clear pause check box
              Set Field [YourTable::YourDateField ; Year ( get ( CurrentDate ) ) ]
              Set Error capture [on]
              Perform Find[]

              The above script shows the criteria used in the find without my having to open up the Perform Find dialog.

              • 4. Re: Get found count of records by year
                LeoB

                Phil:

                Worked on the above script for both current year and previous year, but it does not work for me.

                I am searching records in a field called EndDate, which contains the date the activity ended.  Then I am getting the found count and writing it to a global field in another table (which the summary layout is based on) for display on the summary layout.

                If I set the global field with the current year, that's only what it displays - 2011.

                When I tried: Set Field [Activity::EndDate ; Year (Get ( CurrentDate ) ) ], nothing changed.

                In my limited knowledge of FM, I don't quite see what setting the field with the current year does as it still has nothing to search.  I've populated the Activity table with about 70 test records spanning four years.

                I also don't understand the last Perform Find[] step with no criteria in it.

                Sorry for being a pain.  When I just use the original scripts after changing the dates, it all works fine.  It would be a piece of cake if the find criteria incuded an operator that allowed Year ( Get ( CurrentDate ) ) and be able to use the - 1 and - 2, etc., after it.

                I just may have to do manual changes to the scripts on the first of every year.

                Thanks.

                • 5. Re: Get found count of records by year
                  philmodjunk

                  Note that the script first enters find mode. While in find mode, set field steps enter criteria into a find request rather than modifying data in an actual record. Then Perform find[] kicks off the find after the crteria is entered.

                  There is no global field in my example script.

                  You can also count records with the count function to count records and not need a script if you can set up a relationship to match to the desired set of records.

                  • 6. Re: Get found count of records by year
                    LeoB

                    Phil:

                    Had to be away for a few days and started in this morning on your advice.

                    I only have one word for your advice and explanation -- WHOO HOO!  (OK, maybe that's two words.)  It worked after I fixed a couple of other things I messed up (likr putting the - 1 in the wrong place.

                    Thanks (again).