Perform Find: Todays Date - X days

[UURickRose]

I am hoping to find a way to Perform a Find on a data field based on the following logic:

Today's Date - (some number of days)

Example: Today - 14 (for 2 weeks)

I have found another thread in this forum that addresses this through a script using calculations but that is not what I'm looking for. Rather, I'm trying to use the search parameters to bring up all dbase entries that have dates of the current date minus some variable (variable = number of days)

Thanks,

Rick

[LaRetta]

If [ not gNumber ]

Show Custom Dialog [ "You must enter a number" ]

Halt Script

End If

Set Error Capture [ On ]

Enter Find Mode [ ] ... uncheck Pause

Set Field [ yourDateField ; Get ( CurrentDate ) - gNumber ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ "No records found" ]

Halt Script

End If

... do whatever you wish with the found set

- where gNumber is a global field in which you enter a value before you click the button to perform the find.

[ThatOneGuy]

Hey Rick:

This certainly is not intended as creepy as it kinda sounds, but how comfortable are you with relationships? If I follow what you're asking, this could all be achieved with a "date-range" relationship ... no scripting necessary unless you want to spiff it up for your users.

It would require (a) two fields to establish the beginning date and ending date, and defining those fields as "globals" would allow it to function nicely in a multiuser environment, and (: a portal to view the records falling within the date range.

Before going into a full explanation, let us know if you want to head down this path.

[UURickRose]

Thanks, I tried doing this word for word but it didn't work. I appreciate the effort.

Here is a copy of my script (exported the report for you to review):

Script Steps:

If [ not Loop Quotes::gNumber ]

Show Custom Dialog [ Message: "You Must Enter a Number"; Buttons: “OK”, “Cancel” ]

Halt Script

End If

Set Error Capture [ On ]

Enter Find Mode [ ]

Set Field [ Loop Quotes::Prov1 Datestamp; Get ( CurrentDate ) - Loop Quotes::gNumber ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ Message: "No Records Found"; Buttons: “OK”, “Cancel” ]

Halt Script

End If

Fields used in this script:

Loop Quotes::gNumber

Loop Quotes::Prov1 Datestamp

Edited September 12, 2006 by Guest

[UURickRose]

If not too much trouble, I'll get on board for the ride. No problem creating a couple of global date fields. I have several relationships implemented within my dbase today.

[comment]

Is Prov1 Datestamp a Date field?

[UURickRose]

Yes, its a Date field, not a timestamp field

[comment]

AFAICT, your script should work - provided you are on a layout of Loop Quotes.

Why don't you put a pause just before Perform Find[] and see what happens at that point.

[ThatOneGuy]

It's hard to go wrong following LaRetta and Comment, but since you asked about the Date Range model, let's see if we can cover it with this example file. It's rather rough with tables only for Customers and Tasks.

There are three portals on the Customers layout. The first is a garden variety showing the child Tasks belonging to the parent Customer.

The second and third portals use the Date Range fields I mentioned. Of these, one portal shows all Tasks for the given Customer within the date range, while the last portal demonstrates how Tasks for All Customers can be filtered for a date range.

You can go to the related tasks for each portal by clicking on the Row Number.

On the Table Occurrence Graph, examine how the relationships for these three portals are defined. Feel free to post a response, but I bet you'll pick it up quickly, Rick!

DateRangeExample.fp7.zip

[comment]

Ahem.. the script is NOT looking for a RANGE: it is looking for records of a specific date n days ago.

If the requirement is for a range of n days ago until today, it should be:

Set Field [ Loop Quotes::Prov1 Datestamp; Get ( CurrentDate ) - Loop Quotes::gNumber & ".." & Get ( CurrentDate ) ]

[ThatOneGuy]

Ahem.. the script is NOT looking for a RANGE

Never said it was. My post was with respect to an earlier line of discussion.

[UURickRose]

Well, I do apologize... I did not articulate my thoughts properly. You have solved my problem. Thank you very much.

[UURickRose]

LaRetta - thank you - works perfectly :

Everyone else, thank you for chiming in as well. I was hoping to do this without a script, but this will certainly do.

[UURickRose]

Ok, so here comes part II

I have a second date field called "Prov2 Datestamp". Is there a way to extend the found set to check all records that show a range of (today - 7 days) for both of these fields collectively?

So do the script outlined above on "Prov1 Datestamp" field and then extend the found set to include a search for all records in the "Prov2 Datestamp" field as well.

I thought it would be easy enough to add a 2nd line like the first "set field...." but that doesn't extend the found set, rather, it replaces it. So get it to look something like this:

If [ not Loop Quotes::gNumber ]

Show Custom Dialog [ Message: "You must enter a Number"; Buttons: “OK” ]

Halt Script

End If

Set Error Capture [ On ]

Enter Find Mode [ ]

Set Field [ Loop Quotes::Prov1 Datestamp; Get ( CurrentDate ) - Loop Quotes::gNumber & ".." & Get ( CurrentDate ) ]

Set Field [ Loop Quotes::Prov2 Datestamp; Get ( CurrentDate ) - Loop Quotes::gNumber & ".." & Get ( CurrentDate ) ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ Message: "No Records Found", “l” ]

Halt Script

End If

[UURickRose]

Actually, its not replacing it, its returning results that show only records that have dates entered into both fields. I'm trying to get it to return results for any records that had dates entered into the first date field OR any records that had dates placed in the second date field.

The logic above is using AND instead of OR

[comment]

Try:

...

Enter Find Mode [ ]

Set Field [ Loop Quotes::Prov1 Datestamp; Get ( CurrentDate ) - Loop Quotes::gNumber & ".." & Get ( CurrentDate ) ]

[color:red]New Record/Request

Set Field [ Loop Quotes::Prov2 Datestamp; Get ( CurrentDate ) - Loop Quotes::gNumber & ".." & Get ( CurrentDate ) ]

Perform Find [ ]

...

[UURickRose]

I love you guys :

[UURickRose]

Just thought I would reply one more time with the script that I was ultimately trying to attain.

This is an Aging Report that shows outstanding bids based on todays date - some variable number of days. We send out quote requests to up to 4 vendors at least 100 times per day. This report will show who hasn't gotten back to us in X number of days. Quotes that were sent back are excluded from the query.

Your thoughts gave me enough fuel to finish things out. Here is the final script. Thanks again.

If [ not Loop Quotes::gNumber ]

Show Custom Dialog [ Message: "You must enter a Number"; Buttons: “OK” ]

Halt Script

End If

Set Error Capture [ On ]

Enter Find Mode [ ]

Set Field [ Loop Quotes::Prov1 Datestamp; Get ( CurrentDate ) - Loop Quotes::gNumber & ".." & Get ( CurrentDate ) ]

Insert Text [ Loop Quotes::Prov 1; “*” ] [ Select ]

Insert Text [ Loop Quotes::Prov 1 MRC; “=” ] [ Select ]

Perform Find [ ]

Enter Find Mode [ ]

Set Field [ Loop Quotes::Prov2 Datestamp; Get ( CurrentDate ) - Loop Quotes::gNumber & ".." & Get ( CurrentDate ) ]

Insert Text [ Loop Quotes::Prov 2; “*” ] [ Select ]

Insert Text [ Loop Quotes::Prov 2 MRC; “=” ] [ Select ]

Extend Found Set [ ]

Enter Find Mode [ ]

Set Field [ Loop Quotes::Prov3 Datestamp; Get ( CurrentDate ) - Loop Quotes::gNumber & ".." & Get ( CurrentDate ) ]

Insert Text [ Loop Quotes::Prov 3; “*” ] [ Select ]

Insert Text [ Loop Quotes::Prov 3 MRC; “=” ] [ Select ]

Extend Found Set [ ]

Enter Find Mode [ ]

Set Field [ Loop Quotes::Prov4 Datestamp; Get ( CurrentDate ) - Loop Quotes::gNumber & ".." & Get ( CurrentDate ) ]

Insert Text [ Loop Quotes::Prov 4; “*” ] [ Select ]

Insert Text [ Loop Quotes::Universal Access MRC; “=” ] [ Select ]

Extend Found Set [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ Message: "No Records Found"; Buttons: “OK” ]

Halt Script

End If

View As [ View as Table ]