Use drop-down of month names (Jan-Dec) to find records for a report
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] & ) 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  and  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!