The following EBMS functions are available within the Excel if the EBMS Excel ADD-IN is installed. This section describes the syntax for the Excel formulas that are available when the EBMS Add-in is installed into MS Excel.
The following syntex must be followed for EBMS Excel formulas:
-
All text parameters such as queries and other criteria mast be placed within double quotes. Numeric types should not.
-
All text parameters are case sensitive with exception of field names. Functions will Auto-correct to their proper case.
-
In the case of using a text value within a query such bmsQuery("state='PA' "), the text requires single quotes
-
Text values are queried based if the text starts with a value. For example a "ABCD" query would include "ABCDEF" since the value of the 2nd text value starts with the value of the first. Place a space at the end of the text to only query the exact value. Exmple: "ABCD "
Review Excel Formula Overview for some general examples.
Review Technical > EBMS Tables and Fields for information on identifying EBMS files and field names.
Formulas
_____________________________________________________________________________
EBMSCountIf(data type, primary query): Find the number of records within a table
Returns:
Notes:
The data type parameter will ignore the field name, if it is included. Detail records are supported.
The query parameter will not accept an ID.
Examples:
EbmsCountIF("Customer", EbmsQuery("state='PA'")): returns the number of customers within the state of PA
EBMSCountIF('Inventory", EbmsStartsWith( "MM"): returns the number of inventory items that contain a part number that starts with "MM"
EBMSCountIF("ARINVDET", EBMSQuery("Invoice="123456789")): returns the number of records within the sales invoice #12345679
_____________________________________________________________________________
EBMSDetailLookup: This function is used to access the details lines of a document
Returns:
Notes:
A detailed EBMS file name that contains detail records mush to be specified as the data type (first parameter)..
Example:
EbmsDetailLookup("arinv.detail[arinvdet].descr", EbmsQuery("invoice=' 1226'"), EbmsQuery('inven='5038155'")): The function returns the value in the DESCR field of the first matching record that matches the queries above. Note that the invoice field is padded with spaces to 15 characters when using the database script code in the query function since the invoice is right justified.
EbmsDetailLookup("arinv.detail[arinvdet].descr", EbmsQuery("invoice=' 1226'"), EbmsQuery('inven='5038155'")): A simpler function that accomplishes the same purpose is as follows:
_____________________________________________________________________________
EBMSDetailSumIf(data type primary query, detail query): This function is similar to the EBMSSumIf function but accesses the details lines of a document
Returns:
Notes:
The data type parameter requires a detail table to be specified.
This function is similar to EbmsSumIf, but it allows you to query detail records. See EbmsSumIf for more information.
Example:
EbmsDetailSumIf("arinv.detail[arinvdet].quan", EbmsQuery("id='DOEJOH'"), EbmsQuery('inven='DUCTAP'")): This function returns the sum of the quan field in the sales order detail table, where the customer ID = DOEJOH (primary query) and the Inventory ID (INVEN field) = "DUCTAP" (secondary Query)
EbmsDetailLookup("arinv.detail[arinvdet].descr", EbmsQuery("invoice=' 1226'"), EbmsQuery('inven='5038155'")): The function returns the value in the DESCR field of the first matching record that matches the queries above
Note that the invoice field is padded with spaces to 15 characters
_____________________________________________________________________________
EBMSFiscalYear(begin year): Returns the year information to feed into a function parameter that requires a year and causes the following month parameter to look for a fiscal month rather than a calendar month
Returns:
Notes:
The user can pass a relative year up to 5 years in the future. 0 is this year, 1 is next year, and -1 is last year.
The results of this function have no value except to pass to a year parameter of another function. The EBMSFiscalYear() function must be nested within another function such as EBMSHistoryTotal() or EBMSHistoryBalance()
Example:
EbmsFiscalYear(0) for current year or EbmsFiscalYear(-1) for last year and the month parameter of the same function will process the fiscal month instead of the calendar month. Example using the same function:
EBMSHistoryTotal( "GL.Difference", EbmsRange("50000-000", "59999-999", EBMSFiscalYar(0), 4, EBMSFiscalYear(0), 6) will return the monthly total of fiscal months 4,5, and 6 which will be different for any financial system with a fiscal year different than the calendar year.
EBMSHistoryTotal( "GL.Difference", EbmsRange("50000-000", "59999-999", 2019, 4, 2019, 6) will return the month total of calendar months of April, May, June (months 4-6)
_____________________________________________________________________________
EBMSHistoryBalance(balance data type, primary query, year, month): Returns the balance for the specified year and month as shown on a history tab
Returns:
Notes:
If the data type is one that uses a 'month 13', the month parameter defaults to 13, otherwise 12.
The year parameter will accept a calendar year or a fiscal year. A relative calendar year can be specified in the same way as for the EbmsFiscalYear function. See EbmsFiscalYear for information on fiscal years.
Example:
EbmsHistoryBalance("GL", "01000-000", EbmsFiscalYear(-1), 3) The balance of the 3rd fiscal month of last year will be returned rather than the month of March (3rd calendar month) because the EBMSFiscalYear() function was used with a paremeter of -1 (last year).
EBMSHistoryBalance( "Customer", "DOEJOH" ) will return the current accounts receivable balance for customer with an ID of DOEJOH.
EBMSHistoryBalance( "Customer", "DOEHOH", 2019, 4 ) will return the balance for the customer at the 4 month of year 2019.
EBMSHistoryBalance("Vendor", "ACE", 2017, 7) will return the balance for a vendor
EBMSHistoryBalance("Inventory", "DIRSHO", 2017, 1) will return the total inventory value at the end of January, 2017.
EBMSHistoryBalance("INVENTRY.QUAN_", "DIRSHO", EBMSFiscalYear(0), 1) will return the quantity inventory volume at the end of the first fiscal month for the current fiscal year. Note that month 1 is identified as January if the fiscal year is same as calendar year or if the EBMSFiscalYear() function is not used to identify the fiscal year.
_____________________________________________________________________________
EBMSHistoryTotal(data type, primary query, begin year, begin month, end year, end month): Returns the total of the specified field based on the query, year, and month range
Returns:
Notes:
If the data type is one that uses a 'month 13', the endMonth parameter defaults to 13, otherwise 12.
The year parameter will accept a calendar year or a fiscal year. A relative calendar year can be specified in the same way as for the EbmsFiscalYear function. See EbmsFiscalYear for information on fiscal years.
Example:
EBMSHistoryTotal( "GL.Difference", "50000-000", 2015, 1, 2015,13) This function will return the general ledger account difference for the entire year (months 1 through 13)
EBMSHistoryTotal( "GL.Difference", EbmsQuery("Account='50000-000'"))) This syntax will return the same value as the previous example within the entire current fiscal year. Note that the text within the query text is wrapped in single quotes (') while the entire query string is within double quotes (").
EBMSHistoryTotal( "GL.Difference", EbmsRange("50000-000", "59999-999", 2015, 4, 2015, 6) This syntax will return the sum cost of all general ledger accounts between 50000-000 and 59999-999 for the 2nd quarter of the calendar year.
EBMSHistoryTotal( "GL.Difference", EbmsStartsWith("5"), EBMSFiscalYear(-1)) This syntax will return the sum cost of all the general ledger accounts that start with 5 for last fiscal year.
EBMSHistoryTotal("ProfitCenter.Credit", EbmsProfitCenterQuery("PCA","50000-000"), 2015,6, 2015, 6) This function will return the account balance amount for account 50000-000 for the 6th month in 2015. The amount is adjusted based on the percentage allocation for profit center PCA.
EBMSHistoryTotal("ProfitCenter.Difference", EbmsProfitCenterQuery(EbmsRange("PCA","PCZ"),EbmsRange("50000-000","59999-999")), EbmsFiscalYear(0),, EbmsFiscalYear(0),) This function will return the total amount for all accounts between 50000-000 and 59999-999 for all months of the current fiscal year. The amount is adjusted based on the percentage allocation for all profit centers within the PCA and PCZ range. Review the General Ledger > Departments and Profit Centers > Creating New Profit Centers of this documentation for more details on the profit center allocation settings.
EBMSHistoryTotal("GL.Budget", EBMSRange("50000-000","59999-999"), EBMSFiscalYear(0),4, EBMSFiscalYear(0), 6) This syntax will return the budget amount all general ledger accounts between 50000-000 and 59999-999 for the 2nd quarter of the current fiscal year.
EBMSHistoryTotal("ProfitCenter.Budget", EbmsProfitCenterQuery("PCA",EBMSRange("50000-000","59999-999")), EBMSFiscalYear(0),4, EBMSFiscalYear(0), 6) This syntax will return the budget amount for profit center PCA within accounts 50000-000 and 59999-999 for the 2nd quarter of the current fiscal year.
_____________________________________________________________________________
EBMSLookup(data type, primary query, begin year, begin month, end year, end month): This function returns the contents of a specified field
Returns:
Notes:
If more than one record is found that matches the filter criteria, EbmsField returns the first record's value, unless the field is a numeric field, in which case it returns the total of all the values.
Example:
EBMSLookup( "Vendor.L_name", "QUADIS") This function will return the Last Name (main name) of a vendor with an ID of QUADIS.
EBMSLookup( "APVENDOR.L_name", "QUADIS") This example replaces the data type alias Vendor from example A, with the actual file name. The primary query will use the natural key such as the vendor ID if no query function is used.
EBMSLookup( "CUSTOMER.L_name", EbmsQuery("ID='DOEJOH'")) This example will return the last name of a customer.
_____________________________________________________________________________
EBMSProfitCenterQuery( Profit Center, primary query): This function is used within another function to also query based on the profit center
Returns:
Notes:
This function will return the amount calculated from the profit center percentages allocated to each account in the primary query. Review the General Ledger > Departments and Profit Centers > Creating New Profit Centers section of this documentation for more details.
Example:
EBMSHistoryTotal("ProfitCenter.Difference", EbmsProfitCenterQuery("PCA",EbmsRange("50000-000","59999-999")), 2015,6,2015,6) returns the general ledger amount for all accounts between 50000-000 and 59999-999 for the 6th month of 2015.
EbmsHistoryTotal("ProfitCenter.Debit", EbmsProfitCenterQuery("PCA",EbmsRange("60000-000","62999-999")), EbmsFiscalYear(-1),, EbmsFiscalYear(-1),) This example will return the debit amount for all accounts between 60000-000 and 62999-999 for all months within last fiscal year.
EBMSHistoryTotal("GL.Budget", EBMSRange("50000-000","59999-999"), EBMSFiscalYear(0),4, EBMSFiscalYear(0), 6) This syntax will return the budget amount all general ledger accounts between 50000-000 and 59999-999 for the 2nd quarter of the current fiscal year.
EBMSHistoryTotal("ProfitCenter.Budget", EbmsProfitCenterQuery("PCA",EBMSRange("50000-000","59999-999")), EBMSFiscalYear(0),4, EBMSFiscalYear(0), 6) This syntax will return the budget amount for profit center PCA within accounts 50000-000 and 59999-999 for the 2nd quarter of the current fiscal year.
_____________________________________________________________________________
EBMSQuery(primary query): This function is used within another function to specify a database query
Returns:
Notes:
If the query parameter is not a valid database query, EbmsQuery will generate an invalid query which will cause problems in other functions.
Example:
EbmsQuery("id<>'($)'") This function allows the user to create a query script described within Technical > Advanced Query Options. Note that the text within the query text is wrapped in single quotes (') while the entire query string is within double quotes (").
The EBMSQuery function may contain some very complex code. Review the Excel Add-in Troubleshooting section if you receive a -460 database error for any cell that has this function nested anywhere within the formula.
_____________________________________________________________________________
EBMSRange(from, to): This query function is used to identify a range of query values
Returns:
Notes:
If the item in the from parameter comes after the item in the to parameter, from and to will be switched.
Range queries are a special form of query that can be used as a regular query. They search the ID's and get only the items between the two, inclusive.
Example:
EbmsRange("00000-000", "19999-999") - All asset financial accounts with codes that are equal to or less than 19999-999
EbmsRange("A", "AZZZZZZZZZZZZZ") - Used to identify a range of inventory items or customer IDs that are within the specified range
_____________________________________________________________________________
EBMSStartsWith(String text): This query function is used to query records based on the beginning values of the field
Returns:
Notes:
This creates a range query that gets all the items that start with a specified string. See EbmsRange for more information on range queries.
Example:
EbmsStartsWith("A") - any code that starts with A: Note that this criteria is case sensitive.
EbmsStartsWith("REM") - any code that starts with REM
_____________________________________________________________________________
EBMSSumIf(data type, primary query): This function returns the total value based on the query settings
Returns:
Notes:
This function returns the sum total of the records within the primary query.
Do not use the ID query with this function. Always use a primary query function.
Example:
EbmsSumIf("arinv.total",EbmsQuery("ID = 'GROFAR'")) This function totals the "Total" field of the invoices for the customer "GROFAR"
-
EBMSSumIf( "Inventory.Cost", "DIRSHO") This function will return the inventory cost of the inventory item with an ID of DIRSHO. The user can enter a data type alias inventory instead of the file name, as shown in example B. Review the data type alias details in the EBMS Formula Data Types section.
-
EBMSSumIf( "INVENTRY.Cost", "DIRSHO") This example replaces the data type alias inventory from example A, with the actual file name. The primary query will use the natural key such as the inventory ID if no query function is used. The more flexible query is described in example C.
-
EBMSSumIf( "INVENTRY.Cost", EbmsQuery("ID='DIRSHO'")) This syntax will return the same value as the previous two examples. The file name INVENTRY and the field name Cost are separated by a period. Note that the text within the query text is wrapped in single quotes (') while the entire query string is within double quotes (").
EBMSSumIf( "INVENTRY.Cost", EbmsRange("DA", "DZZZ")) This syntax will return the sum cost of all inventory items between DA and DZZZ..
EBMSSumIf( "INVENTRY.Cost", EbmsStartsWith("D")) This syntax will return the sum cost of all the inventory items that start with D.
EBMSSumIf("pytmdet.hours", EbmsQuery("ID='"&A2&"'.and.Dtos(DATE)='"&B1&"'")) This example returns the hours worked by employee this year. MS Excel cell A2 contains the Employee’s ID e.g. “DOEJOH” and “B1” is the year e.g. “2016"
EbmsSumIf("pytmdet.hours",EbmsQuery("ID='"&A2&"'.and. Dtos(DATE) >= '"&TEXT(A1-WEEKDAY(A1,1)+1,"yyyymmdd")&"'.and. Dtos(DATE) <= '"&TEXT(A1,"yyyymmdd")&"' ")) This example returns the hours worked by an employee this week. Note that MS Excel cell A2 contains the Employee’s ID e.g. “DOEJOH” and “A1” is Todays date i.e. “=TODAY()”
The EBMSQuery function may contain some very complex code. Review the Excel Add-in Troubleshooting section if you receive a -460 database error for any cell that has this function nested anywhere within the formula.
_____________________________________________________________________________