4 Replies Latest reply on Oct 18, 2012 4:57 PM by schamblee

    Use drop-down of month names (Jan-Dec) to find records for a report

    ChrisDeith

      Title

      Use drop-down of month names (Jan-Dec) to find records for a report

      Post

           Should be simple, but doesn't appear so?

           1. Start with a value list of the 12 months January ---> December.  Build a dropdown list and use it to select a month. Store as a global value.

           2. Create a value list and fill it with all valid years found in the invoices table.  Build a dropdown list and use it to select a year. Store as a global value.

           Having done the above, use the result of that selection (eg [May] & [2011]) to find all invoices which were created in that month & year (there is a date field called "createdate" in the invoices table that stores the invoice date).

           Problem No. 1 - I don't know how to fill the "years" value list with all unique years in the table?

           Problem No. 2 - Even when I populate the year list manually, I'm still left with a text value for the month; I can't find a function that will convert this text back to a month number? I think a lookup table would probably work but I can't get it to function correctly?

           Problem No. 3 - If I resolve both the above, I end up with a numeric month (eg 5) & numeric year (eg 2011).  I know I can search the table for all records where [createdate=*/05/2012], but how do I turn the two numbers [05] and [2012] into a date field [*/05/2012]??

           I know how to do this in VBA but as a newcomer to filemaker, I can't find the same functionality - although I'm sure it's there!

        • 1. Re: Use drop-down of month names (Jan-Dec) to find records for a report
          philmodjunk

               1) If you don't already have it, define a calculation field, cYear as Year ( InvoiceDateField ). Define your value list with the use values from a field option and specify cYear as the field that supplies your values. Such value lists automatically filter out duplicates.

               2) You can probably find a custom function for this, but this calculation will also do the job:

               Ceiling ( Position ( "JanFebMarAprMayJunJulAugSepOctNovDec" ; Left ( monthNameField ; 3 ) ; 1 ; 1 ) / 3 )

               3) your script can simply combine the month number and the year separated by a slash. If you enter find mode and enter 1/2012, your find will find all records dated January, 2012. This can be easily scripted.

          • 2. Re: Use drop-down of month names (Jan-Dec) to find records for a report
            ChrisDeith

                 Hi Phil

                 Thanks for such a swift and informative reply!  I will try out the two solutions you've suggested for the year & month lists, but I've also not been able to combine strings to create a searchable date value?

                 in the interim, to test the "find values" scripting, I tried setting up two numeric globals (monthnum & yearnum) and entered the values of [05] and [2011]. If I go into find mode and manually enter [*/05/2011] into the createdate field it works fine, however a find based upon [createdate="*/" & monthnum & "/" & yearnum] comes up as "invalid date value" - clearly I'm not using the correct syntax to concatenate the strings together?  

                 In VBA I would have created a text string that added the fields together, and then used the date$(text) function to convert the text into a date upon which I could search but there doesn't seem to be an equivelent function in filemaker so I'm clearly missing something!?

                 thanks,   Chris

            • 3. Re: Use drop-down of month names (Jan-Dec) to find records for a report
              philmodjunk

                   Script your find like this:

                   Enter FInd mode[] --> clear pause check box
                   Set Field [YourTable::DateField ; Globals::gMonthNumber & "/" & Globals::gYear ]
                   Set Error Capture [on]
                   Perform Find[]

                   gMonthNumber and gYear must be either fields with global storage specified or they can be $Variables that received their values prior to the script entering Find mode.