Sage 50 Functions in Crystal Custom Formulas

Some Sage 50 fields—ones that can't be described in Sage 50 data [.DAT] files require custom formulas to extract data for reports. The fields listed below fall into this category. If you need the additional information contained in these special fields while designing reports in Crystal Reports, use the functions listed below in your custom report formulas.

Note: Reports containing these custom formulas must reside in the same directory as your Sage 50 company files. This is so the reports can extract data directly from the company information contained in the files.

Important! If you are working with Crystal on a computer where no previous versions of the Sage product and Crystal Reports have been installed, the special Sage 50 functions need to be loaded into Crystal Reports. Otherwise, they will not be present for use in the reports you are designing.

To load the Sage 50 functions into Crystal, follow these steps:

  1. Open Sage 50 and then a Sage 50 company.
  2. From the Reports & Forms menu, select Crystal Reports.
  3. Select any report in the list, and double-click it.

    4. The report opens in Crystal Reports.

  4. Close both the report and Crystal Reports.
  5. In Sage 50, from the Reports & Forms menu select Crystal Reports Designer.

Now when you design a new report in Crystal Reports, all Sage 50 functions will appear in the Crystal Formula Editor.

How To Access Custom Formulas in Crystal Reports

Do the following:

  1. From the Standard Toolbar, select the Field Explorer button.
  2. In the Fields Explorer window, select Formula Fields.
  3. To create a new formula field, click the New button.
  4. Enter a name, such as “Amount,” and click OK.

    5. The Crystal Reports Formula Editor appears.

  5. To access the Sage 50 functions, click Functions in the middle pane, and then double-click Additional Functions.
  6. Find and double-click peach (u2lpeach.dll).

    The Sage 50 functions listed below should appear.

Custom Field Names

GetPeachCustomFieldHeadingv2 ({Company.CompanySpecial1a}, {Company.CompanySpecial1b}, {Company.CompanySpecial1c}, {Company.CompanySpecial1d}, AR=1 AP=2 PR=3 Inventory=6 Jobs=7, (Index 0..4))

The appropriate format for the function is suggested by the displayed text. For example, to return the custom field headings for customers, you would create five formulas, each for a different heading. The function should read

GetPeachCustomFieldHeadingv2 ( Filename, 1, 0)

