Formula Syntax

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.   

_________________________________________________________________________________________________________

 

EBMSCountIf(data type, primary query) - Find the number of records within a table

Parameters:

Name

Type

Optional

Explanation

data type

String

No

Data type to count. See Data Types section.

primary query

String

Yes

Query to filter by. See EbmsQuery, EbmsRange, EbmsStartsWith.

Returns:

Type

Explanation

Integer

The number of records found in the specified data type.

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.

Example:
EbmsCountIF("Customer", EbmsQuery("state='PA'")) - Finds all the customers with PA in the State field

The criteria is case sensitive, but the field names are not. The functions will Auto-correct to their proper case.

All string data types need to be in double quotes; Integer types do not.

In the case of using a string value within a query EbmsQuery("state='PA' ") single quotes need to be used for the string, to avoid having the function confused about where the query starts and stops.

_________________________________________________________________________________________________________

 

EBMSLookup(data type, primary query, begin year, begin month, end year, end month) - This function returns the contents of a field.

Parameters:

Name

Type

Optional

Explanation

data type

String

No

Data type to read. See Data Types section.

primary query

String

No

ID or query to filter by. See EbmsQuery(), EbmsRange(), EbmsStartsWith(), EBMSProfitCenterQuery().

Returns:

Type

Explanation

Object

The value of the field, or total in the case of a numeric field.

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:
EbmsField("Customer.f_name", "DOEJOH")

_________________________________________________________________________________________________________


EBMSSumIf(data type, primary query) - This function returns the total value based on the query settings.

Parameters:

Name

Type

Optional

Explanation

data type

String

No

Data type to count. See Data Types section.

primary query

String

Yes

Query to filter by. See EbmsQuery, EbmsRange, EbmsStartsWith.

Returns:

Type

Explanation

Number

The sum total of the data type in the records within the query

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"

_________________________________________________________________________________________________________

 

EBMSDetailSumIf(data type primary query, detail query) - This function is similar to the EBMSSumIf function but accesses the details lines of a document

Parameters:

Name

Type

Optional

Explanation

data type

String

No

Data type to read. See Data Types section.

primary query

String

No

ID or query to filter by. See EbmsQuery(), EbmsRange(), EbmsStartsWith(), EBMSProfitCenterQuery().

detail query

String

No

ID or query to filter detail records by. See EbmsQuery, EbmsRange, EbmsStartsWith.

Returns:

Type

Explanation

Number

The sum total of the data type in the records within the query

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

_________________________________________________________________________________________________________

 

EBMSDetailLookup - This function is used to access the details lines of a document.

Parameters:

Name

Type

Optional

Explanation

data type

String

No

Data type to read. See Data Types section.

primary query

String

No

ID or query to filter by. See EbmsQuery(), EbmsRange(), EbmsStartsWith(), EBMSProfitCenterQuery().

detail query

String

No

ID or query to filter detail records by. See EbmsQuery, EbmsRange, EbmsStartsWith.

Returns:

Type

Explanation

Object

The value of the field, or total in the case of a numeric field.

Notes:
The data type parameter requires a detail table to be specified.
This function is similar to EbmsField, but it allows you to query detail records. See EbmsField for more information.

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

_________________________________________________________________________________________________________

 

EBMSFiscalYear(begin year) - Returns the year information

Parameters:

Name

Type

Optional

Explanation

begin year

Integer

No

The year in which the fiscal year begins.

Returns:

Type

Explanation

Integer

A fiscal year that can be passed to any year parameter.

Notes:
You 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.

Example:

EbmsFiscalYear(0) for current year or EbmsFiscalYear(-1) for last year

_________________________________________________________________________________________________________


EBMSHistoryLookup(data type, primary query, year, month) - Returns information from the history year tabs

EBMSHistoryBalance(data type, primary query, year, month) - Similar to EBMSHistoryBalance but returns the balance

Parameters:

Name

Type

Optional

Explanation

data type

String

No

Data type to read. See Data Types section.

primary query

String

No

ID or query to filter by. See EbmsQuery(), EbmsRange(), EbmsStartsWith(), EBMSProfitCenterQuery().

year

Integer

Yes

The year to find. Defaults to the current year.

month

Integer

Yes

The month to find. Defaults to the last month of the year.

Returns:

Type

Explanation

Object

The balance for the specified year and month.

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.Debit", "01000-000", EbmsFiscalYear(-1))

_________________________________________________________________________________________________________


EBMSHistoryTotal(data type, primary query, begin year, begin month, end year, end month)
- Returns the total of the specified field based on the query and fiscal year range.

Parameters:

Name

Type

Optional

Explanation

data type

String

No

Data type to read. See Data Types section.

primary query

String

No

ID or query to filter by. See EbmsQuery(), EbmsRange(), EbmsStartsWith(), EBMSProfitCenterQuery().

begin year

Integer

No

The first year to find.

begin month

Integer

Yes

The first month to find. Defaults to 1.

end year

Integer

Yes

The last year to find. Defaults to the current year.

end month

Integer

Yes

The last month to find. Defaults to the last month of the year.

Returns:

Type

Explanation

Object

The total for the specified years and months.

Notes:
If the data type is one that uses a 'month 13', the endMonth parameter defaults to 13, otherwise 12.
See EbmsHistoryBalance for an explanation of the year parameters.

Example:
=EbmsHistoryTotal("GL.Credit", EbmsQuery("ACCOUNT = '51000-000'"), 2014, 0, 2015, 1)

This function finds all the GL Entries on the debit side of Account 01000-000 for the Fiscal Year prior to the current Fiscal Year

=EbmsLookup("Customer.l_name", "DRUJAM")

This function finds the last name value of the customer "DRUJAM"

_________________________________________________________________________________________________________

 

EBMSQuery(primary query) - This function is used within another function to specify a dBase query.

Parameters:

Name

Type

Optional

Explanation

primary query

String

No

A dBase query.

Returns:

Type

Explanation

String

A query that can be passed to any query parameter.

Notes:
If the query parameter is not a valid dBase query, EbmsQuery will generate an invalid query which will cause problems in other functions.

Example:
EbmsQuery("id<>'($)'")

_________________________________________________________________________________________________________

 

EBMSProfitCenterQuery( Profit Center, primary query) - This function is used within another function to also query based on the profit center.

Parameters:

Name

Type

Optional

Explanation

Profit center

String

No

Profit center ID.

primary query

String

No

ID or query to filter by. See EbmsQuery(), EbmsRange(), EbmsStartsWith(), EBMSProfitCenterQuery().

Returns:

Type

Explanation

String

A query that can be passed to any query parameter

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)

This example will return 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.

_________________________________________________________________________________________________________

 

EBMSRange(from, to) - This query function is used to identify a range of query values

Parameters:

Name

Type

Optional

Explanation

from

String

No

The first item in the range.

to

String

No

The last item in the range.

Returns:

Type

Explanation

String

A range query that can be passed to any query parameter.

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")

_________________________________________________________________________________________________________


EBMSStartsWith(String text)
- This query function is used to query records based on the beginning values of the field.

Parameters:

Name

Type

Optional

Explanation

starts with text

String

No

A dBase query.

Returns:

Type

Explanation

String

A range query that can be passed to any query parameter.

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")