AnsweredAssumed Answered

Examine Payments Table and list Credit Alert for Contacts with Unpaid Credits

Question asked by puzzledata on May 25, 2010
Latest reply on May 26, 2010 by puzzledata

Title

Examine Payments Table and list Credit Alert for Contacts with Unpaid Credits

Post

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.

Outcomes