Cacher is the code snippet organizer for pro developers

Client Inquiry Custom Formulas [ New ]
Options

‎06-05-2015 01:46 PM

Custom Formulas 

 

    Client Inquiry: Last receipt Date, from Client Invoice Inquiry.

Note: You can substitute rdate for any field on the Axreceipt table you might want.

 

SELECT rDate

 FROM(

   SELECT DISTINCT TOP 1

   ciKey

   ,rdate

   FROM  AxReceipt

      LEFT JOIN AxGLDetail         ON rKey = gldReceipt

      LEFT JOIN AxTransaction    ON gldTransaction = tKey

      LEFT JOIN AxClientInvoice  ON tClientInvoice = ciKey

   WHERE gldIsCurrent = 1 AND gldReceipt IS NOT NULL

   AND ciKey = [Client Invoice Key]

   ORDER BY rDate desc

   )Temp

  

    Client Inquiry: Add Project Department to Client Receipt Inquiry

SELECT prjEntityDescription FROM PROJECT WHERE prjkey = [Project Key]

 

    Client Inquiry: Add First Client Invoice Number

SELECT TOP 1 ciInvoiceDate FROM AxClientInvoice WHERE ciClient = [Client Key] AND ciStatus = 4 ORDER BY ciInvoiceDate

 

    Client Inquiry: Add Receipts for a date range

SELECT ISNULL(-SUM(gldAmount), 0.0)

FROM AxGLDetail

            JOIN AxReceipt ON gldReceipt = rKey

WHERE gldControlAccountType IN (2,4)

            AND rClient = [Client Key]

            AND rDate BETWEEN '01-01-1900' AND '12-31-2078'

GROUP BY rClient

 

To Use these Custom Formulas

Copy the desired Formula into the definition of your Inquiry column.