Setting up alerts in an existing database

[ambweb]

I'm using Filemaker 5.5 on a Mac OSX 10.1.5. I have a database for collecting payments. I want to be notified when payments are past due. While I can go in and perform a find to locate this information, I'm trying to make my life easier by automating the process . I'm actually trying to do 2 things here.

1. have an alert in filemaker come up everytime the date in the "due date" field matches the current date without anything in the "paid" field.

2. Send an e-mail to the customer alerting him of the late payment (ideally CC:ing the info to me)

Not sure if these are easy fixes or age-old problems, but would appreciate any help.

[LiveOak]

It is easy enough to create an overdue status:

Warning (calculation, text) =

Case(Status <> "Paid" and (Status(CurrentDate) - InvoiceDate) > 30, "Past Due", "")

This can be used as the basis of a script that is run or runs automatically (a plug-in such as Activator can do this or it can be done on startup or shutdown) to send an email. The set of destination would be the found set of unpaid/overdue invoices.

I like sending emails using an email plug-in such as SMTPit from Comm-Unity (www.cnsplug-ins.com). A script can be created that will loop through the records in the invoice file and build and send an email for each found record. Plug-ins allow a "cc:" to be defined for each email. The email scripting is a little complex, but the example files with SMTPit are good.

-bd

[ambweb]

Thank you for the quick reply. I was actually able to run a nice script for the e-mail problem using SMTPit. What a great plugin!

I had a tougher time figuring out the calculation field. I wrote it like this :

Case (Status <> DEPOSIT IN: and (Status (CurrentDate) - DUE DATE 1) > 30, "Past Due", "")

"Deposit In" is my "paid" field and Due Date 1 is the date the deposit is due. Filemaker keeps highlighting the first Status and telling me that "This field can't be found". Also, I suppose the 30 is assuming that my clients have 30 days past the due date to make good?

[LiveOak]

Dates are stored internally as the number of days from 1/1/01, so when you subtract two dates you get days.

If the contents of your status field is "DEPOSIT IN:", it must be in quotes in your calculation.

Case (Status <> "DEPOSIT IN:" and (Status (CurrentDate) - DUE DATE 1) > 30, "Past Due", "")

-bd

[ambweb]

That's really not the case. I was confused.

Deposit In: is actually a field with date values. A date only gets entered if a deposit has been paid, hence the unpaid deposits are shown by an empty Deposit In: field.

I did go back and enter the calculation copying and pasting what you have below and still get the same error. "This field cannot be found" highlighting the first status instance. Are there too many "(" marks, not enough? I also don't know what this function "<>" returns.

I'll admit to being fairly uneducated here. Perhaps you could dumb it down a bit for me?

[Kurt Knippel]

Case (Status <> "DEPOSIT IN:" and (Status (CurrentDate) - DUE DATE 1) > 30, "Past Due", "")

I did go back and enter the calculation copying and pasting what you have below and still get the same error. "This field cannot be found" highlighting the first status instance. Are there too many "(" marks, not enough? I also don't know what this function "<>" returns.

I prefer writing code out a little differently, especially CASE statements. Try this:

CASE (

Status <> "Deposit IN: " and

(Status(CurrentDate) - Due Date 1) > 30,

"Past Due", ""

)

This is really the same calc formatted a bit differently, and everything looks fine.

The error you are getting on the first Status could either indicate that your actual status field is something different or is spelled differently. The "<>" is an inequality symbol that means "Not Equal To", more literally it evaluates true if the value on the left is either less than or greater than the value on the right.

[ambweb]

Does this actually work for you? Have you tried it in a calculation field? I copied and pasted yours exactly as you had it captKurt. I still get the same error highlighting the same instance... I'm missing something here.

What's worse, is I don't think this will actually work as expected for my database, even if it did, it's not what I really want to do anyway. Let me go back to the beginning and try to explain my setup. I currently determine if an invoice is past due using the following script:

Enter Find Mode

Insert Text [DEPOSIT IN", "="]

Insert Calculated Result [DUE DATE 1, DateToText(Today-14)&"..."&DateToText(Today)]

Perform Find

Basically, I'm retrieving empty values in the DEPOSIT IN: field for a range of dates (in this case, the past two weeks). If the field is empty, the deposit has not been received and since I'm only looking at past dates, I already know they're past due.

I suppose I may not have been specific enough in my initial request. What I was hoping to do is on any given day if the deposits for that day have not been received have a message pop up telling me which deposits are due. I've been unable to get Show Message to include any information about a specific record, or I could create a script that would do it using that.

[LiveOak]

We need to get past copy and paste and think about the calculation:

Case (DEPOSIT IN <> "paid" and (Status (CurrentDate) - DUE DATE 1) > 30, "Past Due", "")

In the first calculation, you needed to replace the first "Status" with YOUR status field, "DEPOSIT IN" (I don't know if the colon was an error or part of your field name). You are comparing the contents of "DEPOSIT IN" to whatever you use to indicate that an invoice is paid. I've used "paid", but you need to replace this with whatever you use. If you use a number to flag payment, like a "1", you will need to replace DEPOSIT IN <> "paid" with DEPOSIT IN <> 1 (notice the comparison for numbers fields doesn't use quotes). If you use the text "all done" you will need to make this DEPOSIT IN <> "all done".

-bd