How can I get the total cost for an EQ Cost Code as of a given date?

Background
Normally, the solution for obtaining a value as of a given date is to first use the Query Wizard to create your query that returns the list of IDs for the rows on your report (e.g., accounts, jobs, etc) and then use the Sum Wizard to create a column of TSSUM functions to the right of your query that sum up the transaction detail related to each row and for the transactions that fall within your desired date range. In the case of EQ Cost Code amounts, this is not possible because the EQ Transactions are effectively split between multiple tables. The EQ Transaction table holds the Accounting Date and Transaction Date fields and the EQ Cost Code Tran table holds the Amount of each transaction. Since the Sum Wizard only allows you to select one table, you can't specify the field you want to sum (EQ Cost Code Tran - Amount) and filter the records based on the date fields (EQ Transaction - Accounting Date or Transaction Date). This article provides a solution.

Concept
The first argument of most Office Connector worksheet functions (e.g., TSLookup, TSFind, TSSum, TSCount) is the name of the table that you wish to query data from. As an alternative to using the table name, Office Connector also allows you to use a query statement. A query statement, if properly constructed, can allow you to retrieve values from multiple tables and much more. The approach that will be conveyed in this article will make use of this capability.

Steps

1. Create your date range parameters. Identify two cells in your worksheet to identify your desired date range (From and To dates) and enter an initial set of dates to use.
2. Name the From Date cell. Select the cell you used for your From Date, click inside the Name box (to the left of the formula bar where you normally see the cell address displayed), and type: FromDate
3. Name the To Date cell. Select the cell you used for your To Date, click inside the Name box, and type: ToDate
4. Copy and paste the following formula text into a cell somewhere in your workbook. This is a query that joins the EQ Cost Code Tran table to the EQ Transaction table.
   

   ="SELECT TCEQUIP, TCCODE, SUM(TCAMT) AS TCAMT FROM CURRENT_EQT_RECORD_2 LEFT OUTER JOIN CURRENT_EQT_RECORD_1 ON TCEQUIP=TEQUIP AND TCRUN=TRUN AND TCSEQ=TSEQ WHERE TACCTDT BETWEEN {d '" & TEXT(FromDate,"yyyy-mm-dd") & "'} AND {d '" & TEXT(ToDate,"yyyy-mm-dd") & "'} GROUP BY TCEQUIP, TCCODE"

5. Name the cell containing the above query text. Select this cell, click inside the Name box, and type: sqlEQCostCodeTran
6. Query the data returned by the query statement. Type the following formula. If you have a query that returns a list of EQ Cost Codes, you'll likely want to put this formula just to the right of your query on the first row of data. In this example, A5 would be the cell that contains the Equipment ID and B5 would be the cell that contains the EQ Cost Code ID. Adjust these cell addresses if needed.
   

   =TSLOOKUP(sqlEQCostCodeTran, "TCAMT", A5, B5)

7. Copy your TSLOOKUP formula down to all rows of your query. When you refresh the query and new rows are added or other rows drop off, the adjacent TSLOOKUP formulas will be automatically copied down or removed as needed.

How Does It Work?
The query text describes a simple query that returns three columns: Equipment, Cost Code, and Amount (TCEQUIP, TCCODE, and TCAMT). The query has a built-in condition that looks at your date range so that only the desired transactions are included. The results are summarized so that the query would return only one row per Equipment & Cost Code and where the Amount represents all of the accumulated transactions. Think of this query like a table on a hidden worksheet that has data ready for you to query. When you use the TSLookup formula, it works with this data just like it was an ordinary table. The second argument says which column you want to retrieve and the remaining two arguments (A5 = Equipment, and B5 = Cost Code) are used as a filter matching on the first two columns of the query. This identifies the specific row in the table the value is to returned from.

You might notice a small delay when you enter the first TSLOOKUP function but subsequent ones return data quickly. This is because Office Connector doesn't become aware of your custom query text until it is used for the first time by an Office Connector function. When that happens, Office Connector runs the query and holds onto the results. Subsequent functions that use the same query text benefit by being able to use those results.