Searching multiple date fields

[Chrism]

Hi,

We have a database with various renewal dates.

Each date field relates to a different renewal type, and is its own field.

I can easily do a search on Renewal type 1's date field to bring back results / sort.

What i'd like to be able do it is search across multiple date fields, and bring back a list of results if any of those fields say are less 30 days in the future.

Is that possible?

Thanks

 

[comment]

It is possible, but tedious. Why not use a single RenewalDate field, with a RenewalType field alongside it? If the same object can have multiple renewal dates of different types, use separate records in a related table or possibly - if the number of types is fixed - a repeating field. Then you will only have one field to search. 

With your current structure you need to create a separate find request for each field, e.g. 

Set Variable [ $criteria; Value:"<" & Get ( CurrentDate ) + 30 ]
Enter Find Mode []
Set Field [ YourTable::RenewalDateA ; $criteria ]
New Record/Request
Set Field [ YourTable::RenewalDateB ; $criteria ]
New Record/Request
Set Field [ YourTable::RenewalDateC ; $criteria ]
Perform Find []

 

[Chrism]

Great thank you thats works!

Yes I am a Filemaker novice and sure I could of been more clever with using the fields as you suggested, but we are doing lots of importing etc and seemed easier to have separate fields.

I created a script and a button for this search so easy enough!