Jump to content
Sign in to follow this  
ppo

Single search in multiple date fields

Recommended Posts

In a data base, with several Text Fields, I can easily make a research in ALL fields with just ONE "Search Field". I simply create a calculated field with a formula like " Chain(TextField1) & Chain(TextField2) & ....", and place it in a request layout. In Find Mode, you can fill this only field and know that FmP will search in EACH field includes in the formula to extract the concerned records.

Here is my problem :

When I do the same with Date Fields (and of course modify the calculated field formula), FmP never find any record even if all fields contain the date I'm looking for !!!

Many thanks if you can help.

Patrick

Share this post


Link to post
Share on other sites

It is not possible (as far as I know) to Script repeated Find Requests. this means that a scripted work around, such as 'tagging' each successively 'found' group (one date field at a time), and then eventually finding all the tagged records will be slow and tedious, and not always accurate either if there are multiple users trying to do the same thing.

A cleaner way, although not perfect, is to set up a Global date field, and create self-join relationships to each date fields you're looking for.

The 'viewing' layout would need to have a small portal for each self-join relationship, each one showing matching records for the selected global date field. Then you could do analysis on the numbers per portal, or together.

I do not know of way a to put them all in one portal unless you're predefining what you want to see by such functions as MIN(date) or MAX(date). Since dates are inherently numeric fields, they do not concantenate well.

HTH

Share this post


Link to post
Share on other sites

It's the conversion of a formatted date into its internal numeric representation that makes these and other kinds of searches on date fields difficult.

But, you can script the search in multiple fields with multiple requests like this:

code:

Enter Find mode

Set Field [DateField1, gSearchDate]

# When in Find mode, the following step will create a new Find Request

New Record/Request

Set Field [DateField2, gSearchDate]

New Record/Request

Set Field [DateField3, gSearchDate]

New Record/Request

Set Field [DateField4, gSearchDate]

# Etc.

Perform Find


Share this post


Link to post
Share on other sites

Hi,

Many thanks to Bob and its good tip. Although his solution is not based on a "Calculated Field", I've been able to organize my search and to find a date, whatever the Date Field used.

Thanks again Bob,

patrick smile.gif" border="0

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

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