3305 Main Street Ste 019 Vancouver, WA 98663

How can I get a list of all cost codes for a job?

Background

The Sage 100 Contractor database does not provide a single table that lists all of the cost codes associated with a given job. The cost codes that are associated with (have been used with) a given job can be found in four separate tables. They include the original budget, budget changes, actual costs, and purchase orders. This article provides an SQL statement that combines the list of cost codes from each of these tables into a single list of the distinct codes that have been used to-date.

Step 1 - Create a simple query

Use the Query Wizard to create a query using the PM Budget Line (bdglin) table. Include the Phase (phsnum) and Cost Code (cstcde) columns and set a condition based on the job so that it only returns the cost codes for the desired job (e.g., using a cell on the worksheet to identify the job).

Step 2 - Replace the SQL
  1. Select a cell anywhere inside the list of cost codes returned in step 1.
  2. On the Liberty Reports tab, click Edit SQL to display the Edit SQL Text window.
  3. Replace the text shown in the window with the following text: SELECT DISTINCT   bdglin.phsnum AS Phase,     bdglin.cstcde AS Cost_Code   FROM     bdglin   WHERE     bdglin.recnum=?   UNION   SELECT DISTINCT     prmchg.phsnum AS Phase,     sbcgln.cstcde AS Cost_Code   FROM     sbcgln     LEFT OUTER JOIN prmchg ON sbcgln.recnum = prmchg.recnum   WHERE     prmchg.jobnum=?   UNION   SELECT DISTINCT      jobcst.phsnum AS Phase,     jobcst.cstcde AS Cost_Code   FROM     jobcst   WHERE     jobcst.jobnum=?   UNION   SELECT DISTINCT     pchord.phsnum AS Phase,     pcorln.cstcde AS Cost_Code   FROM     pcorln     LEFT OUTER JOIN pchord ON pcorln.recnum = pchord.recnum   WHERE     pchord.jobnum=?     AND pchord.status<5   ORDER BY 1,2
  4. Click OK
  5. You will be prompted to supply a value for Parameter1 through Parameter4. For each, select the cell containing the job number and check the box entitled Use this value/refernece for future refreshes.
Explanation

The above SQL statement consists of four separate queries (SELECT statements), one for each of the four tables that contain cost codes associated with the job. The four queries are combined into one query by using the UNION statements. The DISTINCT keyword that appears near the top (on the first query) applies to the entire union query so that the query does not return duplicates. The ORDER BY statement at the end causes the results of the entire union query to be sorted by columns 1 and 2.

Next Steps

Use the Function Wizard or other Excel formulas to add columns adjacent to the column list that will pull other data values or perform calculations. For example, the Function Wizard could be used to insert a DBLookup function that returns the description of each cost code:
=DBLookup("Sage 100 Contractor", "cstcde", "cdenme", $B5) & ""

Other functions (such as DBSum or DBFind) may be useful for obtaining other values such as budget amounts and actual cost.