AnsweredAssumed Answered

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

Question asked by ChrisDeith on Oct 18, 2012
Latest reply on Oct 18, 2012 by schamblee

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!

Outcomes