How can I get the total amount of the change orders for a specific cost code?

Background

Change orders that impact the budget are stored in two tables within the Sage 100 Contractor database. The PM Change Order (prmchg) table contains the header information about each change order (such as the description, job number, date, etc.) and the PM Budget and Sub Change Order Line (sbcgln) table contains the detail information for the change order (broken down by cost code and cost type). When building a report, you might want to include the total amount of the change orders that have been applied to a specific cost code (identified by Job, Phase, and Cost Code). When using the Function Wizard to create a DBSum formula, you may find that although you can select the PM Budget and Change Order Line table, you can't add conditions for the job number, phase, and status because those fields are not in that table.

Solution

The following formula uses a table-join expression to include more than one table in the DBSum function:

To use the above formula in your worksheet, copy and paste the above text into the desired cell. Then replace JobID with a cell reference to the cell containing the job number. Replace Phase with a cell reference to the cell containing the Phase, and Cost Code with a cell reference to the cell containing the Cost Code.

Explanation

The second argument of the DBSum function normally identifies the table from which the data values should be queried. In this case, the following table-join expression is used instead:

sbcgln LEFT OUTER JOIN prmchg ON sbcgln.recnum=prmchg.recnum

By using the above table-join expression in the second argument, you are able to use any column from either the PM Change Order (prmchg) or PM Budget and Sub Change Order Line (sbcgln) table in the filter argument:

jobnum=? AND phsnum=? AND cstcde=? AND status<5

In the above filter, the jobnum, phshnum, and status fields are from the PM Change Order (prmchg) table and the cstcde field is from the PM Budget and Sub Change Order Line table.

The last three arguments simply provide the job, phase, and cost code values for the filter.