Jump to content

Searching multiple date fields


Recommended Posts

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

 

Link to post
Share on other sites

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 []

 

Link to post
Share on other sites

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!

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.