This returns the first customer custom field heading. (Most indexes with Sage 50 fields begin with zero_

GetPeachCustomFieldHeadingv2 ( Filename, 1, 1)

This returns the second customer custom field heading. Follow the same format for the three remaining formulas.

Tip: To view an example of this function, examine the Customer Detail List or the Vendor Detail List which are Crystal Reports included with Sage 50.

Inventory Costing Row Amount

GetPeachRowAmountv2 ({Company.CompanySpecial1a}, {Company.CompanySpecial1b}, {Company.CompanySpecial1c}, {Company.CompanySpecial1d}, {JrnlRow.RowType}, {JrnlRow.Amount}, {JrnlRow.Journal}, {JrnlRow.PostOrder}, {JrnlRow.RowNumber}, {JrnlRow.RowDate}, {JrnlRow.ItemRecordNumber}, {Company.IsCashBasis})

This returns appropriate row amounts for cost of sales rows. In the raw data, the cost of sales mount is not necessarily accurate, as the real cost of sales is based on a calculation. The JrnlRow table is required to use this function in a report.

Price Level Name

GetPeachPriceLevelNamev2 ({Company.CompanySpecial1a}, {Company.CompanySpecial1b}, {Company.CompanySpecial1c}, {Company.CompanySpecial1d}, (Index 1..20))

This function returns one of the 20 price level names listed in Inventory Item Defaults, the Price Levels tab. When using the function, be sure to replace the string "(Index 1.20)" with the desired number from 1 to 20. To use this function, you must include the LineItem table in your report.

Beginning Balance (Quantity)

GetPeachItemBegBalv2 ({Company.CompanySpecial1a}, {Company.CompanySpecial1b}, {Company.CompanySpecial1c}, {Company.CompanySpecial1d}, {LineItem.ItemRecordNumber})

This function returns the quantity on hand at the beginning of the current accounting period for the item specified. The function requires that you use the LineItem table.

Quantity on Hand

GetPeachItemQtyOnHandv2 ({Company.CompanySpecial1a}, {Company.CompanySpecial1b}, {Company.CompanySpecial1c}, {Company.CompanySpecial1d}, {LineItem.ItemRecordNumber})

This function is similar to GetPeachItemBegBal except that it returns the quantity on hand at the end of the current accounting period. It is the same number shown in Maintain Inventory Items in the Qty on Hand field. The function requires that you use the LineItem table.

Quantity on PO's

GetPeachItemQtyOnPOv2 ({Company.CompanySpecial1a}, {Company.CompanySpecial1b}, {Company.CompanySpecial1c}, {Company.CompanySpecial1d}, {LineItem.ItemRecordNumber})

This function returns the quantity of the specified item on open purchase orders. It is the same number shown in Maintain Inventory Items in the Qty on PO's field. The function requires that you use the LineItem table.

Quantity on SO's

GetPeachItemQtyOnSOv2 ({Company.CompanySpecial1a}, {Company.CompanySpecial1b}, {Company.CompanySpecial1c}, {Company.CompanySpecial1d}, {LineItem.ItemRecordNumber})

This function returns the quantity of the specified item on open sales orders. It is the same number shown in Maintain Inventory Items in the Qty on SO's field. The function requires that you use the LineItem table.

Last Cost

GetPeachItemLastCostv2 ({Company.CompanySpecial1a}, {Company.CompanySpecial1b}, {Company.CompanySpecial1c}, {Company.CompanySpecial1d}, {LineItem.ItemRecordNumber})

This function returns the same number shown in Maintain Inventory Items in the Last Unit Cost field. The function requires a LineItem record. The function requires that you use the LineItem table.

Current Period Start Date

GetPeachCurrentPeriodStart

This function returns the start date of the current accounting period .

Current Period End Date

GetPeachCurrentPeriodEnd

This function returns the end date of the current accounting period.

Selected Period Start Date

GetPeachPeriodStart

This function returns the start date of the accounting period selected by index number within the function

GetPeachPeriodStart(Filename, (Index 1..40) )

For example, the function

GetPeachPeriodStart(Filename, 40)

would return the start date of period 26 in the case of fiscal years set up with thirteen periods.

Selected Period End Date

GetPeachPeriodEnd

This function returns the end date of the accounting period selected by index number within the function:

GetPeachPeriodEnd(Filename, (Index 1..40) )

For example, the function

GetPeachPeriodEnd(Filename, 40)

would return the end date of period 26 in the case of fiscal years set up with thirteen periods.

Estimated Job Expenses

GetPeachEstJobExpensev2

This function returns the total estimated job expenses seen on the Maintain Jobs window. The function requires that you use the Projects table.

Estimated Job Revenue

GetPeachEstJobRevenuev2

This function returns the total estimated job revenue seen on the Maintain Jobs window. The function requires that you use the Projects table.

Actual Job Expenses

GetPeachActualJobExpensev2

This function returns the total actual job expenses seen on the Maintain Jobs window. The function requires that you use the Projects table.

Actual Job Revenue

GetPeachActualJobRevenuev2

This function returns the total actual job revenue seen on the Maintain Jobs window. The function requires that you use the Projects table.

Actual Job Expenses by Period

GetPeachThisPeriodActualJobExpensev2

This function returns the total actual job expenses for the chosen period. The function requires that you use the Projects table.

Actual Job Revenue by Period

GetPeachThisPeriodActualJobRevenuev2

This function returns the total actual job revenue for the chosen period. The function requires that you use the Projects table.

Account ID

GetPeachGLAccountIDv2

This function returns the general ledger account ID for the account with the account record number specified in the function.

Account Description

GetPeachGLAccountIDv2

This function returns the general ledger account description for the account with the account record number specified in the function.

Time Stamp Date

GetPeachTimeStampDatev2

This function returns the date of the time stamp for a particular row in the Audittr.DAT table.

Time Stamp Time

GetPeachTimeStampTimev2

This function returns the time of the time stamp for a particular row in the Audittr.DAT table.

Company Information Functions

These functions, which are self-explanatory, return company information as entered in the Sage 50 Maintain Company Information window. The functions include the following:

  • GetPeachCompanyNamev2
  • GetPeachAddress1v2
  • GetPeachAddress2v2
  • GetPeachCityv2
  • GetPeachStatev2
  • GetPeachZIPv2
  • GetPeachCoutryv2
  • GetPeachFEINv2
  • GetPeachIsCashBasisv2
  • GetPeachISRealTimePostv2
  • GetPeachCompanyTypev2
  • GetPeachEmailv2
  • GetPeachWebSitev2
  • GetPeachPhoneNumberv2
  • GetPeachFAXNumberv2
  • GetPeachSEINv2
  • GetPeachSUINv2

Bill of Material (BOM) Functions

All bill of material functions discussed below require that you use the LineItem table.

Assembly Component

GetPeachBOMCompIDv2

This function returns the ID of a component used in an assembly based on the index specified in the function. In this function, the numbered index of assembly components starts with zero. The function is displayed as follows:

GetPeachBOMCompIDv2 (Filename, {LineItem.ItemRecordNumber}, (Index 0..99) )

To return the desired component ID, you must replace "Index 0..99" with the appropriate number from 0-99. For example, to return the first component in the assembly, the function should read

GetPeachBOMCompIDv2 (Filename, {LineItem.ItemRecordNumber}, 0 )

Use the index field in a similar manner for all BOM functions.

Assembly Component Description

GetPeachBOMCompDesv2

This function returns the description of a component used in an assembly based on the index specified in the function.

Required Number of an Assembly Component

GetPeachBOMCompReqv2

This function returns the number of units of an individual component required to build the assembly.

Quantity on Hand of an Assembly Component

GetPeachBOMComponHandv2

This function returns the quantity on hand of an individual component used in the assembly.

Quantity Available of an Assembly Component

GetPeachBOMCompAvailv2

This function returns the quantity available of an individual component used in the assembly. The quantity available is calculated as the amount on hand plus the quantity on all purchase orders minus the quantity on all sales orders.

Location of an Assembly Component

GetPeachBOMCompLocv2

This function returns the storage location in your facility of an individual component used in the assembly.

UPC/SKU of an Assembly Component

GetPeachBOMCompUPCv2

This function returns the UPC/SKU value of an individual component used in the assembly.

Sales Description of an Assembly Component

GetPeachBOMCompSalesDesv2

This function returns the sales description (as noted in the Maintain Inventory Items window) of an individual component used in the assembly.

Purchase Description of an Assembly Component

GetPeachBOMCompSalesDesv2

This function returns the purchase description (as noted in the Maintain Inventory Items window) of an individual component used in the assembly.

Last Cost of an Assembly Component

GetPeachBOMCompLastCostv2

This function returns the last cost (as noted in the Maintain Inventory Items window) of an individual component used in the assembly.

Assembly Revision Number

(Available only in Sage 50 Quantum Accounting.)

GetPeachRevisionNo

This function returns the current revision for an assembly.

Serial Number Functions

All Serial Number functions are available only in Sage 50 Premium Accounting.

Status of Serial Number

GetPeachSNoStatus

This function returns the current status of the serial number using status text that is displayed in Maintain Inventory Items. You will be required to provide the file path and name, the item record number, and the serial number.

Warranty Expiration Date

GetPeachSNoWarDate

This function returns the expiration date. If the item is not covered under warranty, the field will be returned blank.

Work Ticket Functions

All Work Ticket functions are available only in Sage 50 Quantum Accounting.

Assembly Component's Item ID

GetPeachWTCompID ( Filename, {Worktkt.RecordNumber}, ComponentIndex (1-300 ) )

This function gets the component ID for a row on a particular work ticket.

Assembly Component's Description

GetPeachWTCompDes( Filename, {Worktkt.RecordNumber}, ComponentIndex (1-300 ) )

This function gets the component’s description for a row on a particular work ticket.

Assembly Component's Quantity On Hand

GetPeachWTCompOnHand( Filename, {Worktkt.RecordNumber}, ComponentIndex (1-300 ) )

This function gets the component’s Quantity On-Hand for a row on a particular work ticket.

Assembly Component's Quantity Available

GetPeachWTCompAvail( Filename, {Worktkt.RecordNumber}, ComponentIndex (1-300 ) )

This function gets the component’s Quantity Available for a row on a particular work ticket.

GetPeachWTCompLoc( Filename, {Worktkt.RecordNumber}, ComponentIndex (1-300 ) )

This function gets the component’s location for a row on a particular work ticket.

Assembly Component's UPC

GetPeachWTCompUPC( Filename, {Worktkt.RecordNumber}, ComponentIndex (1-300 ) )

This function gets the component’s UPC for a row on a particular work ticket.

Assembly Component's Sales Description

GetPeachWTCompSalesDes( Filename, {Worktkt.RecordNumber}, ComponentIndex (1-300 ) )

This function gets the component’s Sales description for a row on a particular work ticket.

Assembly Component's Purchase Description

GetPeachWTCompPurchDes( Filename, {Worktkt.RecordNumber}, ComponentIndex (1-300 ) )

This function gets the component’s Purchase description for a row on a particular work ticket.

Unit/Measure Functions

All Unit/Measure functions are available only in Sage 50 Quantum Accounting.

Unit/Measure ID

GetPeachUMID

This function returns the Unit/Measure ID.

Unit/Measure Description

GetPeachUMDescription

This function returns the Unit/Measure Description.

Published: March 21, 2017
© 2017 The Sage Group plc or its licensors. All rights reserved. Privacy Policy | Copyright/Trademarks