7 Replies Latest reply on Dec 3, 2013 7:16 AM by adamdavid

    Filtering records by existing month

    adamdavid

      Title

      Filtering records by existing month

      Post

           I have database of records showing working time. So for each day there will be one or more records for multiple users. There will be a lot of records at the end of the month so I want to make list simpler and show just current months records.

           IDEA ON SOLUTION: I want to have drop down list with months so user can select which month to show. In this list there should be just months in which there is some record created.

           So if there is just records for november 2013 and december 2013 there would be just those two months to select in drop-down list.

           What is the best way to achieve this?

        • 1. Re: Filtering records by existing month
          philmcgeehan

               I have a similar set up in my database.

               I made two calculation fields:
          c_MonthYear Date - Day ( Date ) + 1
          c_MonthNAMEYear = MonthName ( Date ) & " " & Year ( Date )
               and a global filter field to select the month:
          g_FilterDate

          Where Date is the name of your field with the date in.

               Then made a Value List using values from fields the table: c_MonthYear & c_MonthNAMEYear

               I then have pop-up menu showing values from my new Value List whis sets the global field g_FilterDate
               and an OnObjectModify Script Trigger

               That sets a variable $FilterDate
               Shows all records
               Performs find on ==$FilterDate
               Then sorts the found set by date.
                

          • 2. Re: Filtering records by existing month
            adamdavid

                 Hi, thanks for your comment. That's the approach I tried to implement before your replied. 

                 But I am stucked now. I added pop-up menu with value list from field c_MonthNAMEYear. It is showing correct values but as I try to select different value it says "This field is not modifiable". I got script trigger on OnObjectModify so script couldn't be started.

                  

            • 3. Re: Filtering records by existing month
              philmcgeehan

                   Make sure the list isn't set to specify the Calculation fields, but the global field.
              The list gets its values from the c_ fields, but, it needs to set a different field because it cannot modify the calculation.

                   If you right click the pop-up menu and then Specify Field, it should be set to the global field g_FilterDate.

              • 4. Re: Filtering records by existing month
                philmodjunk

                     Here are some examples of scripted finds of the type Phil_1986 is recommending: Scripted Find Examples

                • 5. Re: Filtering records by existing month
                  adamdavid

                       Thanks a lot guys I finally managed to make it work!

                       Now I'm struggling with another thing connected to my problem. When user enter layout I wrote a script to show current months records but pop-up menu still remembers last selected value. I understand why, question is how to change global "variable" in my case Global::g_FilterDate. I tried to use Set variable function but it doesn't work.

                  • 6. Re: Filtering records by existing month
                    philmodjunk

                         Global::g_FilterDate is a global FIELD not a global VARIABLE so you would modify its value with Set Field.

                    • 7. Re: Filtering records by existing month
                      adamdavid

                           Yes it's working now. I started using FileMaker week ago and I need to have my first app ready by the end of this week. This forum is really helping me to have it on time, hopefully I will. Thanks for your help.