Journal Row Fields

The following field information is intended to assist those who design reports for Sage 50 using Crystal Reports.

Filename: JRNLROW.DAT

JRNLROW.DAT is the file that holds the meat of the Sage 50 accounting system! It includes at least one row for every account transaction event in Sage 50. Journals rows are synonymous to journal distributions within a single transaction. Some rows serve to hold more than one type of information. There will be one row for every amount that is entered in a Sage 50 task window, PLUS possibly one or two rows for each system-generated (behind the scenes) activity such as cost of goods sold, or below-zero transactions.

Note: Memorized Transactions are stored in StoredTransRows.DAT. All the field names are the same as in JrnlRow.DAT.

Indexes and Their Uses

JRNLROW.DAT has eleven keys, as follows:

0 = By PostOrder. Use this index to link to the JrnlJHdr file.

1 = By PostOrder and then by Index.

2 = By Account, then by Date and then by PostOrder.

3 = By LinkToAnotherTrx, and then by Date.

4 = By Phase

5 = By ProjectNumber, Phase, Cost.

6 = By ProjectNumber, and then by Date.

7 = By Cost

8 = ByGUID

9 = ByUM_GUID

10 = ByPostOrder and then by DistNumber

11 = By ItemRecordNumber, then by date, then by PostOrder, and finally by index. Skips all records where ItemRecordNumber == 0.

12 = By ProjectNumber, then by Phase, then by Cost Code, Account, Date, PostOrder and finally by Index, skipping all records where ProjectNumber ==0.

13 = By LinkToAnotherTrx, then by Date, skipping all records where LinkToAnother Trx == 0.

14 = By ProjectNumber, then by Phase, then by Cost Code, Date. Skips all records where ProjectNumber = 0.

15 = ByJournal, then by Item, then by POClosed, and then by Date. Skips all records where Journal == zero, or Item == zero, or POClosed ==1.

Fields

The following table shows the fields available in the JRNLROW.DAT file and includes the type and size in number of bytes of each field.

Field Name

Type

Size

Comment

GLAcntNumber

Integer

4

G/L account that this row applies to. For certain beginning balance entries, this field will be blank.

PostOrder

Integer

This number is unique to each whole transaction. The same PostOrder can exist on any number of journal rows, but only one journal header can share this number.

RowNumber

Integer

Used to order the rows in a particular transaction consistently.

ItemRecordNumber

Integer

Identifies the inventory item associated with this row, if applicable. This field links to the LINEITEM.DAT table. If this row does not include inventory item record, then value of the field is 0.

JobRecordNumber

Integer

Identifies the job record associated with this row, if applicable. This field links to the PROJECT.DAT table. If this row does not include job record, then value of the field is 0.

PhaseID

ZString

20

If this row pertains to a job and a phase, then this field identifies the phase. This field links to the PHASE.DAT table. (Sage 50 Premium Accounting and higher)

CostCodeID

ZString

20

If this row pertains to a job, phase, and cost code, then this field identifies the cost code. This field links to the COST.DAT table. (Sage 50 Premium Accounting and higher)

LinkToAnotherTrx

Integer

If this row pertains to another transaction, then this is the distribution number of that other transaction. Possible links include:

  • Payments linked to the purchase that is paid off.
  • Receipts linked to the sales invoice that is paid off.
  • Purchases and sales invoices linked to the orders that they are based on.

LinkToOtherTrxIndex

Integer

If LinkToAnotherTrx (above) links to a sales or purchase order, then this is the row number in that order that this row relates to.

Journal

Integer

Identifies the journal source of this transaction row? Valid values include:

0 = General Journal

1 = Cash Receipts Journal

2 = Cash Disbursements (Payments) Journal

3 = Sales Journal

4 = Purchase Journal

5 = Payroll Journal

6 =Cost of Goods Sold Journal

7 = Inventory Adjustment Journal

8 = Assembly Adjustments (Build/Unbuild) Journal

