Examine Payments Table and list Credit Alert for Contacts with Unpaid Credits
I have a contact table with basic customer data. Each customer has a unique Customer Number
In addition there is a Payments Table with purchases and credits on it.
If the customer is given a credit, when it is paid the date received is entered in the table.
Unpaid credits are evident by:
PaymentType = "CREDIT" and PayDateReceived = ""
I have a portal of the Payments Table in a tab on the Contact Table.
The client wishes to see a "CREDIT ALERT" displayed
on the Main Contact Page whenever there is an unpaid credit
for that customer number.
So for example if there are the following data in their history:
TransactionDate Amount PaymentType Date Received
04/05/2010 $300.00 CREDIT 4/15/2010
04/12/2010 $200.00 PURCHASE
04/17/2010 $300.00 CREDIT
05/01/2010 $200.00 CREDIT 5/3/2010
I wish to create a Calculation Field that would examine each record in the Payments Table
for that unique customer number
Find out if there are any "CREDIT" that do not have a Date Received
(example the line in red would be the unpaid credit)
and have the field appear in Red Bold Letters: "CREDIT ALERT"
So far it only works if I have just the first "CREDIT" entry without a Date Received.
Please recommend a way to do this within a Calculation Field, or some type of automatic trigger when opening the contact data for each customer if it requires a script.