3305 Main Street Ste 019 Vancouver, WA 98663

How can I get a combined list of cost codes and cost types?

Background

Sage 100 Contractor (formerly Sage Master Builder) supports up to nine cost types per cost code.  The Cost Code table (cstcde) contains the list of possible cost codes and the Cost Type table (csttyp) contains the list of possible cost types.  This article covers how to get a vertical list of codes such that each Cost Type is listed for each Cost Code.

Steps
  1. Create a Cost Code query
    1. Click Query on the Liberty Reports toolbar
    2. Select your Sage Master Builder connection and click Next
    3. In the Select Table step, select PM Cost Code (cstcde) and click Next
    4. Select the following columns:
      • Cost Code (recnum)
      • Description (cdenme)
    5. Click Next until you reach the Sort Order step.
    6. Click Add Sort
    7. Click Cost Code and click OK
    8. Click Finish
  2. Add the Cost Type table to the query
    1. Click Edit SQL on the Liberty Reports toolbar.  The following SQL text will be displayed:

      SELECT
      recnum AS Cost_Code,
      cdenme AS Description

      FROM
      cstcde

      ORDER BY
      recnum
    2. Make the following edits (highlighted in yellow) to include the Cost Type table.

      SELECT
      cstcde.recnum AS Cost_Code,
      cdenme AS Description,
      csttyp.recnum AS Cost_Type_ID,
      typnme AS Cost_Type_Description

      FROM
      cstcde, csttyp

      ORDER BY
      cstcde.recnum, csttyp.recnum


      Note that the Cost Type (csttyp) table is added without using table join syntax.  By doing this, all rows from the Cost Type (csttyp) table are returned for each row in the Cost Code (cstcde) table.  The number of rows is therefore equal to the number of Cost Codes times the number of Cost Types.
  3. Optionally add Cost Type criteria
    1. Click Edit SQL on the Liberty Reports toolbar.
    2. Make the following edits (highlighted in yellow) to limit the cost types to number 1 to 5

      SELECT
      cstcde.recnum AS Cost_Code,
      cdenme AS Description,
      csttyp.recnum AS Cost_Type_ID,
      typnme AS Cost_Type_Description

      FROM
      cstcde, csttyp

      WHERE
      csttyp.recnum >= 1 AND csttyp.recnum <= 5

      ORDER BY
      cstcde.recnum, csttyp.recnum

Sample Output

Cost Code Description Cost Type ID Cost Type Description
1000 GENERAL REQUIREMENTS 1 Material
1000 GENERAL REQUIREMENTS 2 Labor
1000 GENERAL REQUIREMENTS 3 Equipment
1000 GENERAL REQUIREMENTS 4 Subcontract
1000 GENERAL REQUIREMENTS 5 Other
1020 Contingency 1 Material
1020 Contingency 2 Labor
1020 Contingency 3 Equipment
1020 Contingency 4 Subcontract
1020 Contingency 5 Other
1050 Bonding 1 Material
1050 Bonding 2 Labor
1050 Bonding 3 Equipment
1050 Bonding 4 Subcontract
1050 Bonding 5 Other

Design Tip

Use conditional formatting to hide repeating cost code values by changing their font color to white-on-white.