2 Replies Latest reply on May 26, 2010 5:18 PM by puzzledata

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

    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.