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:
- Open Sage 50 and then a Sage 50 company.
- From the Reports & Forms menu, select Crystal Reports.
- Select any report in the list, and double-click it.
- Close both the report and Crystal Reports.
- In Sage 50, from the Reports & Forms menu select Crystal Reports Designer.
The report opens in Crystal Reports.
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:
- From the Standard Toolbar, select the Field Explorer button.
- In the Fields Explorer window, select Formula Fields.
- To create a new formula field, click the New button.
- Enter a name, such as “Amount,” and click OK.
- To access the Sage 50 functions, click Functions in the middle pane, and then double-click Additional Functions.
- Find and double-click peach (u2lpeach.dll).
The Sage 50 functions listed below should appear.
The Crystal Reports Formula Editor appears.
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:
|
|
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.