Searching on Dates

AMoore · December 3, 2004

I need to write a database, Where when the database starts up will alert us to anyone who has a Birthday , Wedding Anniversary ONE Week BEFORE the event , Using BDATE ( for birthday field date) and WEDDATE ( For Wedding Date), I have searched this forum and can not find anything that answers this question, any help please!

---------------------------------------

I have also wrote another database, and need to search it for :- When the OUTSTADNING field is YES and the INVOICE DATE field is 30 days and/or OLDER than current date, again any help please, i have tried various methods and no joy!! :-(

RalphL · December 3, 2004

Future dates can be found by adding the number of days to the date. For one week it is date + 7, for 30 days date + 30.

Is this enough help to get you started?

Søren Dyhr · December 3, 2004

In the prefs. is it posible to make a script perform it's task on the opening of the file. This script makes use of the logic's of this search script:

Set Error Capture [ On ]

Enter Find Mode [ ]

Set Field [ Events::BDATE; Get ( CurrentDate ) + 7 ]

New Record/Request

Set Field [ Events::WEDDATE; Get ( CurrentDate ) + 7 ]

Perform Find [ ]

Set Error Capture [ Off ]

Loop

Exit Loop If [ Get ( FoundCount ) = 0 or Get ( LastError ) ]

Go to Record/Request/Page [ First ]

Show Custom Dialog [ Title: "Yikes"; Message: Events::ContactName & " celebrates his/her " & Case ( Events::BDATE = Get ( CurrentDate )+7;"birthday "; Events::WEDDATE = Get ( CurrentDate )+7;"wedding anniversary " ) & "this day next week"; Buttons: "OK" ]

Omit Record

End Loop

Show All Records

--sd

AMoore · December 3, 2004

Yes!

I was OVER thinking on this one!!

So far i am using

Enter Find Mode [Restore] - Where in restore it looks for "YES" in OUTSTANDING field

Perform Find

Insert Calculated Result[select; invoices::invoice date; Get(CurrentDate)-30]

Perform Find

And i Hope that this search will bring up all records that are flagged YES and that their Date is 30 days or less than todays date

AMoore · December 3, 2004

Great! !!!

THanks for that, I will use that when the database is finished!!!

-Queue- · December 3, 2004

Enter Find Mode [Restore] - Where in restore it looks for "YES" in OUTSTANDING field
Perform Find

Insert Calculated Result[select; invoices::invoice date; Get(CurrentDate)-30]

Perform Find

Once you perform the find, you're overwriting the first found record's invoice date with Get(CurrentDate) - 30. And then your second Perform Find basically does nothing. I don't think that's what you want to do.

Try

Enter Find Mode [Restore]

Set Field [invoices::InvoiceDate; "<=" & Get(CurrentDate) - 30]

Perform Find

AMoore · December 4, 2004

Thanks, Done that ( dropped out the bit about searching for "YES" as well, and just searching for invoices that are 30 days and older), I know there are loads of invoices 30 days and older (including the paid ones!) but the above just returns Not Found, Modify Find!? , I must be doing something wrong.

I know the logic i want to use is :-

if invoicedate <= (currentdate-30) & outstanding ="YES" then search for these records

else

Print "Nope!!! they have all paid up!!! Welldone Boss!!"

end if

I have tried using similar logic in filemaker, but it comes back saying not a valid date!!

And yet if i manually search with YES in Outstanding Field and manually enter into the invoice date field date 04/11/04...04/12/04 works fine (although you can't do it in reverse 4/12/04...4/11/04 says it can't find anything!!) , But i cant search manually by entering something like <= Get(CurrentDate)-30

I'm lost on this, and yet i feel i am sooo close!!

RalphL · December 4, 2004

You have to put that logic into Filemaker.

AMoore · December 4, 2004

Ermmm!

I have! and it did not work, as i said above, Its just getting the syntax correct in filemaker, and i just can not sus it out!

RalphL · December 4, 2004

Try this script:

RalphL · December 4, 2004

I don't know what is happening by the script keeps disappearing from my post

Findscript.pdf

AMoore · December 4, 2004

Got it!!

Many thanks!

I will try it out later!, Need to take a break from the computer now!! -)

Thanks again!

Lee Smith · December 4, 2004

Try this script:

What Script?

Lee Smith · December 4, 2004

Got it!!
Many thanks!

I will try it out later!, Need to take a break from the computer now!! -)

Thanks again!

Got What? Try what?

How are you seeing something that I'm not seeing?

Lee

AMoore · December 4, 2004

