Nov 29, 2009; 16:08
George R. Hozendorf
Past Due Nightmare
Here are the fields on the layout:
Pledge Amount - number ($)
Pledge Type - Drop Down Value List of Monthly, Bi-Monthly, Quarterly, Semi-Annually and Annually
Number of Payments - number (number determined by the Pledge Type i.e. 12, 6, 4, 2 or 1)
Payment Amount - number ($) (Pledge Amount divided by the Number of Payments)
There are 12 Date Due Fields - date, 12 Date Paid Fields- date and 12 Amount Paid - number ($) fields each having a different field name i.e. pmOneDate, pmTwoDate, etc., DatePaid1, DatePaid2, etc. and Amount1, Amount2,etc.
State Date - Entered when a Pledge is received. The Date Due is calculated based on this date as well as the Pledge Type.
Total Paid - number ($) (amount paid total)
Balance Due - number ($) (Pledge Amount minus Total Paid)
Past Due - text (goal is to return a "YES" is a payment is not made and/or if a payment is less than the Payment Amount.
Let's say a donor makes his first payment of $300 but his next payment is only $200. The way I've got it set up now Past Due is blank, where it should be "YES." If it were "YES" and he makes a payment of $400 the next time to catch up, "YES" goes away. I want the past due indicator to simply use the Total Paid against what should have been collected by the current date. Then whenever the expected amount is not current by Current Date, then the "YES" would show. Here's what I've got so far:
PastDue =
If (
pmOneDate and Get ( CurrentDate ) > pmOneDate and not Amount1
or
pmTwoDate and Get ( CurrentDate ) > pmTwoDate and not Amount2
or
etc, etc, etc,
; "YES" )
Any help will certainly be appreciated,
George
--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/FileMaker/Browse/
Manage your subscription: http://www.ListSearch.com/FileMaker/