Formula Data Types

Several of the functions require a data type parameter. A data type tells the function where to look for its data. The data type consists of three parts: the table name, the detail table name, and the field name. The detail table and field names are optional, though some functions will not work correctly without them.

The most common way of specifying a data type is using a data alias. Aliases are a simple way to specify the data type rather than identifying the actual filename and fieldname. For example:

Customer is a valid file alias. Review the File Aliases section for a complete file alias list.
Customer.L_Name is an alias file name suffixed by a field name to identify the last name of a customer. Any file field name can be used as a file alias suffix. Review the Alias Suffix section for more details.
Customer.Detail[ARINV].Total is an alias to reference the invoice total. Review the Alias Suffix section for more details.
Customer.Invoices is an alias used to reference invoice totals for a customer. Review the History Alias section for a complete history alias list.

File Aliases:

File aliases only defines the table.

Alias Suffix

File Alias	EBMS File Name	Description
Customer	ARCUST	Customer master record
Employee	PYEMP	Employee master record
GL	GLEDGER	General ledger chart of account master record
Inventory	INVENTRY	Inventory item master record
PayrollTax	PYTAX	Payroll tax master record
ProfitCenter	GLCC	General ledger profit center
Workcode	PYWORK	Payroll work code record
Vendor	APVENDOR	Vendor master record

A suffix is added to the end of a file alias using a period (.). File aliases can contain either of the following suffix's:

Field Name: File aliases defines only the file and can be followed by the actual field name. For example:
customer.L_name. can be used to specify the last name field of a customer
inventry.descr_1 will access the primary description within an inventory item
Review the Standard Technical > Advanced Query Options > Database Fields section for steps to identify a field name within EBMS.
Detail[] - The detail suffix is used to identify and associate a detail table. For example:
arcust.detail[arinv].total identifies the total field within a customer invoice
arinv.detail[arinvdet].ext.weight identifies the extended weight of individual lines
apinv.detail[apinvdet].cost identifies the extended cost within an expense invoice
The detail suffix is used within the EBMSCountDif(), EBMSDetailLookup(), and EBMSDetailSumIf() fuctions. Review the EBMS Formula Syntax section for more details on EBMS functions.

Review Technical > EBMS Tables and Fields for information on identifying EBMS files and field names.

History Aliases

History aliases are used to reference information within the history tabs of EBMS. All history aliases contain a period and are used within the EbmsHistoryTotals() and EbmsHistoryBalance() functions. Review the EBMS Formula Syntax section for more details on these functions.

File Alias	EBMS File Name	Description	Documentation Link
Customer.Invoices	ARCUSDET->PUR_?	Invoices column within the year tab of the customer record.	Customer History Documentation
Customer.Payments	ARCUSDET->PAY_?	Payments column within the year tab of the customer record.	Customer History Documentation
Employee.Hours	PYEMPDET->HOURS_?	Total Hours column within the year tab of the employee record.	Worker History Documentation
Employee.Pay	PYEMPDET->GROSS_A_?	The timecard Pay Date Gross within the year tab of the employee record	Worker History Documentation
Employee.PayPD	PYEMPDET->GROSS_?	The timecard Work Date Gross within the year tab of the employee record.	Worker History Documentation
Employee.Workweeks	PYEMPDET->WORK_?	The Workweeks column within the year tab of the employee record.	Worker History Documentation
GL.Budget	GLBFG->BUDGET_?	The Budget amount for each account accessed by clicking Budget on general tab of account	Budget Documentation
GL.Debit	GLEDDET->DEBIT_?	The Debit column within the year tab of the general ledger account.	General Ledger Account Summary Documentation
GL.Credit	GLEDET->CREDIT_?	The Credit column within the year tab of the general ledger account.	General Ledger Account Summary Documentation
GL.Difference	GLEDET->BAL_?	The Difference column within the year tab of the general ledger account.	General Ledger Account Summary Documentation
GL.YTD	GLEDET->YTD_?	The YTD Balance column within the year tab of the general ledger account.	General Ledger Account Summary Documentation
Inventory.CostIn	INVENTRY->P_COST_?	Purchases Cost column within the year tab of an inventory item.	Inventory History Documentation
Inventory.CostOut	INVENTRY->S_COST_?	The Sales Cost column within the year tab of an inventory item.	Inventory History Documentation
Inventory.QtyIn	INVENTRY->P_QUAN_?	The Purchase Quantity within the year tab of an inventory item	Inventory History Documentation
Inventory.QtyOut	INVENTRY->S_QUAN_?	The Sales Quantity within the year tab of an inventory item.	Inventory History Documentation
Inventory.Sale	INVENTRY->SALEP_?	The Price column within the year tab of an inventory item.	Inventory History Documentation
PayrollTax.Tax	PYTXYEAR->TAX_?	The Tax column within the year tab of a payroll tax.	Payroll Tax History Documentation
PayrollTax.TaxableGross	PYTXYEAR->TXGROSS_?	The Taxable Gross column within the year tab of a payroll tax.	Payroll Tax History Documentation
PayrollTax.TotalGross	PYTXYEAR->GROSS_?	The Total Gross column within the year tab of a payroll tax.	Payroll Tax History Documentation
ProfitCenter.Budget	GLBGT->BUDGET_?	The Budget amount in general ledger multiplied by the profit center percentage	Budget Documentation Profit Center Documentation
ProfitCenter.Debit	GLEDDET->DEBIT_? *GLCCDET->M?	The Debit amount in general ledger multiplied by the profit center percentage	Profit Center Documentation
ProfitCenter.Credit	GLCCET->CREDIT_?	The Credit amount in general ledger multiplied by the profit center percentage	Profit Center Documentation
ProfitCenter.Difference	GLCCDET->BAL_? * GLCCDET->M?	The profit center income and expenses balance shown within the financial statements by profit center	Profit Center Documentation
ProfitCenter.YTD	GLCCDET->YTD_? * GLCCDET->M?	The profit center income and expenses YTD total shown within the financial statements by profit center	Profit Center Documentation
ProfitCenter.Percent	GLCCDET->M?	The monthly percentage shown within the profit center dialog	Profit Center Documentation
Workcode.Hours	PYWRKDET->HRS_?	The Hours column within the year tab of a work code.	Work code history documentation
Workcode.Pay	PYWRKDET->PAY_?	The Pay column within the year tab of a work code.	Work code history documentation
Vendor.Invoices	APVENDET->PUR_?	The Invoices column within the year tab of a vendor record.	Vendor History Documentation
Vendor.Payments	APVENDET->PAY_?	The Payments column within the year tab of a vendor record.	Vendor History Documentation