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!! :-(

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?

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

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

Great!

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

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

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!!

You have to put that logic into Filemaker.

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!

Try this script:

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

Findscript.pdf

Got it!!

Many thanks!

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

Thanks again!

Try this script:

What Script?

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

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

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.

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.

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

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

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

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.

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

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!!!

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

Yep its unchecked

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!!!

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.

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

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

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

PERFECT!!!

Many thanks again all!!!