Jump to content

Perform Find: Todays Date - X days


This topic is 6457 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

...

Link to comment
Share on other sites

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 ]

Link to comment
Share on other sites

This topic is 6457 days old. Please don't post here. Open a new topic instead.

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.