9 = TempBelowZeroInvAdj (These are system-generated transactions within Sage 50's inventory costing engine. The Sage 50 user will never see this journal. Also, these transactions do not contain header records.)

10 = Purchase Orders Journal

11 = Sales Orders Journal

12 = Quotes Journal

RowDate

Date

Identifies the date of the transaction row. In most cases, each row uses the same date.

POSOIsClosed

Logical

1

Indicates that the purchase order or sales order is closed.

IncludeInGL

Logical

True => only IF this row is to be included in general ledger and thus all financial calculations based on general ledger amounts. For example, this field is false for all customer, vendor, job, employee, and line-item (inventory) beginning balance entries.

IncludeInInvLedger

Logical

Include this row in inventory-ledger type calculations and reports only if this flag is true. For example, this field would be true for inventory change rows and false for cost of sales rows, even though both rows refer to items. This is because cost of sales rows do not change the inventory balance.

RowType

Integer

Identifies what kind of row this is. Valid values include:

0 => JrnlRowType_Normal (A general-type journal transaction row.)

1 => JrnlRowType_CGS (The cost of sales row. This row is generally not seen in the program.)

2 => JrnlRowType_InvChg (The opposite side of the cost of sales row above.)

3 => JrnlRowType_JobOnly

4 => JrnlRowType_Discount (This row may be present in a receipt or a payment. The row amount represents the discount amount applied from this check to the related invoice.)

5 => JrnlRowType_SalesTax (The row associated with sales tax in sales or receipt transactions.)

6 => JrnlRowType_Freight (The freight amount calculated for sales transaction.

7 => JrnlRowType_BelowZero
_COGS => (A system-generated cost of sales row used in inventory costing. This is not directly related to any particular header.)

8 => JrnlRowType_BelowZero
_InvChg (A system-generated row inventory adjustment row used in inventory costing. This is not directly related to any particular header.)

9 => SortJob_AssemblyUnbuild
_COGS (A system-generated cost of sales row used in inventory costing when building and unbuilding assemblies. This is not directly related to any particular header.)

10 => SortJob_AssemblyUnbuild_
InvChg (A system-generated inventory adjustment row used in inventory costing when building and unbuilding assemblies. This is not directly related to any particular header.)

11=>JrnlRowType_Retainage (A retainage row which indicates the amount/percent of retainage withheld on an invoice)

12=>JrnlRowType_LaborBurden (A labor burden row which indicates the amount debited from the labor burden cost of sales account for a job on a payroll check)

13=>JrnlRowType_AppliedLaborBurden (An applied labor burden row which indicates the amount credited to the applied labor burden account for a job on a payroll check)

Amount

Float

Identifies the distribution amount for the row. This field does not contain valid values for cost of sales rows. However, it does contain valid values for non-cost of sales-related rows.

Note: To extract journal transaction amounts that involve inventory costing (cost of sales), you must use a custom formula. Tell me more about this.

StockingQuantity

Float

Identifies the quantity entered in the line item rows for invoices or credit memos. Please note that rows not associated with inventory items can still have quantities.

StockingUnitCost

Float

Identifies the unit cost entered in line item rows for invoices and credit memos. Please note that even rows without inventory items can have unit cost amounts.

AmountReceived

Float

Identifies the dollar amount of items shipped or received in sales or purchase orders.

StockingQtyReceived

Float

Identifies the quantity of items shipped or received in sales or purchase orders.

DistNumber

Integer

This applies only to sales and purchase orders. The DistNumber is how invoice rows link to order rows.

RowDescription

ZString

160

This is a text description entered in the task window for the journal distribution row.

CustomerRecordNumber

Integer

Identifies the customer record associated with this row, if applicable. This field links to the CUSTOMER.DAT table. If this row does not apply to customer record, then value of the field is 0.

VendorRecordNumber

Integer

Identifies the vendor record associated with this row, if applicable. This field links to the VENDOR.DAT table. If this row does not apply to vendor record, then value of the field is 0.

EmpRecordNumber

Integer

Identifies the employee record associated with this row, if applicable. This field links to the EMPLOYEE.DAT table. If this row does not include an employee record, then value of the field is 0.

Note: Do not confuse with the EmpRecordNumber field in the JrnlHdr table, which is used for the record number of the default sales rep who applies to a given sales transaction.

rGUID

UBinary

GUID (Globally Unique Identifier) used for linking the JrnlRow table.

UMGUID

UBinary

GUID (Globally Unique Identifier) for unit-of-measure key.

UsedForReimbExp

Logical

True => only if this row has already been applied to a reimbursable expense.

TaxAuthorityCode

ZString

8

If this row is a JrnlRowType_SalesTax, type, then this field is the sales tax authority code.

SalesTaxType

Integer

If this row is a JrnlRowType_Normal type and is a sale or receipt, then this identifies how to tax the item.

PayrollFieldNumber

Integer

If this is a payroll transaction row, then this identifies which payroll field this row applies to.

InvNumForThisTrx

ZString

20

The invoice number that the check, receipt, payment, or credit memo applies to. You can also find the invoice record by linking to the LinkToAnotherTrx field.

DateCleared

Date

Date when this transaction cleared the bank (the Cleared check box is selected in Sage 50's Account Reconciliation window). Currently, all rows of a transaction clear on the same date.

Quantity

Float

The quantity as it appears in the appropriate Task window.

QtyReceived

Float

The quantity received as it appears in the appropriate Task window.

UnitCost

Float

The unit cost as it appears in the appropriate Task window.

POCreated

Logical

This indicates that a purchase order has been created for the current row.

HasSerialNumbers

Logical

The row has serial numbers associated with it.

RetainagePercent

Float

Retainage percent for the row. (Sage 50 Quantum Accounting)

LaborBurdenPercent

Float

Labor burden percent for the row. (Sage 50 Quantum Accounting)

JournalRowEx

Integer

Indicates the type of transaction row (used by the SDK to choose an appropriate class to instantiate or filter for lists)

LinkJournalRowEx

Integer

If LinkToAnotherTrx is not zero, indicates the type of transaction row this row references (used by the SDK to choose an appropriate class to instantiate or filter for lists)

LastUpdateCounter

Integer

What do you want to do next?