The attachment in this post ( #136193 - Sat Dec 04 2004 09:26 AM )

RalphL · December 4, 2004

Hi Lee,

I printed the script to a pdf. I copied it and pasted into my post. The preview looked good. When it was posted the script disappeared. Any ideas why this is happening.

-Queue- · December 4, 2004

GetAsText isn't necessary in 7. FM auto-converts the date appropriately. As long as the Restore portion of Enter Find Mode is set with Outstanding = "yes", I don't see why the script I suggested wouldn't work, as long as both fields are in the same table.

RalphL · December 4, 2004

You don't need the = or the quotes. I guess I still like to see everything in the find.

Lee Smith · December 4, 2004

I missed the fact that you had posted an attachment.

I don't know why it disappeared on you Ralph?? That hasn't happened to me yet. I have posted many scripts and calculations directly into my posts, and other than having to fiddle with them to look right, I've never noticed them disappear like that. Although I have wished they had when I have made an error.

I hope Stephen Dolenski (Ocean West) is listening. Maybe you should post this in Feedback below to ensure he see it.

Did you try using the "Code" feature?

I usually change the spacing that are tabs in to spaces. Someone, I think it was Fenton, posted a tip about using the Option and Space Bar for adding spacing in the place of Tabs when posting Scripts, Calculations, etc., and it works really great too.

Lee

AMoore · December 6, 2004

i tried to enter in that script, but filemaker would not accept the syntax, kept giving me various errors, i have done this so far, but it does not work!! -(

File attached...I hope!!

find.pdf

RalphL · December 6, 2004

Either this statement

"<=" & GetAsText ( Get ( CurrentDate ) - 30)

or this one

"<=" & Get ( CurrentDate ) - 30

will work

I used <= to represent the less than or equal sign. This part must be in quotes. The & concatenates the sign to the function.

AMoore · December 6, 2004

Many thanks

i got this so far


Set Error Capture [ On ] 

Enter Find Mode [  ]

 Set Field [  invoices::Outstanding? =  "yes" ] 

Insert Calculated Result [ invoices::Invoice Date; "<=" & Get ( CurrentDate ) - 30 ]

 Perform Find [ Specified Find Requests: Find Records; Criteria: invoices::Invoice Number: "0" AND invoices::Invoice Date: "<=6/11/2004" ]

it finds the only invoice that is YES and 30days old, but then goes on to display 11 other records that are 30 days but the outstanding field is set to "NO" , So i guess that the last search (on date) has "over ruled" the first search

AMoore · December 6, 2004

if there is a moderator about, could you fix my above post, as i can not seem to edit it!!

Its too long!!! left to right!!!

i used the " code " tags! and its the only post i can not edit of mine!!!

RalphL · December 6, 2004

The Perform Find step should be with the Specify find requests unchecked.

AMoore · December 6, 2004

Yep its unchecked

AMoore · December 6, 2004

SORTED! !!! ! ( i think!)

With loads of help from Ralph ( THANK YOU!!!)

Got script looking like this -

Set Error Capture [ On ]

Enter Find Mode [ ]

Insert Calculated Result [ invoices::Invoice Date; "<=" & Get ( CurrentDate ) - 30 ]

Insert Calculated Result [ invoices::Outstanding?; "Yes" ]

[ Select ]

Perform Find [ Specified Find Requests: Find Records; Criteria: invoices::Invoice Number: "0" AND invoices::Invoice Date: "<=6/11/2004" ]

Note where is says 6/11/2004 it really is Get(CurrentDate)

EDIT:---

Note sure why it says Invoice Number mind you, as i have made NO reference to it!!! And it appears to be doing its job!!!

RalphL · December 6, 2004

I have assumed the field Outstanding is text field and not a number field formatted as a boolean.

If it is a boolean then value in the script needs to be 1.

RalphL · December 6, 2004

I don't understand how you get this script step:

Perform Find [ Specified Find Requests: Find Records; Criteria: invoices::Invoice Number: "0" AND invoices::Invoice Date: "<=6/11/2004" ]

I get Perform Find []

AMoore · December 6, 2004

Yep! it a text field, with a drop down menu for Yes or No

-Queue- · December 6, 2004

You don't need Insert Calculated Result. You can use Set Field. Perform Find should not have any options selected. And your original Enter Find Mode had 'Restore' selected, and should have had criteria with Outstanding = "yes".

To reiterate:

Set Error Capture [On]

Enter Find Mode [Restore] <- 'Specify find requests' => Action: Find Records when; Field Invoices::Outstanding; Criteria: "yes"

Set Field [invoices::InvoiceDate; "<=" & Get(CurrentDate) - 30]

Perform Find [ ]

Attached is a simple sample file with a script to demonstrate.

AMoore.zip

AMoore · December 6, 2004

PERFECT!!!

Many thanks again all!!!

Sign In

Searching on Dates

Recommended Posts

Create an account or sign in to comment

Create an account

Sign in

Important Information