Hi All. I have a small filemaker DB that has contacts, jobs and invoices tables. I would like to create a script where i can select a date range from a popup menu to fill in start date and end date based on the invoice date. The invoice date field is formatted "dd month year". How do a make the script to work so it will pick the date range that => and =< ?

Many thanks in advance.

Eric:

So long as your invoice date field is an actual date field (not a text field), this is a fairly simple operation. If you set up your layout so that you have two date fields for the user to enter dates into - let's call them StartDate and EndDate - then in your script, you will want to enter Find Mode, then use Set Field[] to set your Invoice Date field to StartDate & "..." & EndDate, then perform the find. The ellipsis (the '...') is what is used in FM to indicate a range, so this will find any dates between (and including) the two dates indicated.

-Stanley

Thanks Stanley. Yes the Invoice field is a date field and not a text field. With the two new fields do I just make them as a standard date field and not a global ?

Global date fields should be used.

BTW you could also use a Go to Related Records with an additional table occurence to the invoice table. The join would have to be a theta join keyed as following:

gStartDate <= InvoiceDate

gEndDate >= InvoiceDate

Precisely as John stated.

Theta join you say... That's a new one.

|/|

|/|

Thanks for this thread this is what I've been trying to figure out how to do. I'm having problems with it working. I created the script as stanley showed and I keep getting no records match this request. I know that there is matching records that should be found.

Thank you for your help.

You are on FM6, try this:

Enter Find Mode []

Insert Calculated Result [ DateToText( StartDate ) & "..." & DateToText(EndDate) ]

Perform Find []