Functions

The following are the User Defined Functions added to Excel when you installed the tool. These are not Service Management functions or components; they are Excel functions. In the example formulas used, you can substitute a cell reference for another item when appropriate. For example, one of the examples below is SLGL(10200, "AUG", 2009, "600"). If "AUG" or "August" is in cell C9, your formula could be SLGL(10200, C9, 2009, "600") instead.

SLDESC

SLDESC returns the description associated with an account code or unit code.

Use it in this way (proper syntax): SLDESC(Account/unit code, [Unit Segment], [site], []).

  • Account is the Chart of Account code or unit code.
    • 0 returns the description for an Account Code. 0 is the default and can be omitted.
    • 1 returns the description of Unit Code 1
    • 2 returns the description of Unit Code 2
    • 3 returns the description of Unit Code 3
    • 4 returns the description of Unit Code 4
  • Site is a valid site name. Site is an optional parameter. If you do not provide this parameter, the default site is used. This parameter can take two types of values, Site and Site Group. The site value is specified by entering the site id in double quotes. The Site Group ID is specified with a prefix "G=Site Group" or "GROUP=Site Group".

    Examples:

    • SLDESC(102000) returns the description associated with the account.
    • SLDESC(600,1) returns the description associated with the unit code 1 (600).
    • SLDESC("101000") returns the description associated with the Chart of Account 101000 from Site OH when OH is the default site.
    • SLDESC("101000",,"MI") returns the description associated with the Chart of Account 101000 from Site MI.
  • FSB is a valid Multi-Financial Set of Books name. You can omit the argument for this optional parameter, however, the use of FSB must provide a valid FSB name as the argument. It has no default value, when the argument is empty or invalid. In this case, it is assumed to be the primary GL. The value is not case sensitive. The FSB argument is ignored when the Unit Segment value is between 1-4. There is no separate unit code definition for FSBs in Service Management.

    Examples:

    • SLDESC("100000", 0, "OH") retrieves the description associated with the Chart of Account "100000" from Site "OH". The value for FSB name is not supplied, so the system retrieves the name from the default COA.
    • SLDESC("101000", 0, "OH", "FSB1") retrieves the description associated with the Chart of Account "101000" from Site "OH" and FSB "FSB1."

SLGL

SLGL returns the period balance amount for the given account code, period, and fiscal year.

Use it in this way (proper syntax): SLGL(Account [: Account], [Period], [Fiscal Year], [Unit Code 1[,...4]], [Type], [Site], [FSB], [Currency]).

All parameters except the Account are optional. The system uses the default value when other parameters are not supplied. To use the default value, the parameter value has to be returned as a blank value supported by Excel using a pair of empty double quotes ("").

  • Account is a valid account code defined in the Chart of Accounts. A range of accounts can be grouped by specifying a starting account and ending account separated by a colon (:). For example, "100100:100500". Cell references can be used when a range is specified with the proper syntax. For example, SLGL(A1&":"&B2, [Period], [Fiscal Year], [Unit Code 1[,...4]], [Type], [site]).
  • Period is an optional parameter. If not supplied, the value from the Period column of the toolbar is used. If the Period column in the toolbar is empty, it picks the current period from Service Management.

    The parameter value can be 1 to 13.

    January (or Jan) to December (or Dec) also can be used to specify the accounting period. If used, Jan or January reads as period 1, and Dec or December reads as period 12. This is useful for users who uses 12 month periods starting from January to December.

  • Fiscal Year is an optional parameter. If not supplied, the value from the Year column of the toolbar is used. If the Year column in the toolbar is empty, it picks the current fiscal year from Service Management. Year should be specified in century format. For example, 2008 or 2018.
  • Unit Code is a valid unit code/sub-account defined in the Unit code table. A maximum of four unit codes (Unit Code 1, Unit Code 2, Unit Code 3 and Unit Code 4) can be used as a single parameter separated by commas. If you don’t want to use a certain unit code in the formula, then use a comma. For example:
    • Use "600" for Unit Code 1
    • Use "600,,,ABC" for Unit Code 1 and Unit Code 4
    • Use ",XYX,30,ABC" for Unit Code 2, Unit Code 3, and Unit Code 4

    You can also use wild cards in formulas for unit codes. For example:

    • Use "100, *, *, *" to return the total of Unit Code 1 (100) and everything for Unit Codes 2, 3, and 4.
    • Use "100, 001, *, *" to return the total of Unit Code 1 (100), Unit Code 2 (001) and everything for Unit Codes 3 and 4.
    • Use "*,*,*,A*" to return the total of all Unit Codes 1, 2, 3, and then any Unit Code 4 that begins with "A".

    You can specify a range of codes in your formula. For example, use "100:200, *, *,*" to return the total of the range of Unit Code 1 from 100 to 200 and everything for Unit Codes 2, 3, and 4.

  • Type is an optional parameter. Select one of these values:
    • Actual or A returns the actual account balance for the given period. Actual is the default value.
    • Budget or B returns the budget amount for the given period.
    • Plan or P returns the planning amount for the given period.

    For example, SLGL(10200, "AUG", 2009, "600"). The Type parameter is returned as blank, and the system takes the default value as Actual.

    Use of empty quotations ("") can be omitted, if there is no subsequent parameter returned with a value. For example, SLGL(10200, "AUG", 2009). The last three parameters Unit Code, Type, and Site are omitted as there is no parameter supplied after them.

  • Site is a valid site name. Site is an optional parameter. If you do not provide this parameter, the default site is used. This parameter can take two types of values, Site and Site Group. The site value is specified by entering the site id in double quotes. The Site Group ID is specified with a prefix "G=Site Group" or "GROUP=Site Group". For example:
    • SLGL(“102000”) returns the current period total.
    • SLGL(“102000”, 9, 2009) returns the period total for period 9 and year 2009.
    • SLGL(“102000”, 9, 2009, "1020") returns the balance of account code 102000 and unit code 1 (1020) for period 9 and year 2009.
    • SLGL(“102000”, 9, 2009, "1020,,ABC") returns the balance of account code 102000, unit code 1 (1020) and unit code 3 (ABC) for period 9 and year 2009.
    • SLGL(“102000”, 13, 2009, "", "B") returns the budget amount for the period of 13 and fiscal year 2009.
    • SLGL(“102200:102500”, 8, 2009,, "Actual") returns total balances of accounts between 102200 and 102500 for the period of 8 and fiscal year 2009.
    • SLGL("101000") returns the amount associated with the Chart of Account 101000 from Site OH when OH is the default site.
    • SLGL("101000", 12,2008,,,"G=ALL") returns the total amount from multiple site(s) which is defined under Site Group Id 'ALL' for the account code 101000 for period 12, fiscal year 2008. For example:
      • 102200 - Cash Account
      • 102300 - Petty Cash
      • 102400 - Cash at Branch x
      • 102500 - Petty Cash Branch Y

      The total of all above can be grouped into one 'cash balance' total using the range option.

    • Example: SLGL("102200:102500",3,2003)

      The reporting group features can be used for more complex range or groups.

  • FSB is a valid Multi-Financial Set of Books name. You can omit the argument for this optional parameter, however, the use of FSB must provide a valid FSB name as the argument. It has no default value, when the argument is empty or invalid. The value is not case sensitive. For example, SLGL("101000", 12,2014,,, "OH", "FSB1") retrieves the period balance from the FSB "FSB1".
  • Currency is an optional parameter, however, use of currency must provide a valid currency code as arguments. The account balance is converted to the given currency by using the conversion method assigned to the account code. This parameter has no default value when the argument is empty or invalid. In this case, no conversion rate is applied. Currency code is not case sensitive. Balance amount from FSB is not translated. In the case that an account code range is used in the formula, the translation method is determined using the starting code of the range. The translated amount could be wrong if the account range contains one or more accounts with a different translation method other than the translation method of the starting code. In this scenario, we recommend you use the SLGRPGL function.

SLGLBAL

SLGLBAL returns the balance amount as on a period and fiscal year for the given account and unit code.

Use it in this way (proper syntax): SLGLBAL(Account [: Account], [Period], [Fiscal Year], [Unit Code 1[,...4]], [Type], [site], [FSB], [Currency]).

  • Account is a valid account code defined in the Chart of Accounts. A range of accounts can be grouped by specifying a starting account and an ending account separated by a colon (:). For example, "100100:100500".

    Cell references can be used when a range is specified with the proper syntax. For example, SLGLBAL(A1&":"&B2, [Period], [Fiscal Year], [Unit Code 1[,...4]]).

  • Period is an optional parameter. If not supplied, the value from the Period column of the toolbar is used. If the Period column in the toolbar is empty, it picks the current period from Service Management

    The parameter value can be 1 to 13.

    January or Jan to December or Dec also can be used to specify the accounting period. If used, January or Jan reads as period 1, and December or Dec reads as period 12. This is useful for users who uses 12 month periods starting from January to December.

  • Fiscal Year is an optional parameter. If not supplied, the value from the Year column of the toolbar is used. If the Year column in the toolbar is empty, it picks the current fiscal year from Service Management. Year should be specified in century format, for example, 1999 or 2009.
  • Unit Code is a valid unit code/sub-account defined in the Unit code table. A maximum of four unit codes (Unit Code 1, Unit Code 2, Unit Code 3 and Unit Code 4) can be used as a single parameter separated by commas. If you don’t want to use a certain unit code in the formula, then use a comma. For example:
    • Use "600" for Unit Code 1
    • Use "600,,,ABC" for Unit Code 1 and Unit Code 4
    • Use ",XYX,30,ABC" for Unit Code 2, Unit Code 3, and Unit Code 4

      You can also use wild cards in formulas for unit codes.

    Example:

    • Use "100, *, *, *" to return the total of Unit Code 1 (100) and everything for Unit Codes 2, 3, and 4.
    • Use "100, 001, *, *" to return the total of Unit Code 1 (100), Unit Code 2 (001) and everything for Unit Codes 3 and 4.
    • Use "*,*,*,A*" to return the total of all Unit Codes 1, 2, 3, and then any Unit Code 4 that begins with "A".

    You can specify a range of codes in your formula. For example, use "100:200, *, *,*" to return the total of the range of Unit Code 1 from 100 to 200 and everything for Unit Codes 2, 3, and 4. SLGLBAL("101000:102000", 12,2008, "100,*,*,*", "A","MI") returns the amount for the account code 101000 through 102000, for the 12th period of 2008, unit code 1 that is equal to 100, everything for unit codes 2-4, and a balance type of actual from the MI site.

  • Type is an optional parameter. Select one of these values:
    • Actual or A returns the actual account balance for all periods up to the given period and fiscal year. This is the default.
    • Budget or B returns the budget amount for all periods up to the given period and fiscal year.
    • Plan or P returns the planning amount for all periods up to the given period and fiscal year.
  • Site is a valid site name. Site is an optional parameter. If you do not provide this parameter, the default site is used. This parameter can take two types of values, Site and Site Group. The site value is specified by entering the site id in double quotes. The Site Group ID is specified with a prefix "G=Site Group" or "GROUP=Site Group".

    Examples:

    • SLGLBAL(“102000”) returns the current balance as of current period.
    • SLGLBAL(“102000”, 9, 2009) returns the current balance as of period 9, 2009.
    • SLGLBAL("101000") returns the amount associated with the Chart of Account 101000 from Site OH when OH is the default site.
    • SLGLBAL("101000", 12,2008,,,"MI") returns the amount for the account code 101000 for period 12, fiscal year 2008, and site MI.
    • SLGLBAL("101000", 12,2008,,,"GROUP=ALL") returns the sum amount for the account code 101000 for period 12, fiscal year 2008, and from all site(s) defined under Site Group ID ALL.
    • SLGLBAL("101000", 12,2008,,,"G=ALL") returns the sum amount for the account code 101000 for period 12, fiscal year 2008, and from all site(s) defined under Site Group ID ALL.
  • FSB is a valid Multi-Financial Set of Books name. You can omit the argument for this optional parameter; however, the use of FSB must provide a valid FSB name as the argument. It has no default value, when the argument is empty or invalid. In this case, it's assumed to be the primary GL. The value is not case sensitive. For example, SLGLBAL("101000, 12,2014,,, "OH", "FSB1") retrieves the accumulated balance from the FSB "FSB1".
  • Currency is an optional parameter, however, use of currency must provide a valid currency code as arguments. The account balance is converted to the given currency by using the conversion method assigned to the account code. This parameter has no default value when the argument is empty or invalid. In this case, no conversion rate is applied. Currency code is not case sensitive. Balance amount from FSB is not translated. In the case that an account code range is used in the formula, the translation method is determined using the starting code of the range. The translated amount could be wrong if the account range contains one or more accounts with a different translation method other than the translation method of the starting code. In this scenario, we recommend you use the SLGRPGL function.

SLNGL

SLNGL returns the balance amount for the given account code and fiscal period with sign reversed. The syntax is the same as SMGL. For example, -1000 is returned as 1000 and vice versa.

SLGLTRAN

SLGLTRAN returns the account balance for any given period other than a fiscal period.

Note: Use of SLGLTRAN should be used sparingly as it is an overloaded function. Use SLGLTRAN only when you need the balance amount other than for a fiscal period. If you want a fiscal-period balance, use SLGL or SLNGL instead. SLGLTRAN is useful for preparing statements with a relatively short term date range like a weekly cash flow or income statement for a week etc.

Use it in this way (proper syntax): SLGLTRAN (Account, [Start Date], [End Date], [Unit Code 1[,...4]], [DebitOrCredit], [site], [FSB]).

  • Account is a valid account code defined in the Chart of Accounts.
  • Start Date is the beginning date of transaction and should be used in the format yyyy-mm-dd; example: 2009-09-17. Date should be used as a string in two quotations. For example, SLGLTRAN(“10200”," 2009-09-01" ," 2009-09-15").
  • End Date is the end date of transaction and should pass in the format yyyy-mm-dd; example: 2009-09-17. Date should be used as a string in two quotations. For example, SLGLTRAN(“10200”," 2009-09-01" ," 2009-09-15").
  • Unit Code is a valid unit code/sub-account defined in the Unit code table. A maximum of four unit codes (Unit Code 1, Unit Code 2, Unit Code 3 and Unit Code 4) can be used as a single parameter separated by commas. If you do not want to use a certain unit code in the formula, then use a comma.

    Example:

    • Use "600" for Unit Code 1
    • Use "600,,,ABC" for Unit Code 1 and Unit Code 4
    • Use ",XYX,30,ABC" for Unit Code 2, Unit Code 3, and Unit Code 4

    You can also use wild cards in formulas for unit codes.

    Example:

    • Use "100, *, *, *" to return the total of Unit Code 1 (100) and everything for Unit Codes 2, 3, and 4.
    • Use "100, 001, *, *" to return the total of Unit Code 1 (100), Unit Code 2 (001) and everything for Unit Codes 3 and 4.
    • Use "*,*,*,A*" to return the total of all Unit Codes 1, 2, 3, and then any Unit Code 4 that begins with "A".

      You can specify a range of codes in your formula.

    Example:

    • Use "100:200, *, *,*" to return the total of the range of Unit Code 1 from 100 to 200 and everything for Unit Codes 2, 3, and 4.
  • DebitOrCredit is the type of the balance. The possible values are Debit or Dr, Credit or Cr, or blank.

    Debit or Dr sums only debit balance.

    Credit or Cr sums only credit balance.

    Blank (the default) returns the net balance amount.

  • Site is a valid site name. Site is an optional parameter. If you do not provide this parameter, the default site is used. This parameter can take two types of values, Site and Site Group. The site value is specified by entering the site id in double quotes. The Site Group ID is specified with a prefix "G=Site Group" or "GROUP=Site Group".

    Examples:

    • SLGLTRAN(“102000”, "2009-08-15", "2009-09-15") returns the balance between the given account and date range.
    • SLGLTRAN(“102000”, "2009-08-15", "2009-09-15",, "Debit") returns the debit total for the given account and date range.
    • SLGLTRAN("101000", "12-01-2009", "12-05-2009") returns the amount for the account code 101000 for the period between 12-01-2009 and 12-05-2009.
    • SLGLTRAN("101000", ", "12-01-2009", "12-05-2009",,,"MI") returns the amount for the account code 101000 for the period between 12-01-2009 and 12-05-2009 and site MI.

      Account codes can be used without double quotes as long as the code is only numeric. This does not work if the account code is alpha numeric. For example, SLGLTRAN(10-2000) returns an error. It should be specified as SLGLTRAN(“10-2000”).

  • FSB is a valid Multi-Financial Set of Books name. You can omit the argument for this optional parameter; however, the use of FSB must provide a valid FSB name as the argument. It has no default value, when the argument is empty or invalid. In this case, it's assumed to be the primary GL. The value is not case sensitive.

SLNGLBAL

SLNGLBAL returns the balance amount for the given account code and fiscal period with sign reversed. The syntax is the same as SLGLBAL. For example, -1000 is returned as 1000 and vice versa.

SLNGLTRAN

SLNGLTRAN returns the balance amount for the given account code and fiscal period with sign reversed. The syntax is the same as SLGLTRAN. For example, -1000 is returned as 1000 and vice versa.

SLPARMS

SLPARMS returns company-specific information such as company name, site, etc.

Use it in this way (proper syntax): SMPARMS("Company") or SMPARMS("Site") or SMPARMS("Period") or SMPARMS("Fiscal_year"). You can add an optional parameter of site at the end.

  • Company: Returns the company name.
  • Site: This site returns the default site.
  • Period: Returns the current period.
  • Fiscal_year: Returns the current fiscal year.
  • Site is a valid site name. Site is an optional parameter. If you do not provide this parameter, the default site is used. This parameter can take two types of values, Site and Site Group. The site value is specified by entering the site id in double quotes. The Site Group ID is specified with a prefix "G=Site Group" or "GROUP=Site Group".

    Examples:

    • SLPARMS(“COMPANY”, “OH”) returns the company name of site OH
    • SLPARMS(“FISCAL_YEAR”, “OH”) returns the current fiscal year site OH
    • SLPARMS(“PERIOD”) returns the current period of the current site

SLPL

SLPL returns the difference amount of revenue and expenditure for a period and fiscal year.

Note: SLPL is used in the Balance Sheet Report templates to calculate the estimated Profit or Loss.

Use it in this way (proper syntax): SLPL([period], [fiscal_year], [site]).

  • Period is an optional parameter. If not supplied, the value from the Period column of the toolbar is used. If the Period column in the toolbar is empty, it picks the current period from Service Management

    The parameter value can be 1 to 13.

    January or Jan to December or Dec also can be used to specify the accounting period. If used, January or Jan reads as period 1, and December or Dec reads as period 12. This is useful for users who uses 12 month periods starting from January to December.

  • Fiscal Year is an optional parameter. If not supplied, the value from the Year column of the toolbar is used. If the Year column in the toolbar is empty, it picks the current fiscal year from Service Management. Year should be specified in century format; example - 1999 or 2009.
  • Site is a valid site name. Site is an optional parameter. If you do not provide this parameter, the default site is used. This parameter can take two types of values, Site and Site Group. The site value is specified by entering the site id in double quotes.
  • IncludeClosingBal is an optional parameter that can take values of Y or N. The default value of N means the summary amount from year-end closing entries are not be considered while calculating the balance of an account. The value Y indicates that the closing entries are included. For example, when IncludeClosingBal = Y, if the Sales Revenue account has a balance of $1000 at year-end, when the year-end closing entries are passed, the balance of this account becomes $0. When IncludeClosingBal = N, the value returned by the function is $1000.

SLPLMTD

SLPLMTD returns the difference amount of revenue and expenditure for a single period.

Note: SLPLMTD is used in the Balance Sheet Report templates to calculate the estimated Profit or Loss.

Use it in this way (proper syntax): SLPLMTD([period], [fiscal_year], [site]).

  • Period is an optional parameter. If not supplied, the value from the Period column of the toolbar is used. If the Period column in the toolbar is empty, it picks the current period from Service Management

    The parameter value can be 1 to 13.

    January or Jan to December or Dec also can be used to specify the accounting period. If used, January or Jan reads as period 1, and December or Dec reads as period 12. This is useful for users who uses 12 month periods starting from January to December.

  • Fiscal Year is an optional parameter. If not supplied, the value from the Year column of the toolbar is used. If the Year column in the toolbar is empty, it picks the current fiscal year from Service Management. Year should be specified in century format; example - 1999 or 2009.
  • Site is a valid site name. Site is an optional parameter. If you do not provide this parameter, the default site is used. This parameter can take two types of values, Site and Site Group. The site value is specified by entering the site id in double quotes.
  • IncludeClosingBal is an optional parameter that can take values of Y or N. The default value of N means the summary amount from year-end closing entries are not be considered while calculating the balance of an account. The value Y indicates that the closing entries are included. For example, when IncludeClosingBal = Y, if the Sales Revenue account has a balance of $1000 at year-end, when the year-end closing entries are passed, the balance of this account becomes $0. When IncludeClosingBal = N, the value returned by the function is $1000.

SLGRPDESC

SLGRPDESC returns the Description associated with a Reporting Group Code.

Use it in this way (proper syntax): SLGRPDESC (Group Name).

  • Group Name is a valid group code defined in the Reporting Group. For example, =SLGRPDESC("COG").

SLGRPGL

SLGRPGL retrieves period balance for a given Account Code, Period and Fiscal-year.

Use it in this way (proper syntax): SLGRPGL (Group Name, [period], [fiscal year], [Type], [Site], [FSB], [Currency]).

  • Group Name is a valid group code defined in the Reporting Group.
  • Period is an optional parameter. If not supplied, the value from the Period column of the toolbar is used. If the Period column in the toolbar is empty, it picks the current period from Service Management.

    The parameter value can be 1 to 13.

    January (or Jan) to December (or Dec) also can be used to specify the accounting period. If used, Jan or January reads as period 1, and Dec or December reads as period 12. This is useful for users who uses 12 month periods starting from January to December.

  • Fiscal Year is an optional parameter. If not supplied, the value from the Year column of the toolbar is used. If the Year column in the toolbar is empty, it picks the current fiscal year from Service Management. Year should be specified in century format; example - 1999 or 2009.
  • Type is an optional parameter. Select one of these values:
    • Actual or A returns the actual account balance for all periods up to the given period and fiscal year
    • Budget or B returns the budget amount for all periods up to the given period and fiscal year
    • Plan or P returns the planning amount for all periods up to the given period and fiscal year

The account COA code and unit code values are picked up from the Group definition. The COA codes and unit codes defined under a group definition is not site specific. These codes are used to retrieve the data from the site specified in the SLGRPGLBAL and SLGRPGL functions. If the code doesn't exist in any site, it simply returns nothing; no error message is displayed.

  • FSB is a valid Multi-Financial Set of Books name. You can omit the argument for this optional parameter, however, the use of FSB must provide a valid FSB name as the argument. It has no default value, when the argument is empty or invalid. In this case, it is assumed to be the primary GL. The value is not case sensitive.
  • Currency is an optional parameter, however, use of currency must provide a valid currency code as arguments. The account balance is converted to the given currency by using the conversion method assigned to the account code. This parameter has no default value when the argument is empty or invalid. In this case, no conversion rate is applied. Currency code is not case sensitive. Balance amount from FSB is not translated. In the case that an account code range is used in the formula, the translation method is determined using the starting code of the range. The translated amount could be wrong if the account range contains one or more accounts with a different translation method other than the translation method of the starting code. In this scenario, we recommend you use the SLGRPGL function.

SLGRPGLBAL

SLGRPGLBAL retrieves balance for a given Account Code, Period and Fiscal-year.

Use it in this way (proper syntax): SLGRPGLBAL (Group Name, [period], [fiscal year], [Type], [Site], [FSB], [Currency]).

  • Group Name is a valid group code defined in the Reporting Group.
  • Period is an optional parameter. If not supplied, the value from the Period column of the toolbar is used. If the Period column in the toolbar is empty, it picks the current period from Service Management.

    The parameter value can be 1 to 13.

    January (or Jan) to December (or Dec) also can be used to specify the accounting period. If used, Jan or January reads as period 1, and Dec or December reads as period 12. This is useful for users who uses 12 month periods starting from January to December.

  • Fiscal Year is an optional parameter. If not supplied, the value from the Year column of the toolbar is used. If the Year column in the toolbar is empty, it picks the current fiscal year from Service Management. Year should be specified in century format; example - 1999 or 2009.

The account COA code and unit code values are picked up from the Group definition. The COA codes and unit codes defined under a group definition is not site specific. These codes are used to retrieve the data from the site specified in the SLGRPGLBAL and SLGRPGL functions. If the code doesn't exist in any site, it simply returns nothing; no error message is displayed.

  • FSB is a valid Multi-Financial Set of Books name. You can omit the argument for this optional parameter, however, the use of FSB must provide a valid FSB name as the argument. It has no default value, when the argument is empty or invalid. In this case, it is assumed to be the primary GL. The value is not case sensitive.
  • Currency is an optional parameter, however, use of currency must provide a valid currency code as arguments. The account balance is converted to the given currency by using the conversion method assigned to the account code. This parameter has no default value when the argument is empty or invalid. In this case, no conversion rate is applied. Currency code is not case sensitive. Balance amount from FSB is not translated. In the case that an account code range is used in the formula, the translation method is determined using the starting code of the range. The translated amount could be wrong if the account range contains one or more accounts with a different translation method other than the translation method of the starting code. In this scenario, we recommend you use the SLGRPGL function.

SLGRPGLYTDBAL

This function is the same as SLGRPGLBAL except the balances are only from the current fiscal-year and do not include any previous year balance.

SLNGRPGLYTDBAL

This function is same as SLGRPGLYTDBAL except the result is reversed to the opposite sign. For example, minus to plus or plus to minus.

SLNGRPGL

SLNGRPGL is the same as SLNGRPGL. The value is returned with reverse sign.

Use it in this way (proper syntax): SLNGRPGL(Group Name, [period], [fiscal year], [Type], [Site]).

SLNGRPGLBAL

SLNGRPGLBAL is the same as SLGRPGLBAL. The value is returned with reverse sign.

Use it in this way (proper syntax): SLNGRPGLBAL(Group Name, [period], [fiscal year], [Type], [Site]).

SLGLYTDBAL

SLGLYTDBAL returns the balance amount computed from the beginning of the given fiscal year until the given period of the same fiscal year for the given account and unit code.

Use it in this way (proper syntax): SLGLYTDBAL (Account [: Account], [period], [Fiscal year], [U1 [:U1] [,...4]], Type, Site, [FSB], [Currency]).

  • Account is a valid account code defined in the Chart of Accounts. A range of accounts can be grouped by specifying a starting account and ending account separated by a “:” (colon). Cell references can be used when a range is specified with the proper syntax. For example, SLGLYTDBAL(A1&":"&B2, ...).
  • Period is an optional parameter. If not supplied, it will default the value from the Period column of Tool Bar. If Period column in the Tool Bar is empty then it picks the current period from Service Management. The parameter value can be, 1 to 13.

    January (or Jan) to December (or Dec) also can be used to specify the accounting period. If used, Jan or January will read as period 1 and Dec or December will read as period 12. This is useful for users who uses 12 months period starting from January to December.

  • Fiscal Year is an optional parameter. If not supplied, it will default the value from the Year column of Tool Bar. If the Year column in the Tool Bar is empty, it picks the current Fiscal Year from Service Management. Year should be specified in century format. example - 1999 or 2009
  • Unit Code is a valid unit code/sub-account defined in the Unit code table. A maximum of four unit codes (Unit Code 1, Unit Code 2, Unit Code 3 and Unit Code 4) can be used as a single parameter separated by commas. If you don’t want to use a certain unit code in the formula, then use a comma.

    Example:

    • Use "600" for Unit Code 1
    • Use "600,,,ABC" for Unit Code 1 and Unit Code 4
    • Use ",XYX,30,ABC" for Unit Code 2, Unit Code 3, and Unit Code 4

    You can also use wild cards in formulas for unit codes.

    Example:

    • Use "100, *, *, *" to return the total of Unit Code 1 (100) and everything for Unit Codes 2, 3, and 4.
    • Use "100, 001, *, *" to return the total of Unit Code 1 (100), Unit Code 2 (001) and everything for Unit Codes 3 and 4.
    • Use "*,*,*,A*" to return the total of all Unit Codes 1, 2, 3, and then any Unit Code 4 that begins with "A".

    You can specify a range of codes in your formula. For example, use "100:200, *, *,*" to return the total of the range of Unit Code 1 from 100 to 200 and everything for Unit Codes 2, 3, and 4.

  • Type is an optional parameter. Select one of these values:
    • Actual or A returns the actual account balance for all periods up to the given period and fiscal year
    • Budget or B returns the budget amount for all periods up to the given period and fiscal year
    • Plan or P returns the planning amount for all periods up to the given period and fiscal year
  • Site is a valid site name. Site is an optional parameter. If you do not provide this parameter, the default site is used. This parameter can take two types of values, Site and Site Group. The site value is specified by entering the site id in double quotes. The Site Group ID is specified with a prefix "G=Site Group" or "GROUP=Site Group".
  • FSB is a valid Multi-Financial Set of Books name. You can omit the argument for this optional parameter, however, the use of FSB must provide a valid FSB name as the argument. It has no default value, when the argument is empty or invalid. In this case, it is assumed to be the primary GL. The value is not case sensitive. For example, SLGLYTDBAL("101000", 12,2014,,, "OH", "FSB1") retrieves the accumulated balance from the beginning of given fiscal-year from the FSB "FSB1".
  • Currency is an optional parameter, however, use of currency must provide a valid currency code as arguments. The account balance is converted to the given currency by using the conversion method assigned to the account code. This parameter has no default value when the argument is empty or invalid. In this case, no conversion rate is applied. Currency code is not case sensitive. Balance amount from FSB is not translated. In the case that an account code range is used in the formula, the translation method is determined using the starting code of the range. The translated amount could be wrong if the account range contains one or more accounts with a different translation method other than the translation method of the starting code. In this scenario, we recommend you use the SLGRPGL function.

SLCURRENCYRATE

SLCURRENCYRATE returns the currency rate of currency for a period and fiscal-year.

Use it in this way (proper syntax): SLCURRENCYRATE (Currency, [period], [fiscal year], [Translation Method], [Site]).

  • Currency is a valid currency code. A valid currency code is the one which is defined in the currency table. Currency has no default value when the argument is empty or invalid. In this case, no conversion rate is applied. Currency code is not case sensitive.
  • Period is an optional parameter. If not supplied, it defaults the value of the Period dropdown from the Tool Bar. If the Period column in the Tool Bar is empty, it picks the current period from Infor Service Management.

    The parameter value can be, 1 to 13.

    January (| Jan) to December (| Dec) also can be used to specify the accounting period. If used, Jan | January will read as period 1 and Dec | December will read as period 12. This is useful for users who uses 12 months period starting from January to December.

  • Fiscal Year is an optional parameter. If not supplied, it will default the value from the Year column of Tool Bar. If Year column in the Tool Bar is empty, it picks the current Fiscal Year from Infor Service Management. Year should be specified in century format. Eg 1999 or 2009
  • Translation Method. Possible values are A (Average), E (End of Period), and C (Current). The default is Current when no value is explicitly supplied.

SLGLBP

SLGLBP returns the budget or planned amount from the primary GL for the given account code, period, and fiscal year. SLGLBP is also used to insert new budget or plan record or update an existing record when used with action type ‘SET’ Unlike for other GL functions, all parameters except the Action Type and Action Value are required. To avoid accidental data loss, the update is not performed even if the Action Type value is ‘SET’. You must click Update Budget and Plan from the Budget and Plan dropdown in the Ribbon Menu to complete the action.

You may see a returned value of 0 if:
  • You do not have "update" privileges on IDO SMChartBPs
  • You are trying to update budget data for a fiscal-year that is already closed
  • You are overwriting an existing budget with zero value.
Use it in this way (proper syntax): SLGLBP(Account, Period, Year, Unit Code, Type, Site, Action, Value)
  • Account is a required parameter and must be a valid account code defined in the Chart of Accounts. The add-in will not perform any action if the given account code does not exist in the Chart of Accounts.
  • Period is a required parameter and must be a valid period defined in the Period table. If not supplied, no action is performed.

    Allowed values are 1-13. January (Jan) to December (Dec) also can be used to specify the accounting period. If used, Jan | January will read as period 1 and Dec | December will read as period 12. This is useful if you use 12 monthly periods starting from January to December.

  • Fiscal Year is a required parameter and must be a valid fiscal year defined in the Period table. If not supplied, no action is performed. The year should be specified in century format; for example, 2009 or 2017.
  • Unit Code is a valid unit code defined in the Unit Code table. A maximum of four unit codes (Unit Code1, Unit Code2, Unit Code3 and Unit Code4) can be passed as a single parameter separated by commas. The Add-in will not perform any action if the given unit codes are not defined or associated with a Chart of Accounts code or if the unit code is defined as not accessible. Wildcard character (*) or range of unit code (100:200) is not supported.

    These two examples indicate that all four Unit codes are blank:

    • =SLGLBP("10000",3,2007,"","B","DALS")
    • =SLGLBP("10000",3,2007,",,,","B","DALS")

    These two examples indicate that Unit Code 1 is 100 and all other unit codes are blank:

    • =SLGLBP("10000",3,2007,"100","B","DALS")
    • =SLGLBP("10000",3,2007,"100,,,","B","DALS")

    This example indicates that just Unit Code 2 is blank:

    • =SLGLBP("10000",3,2007,"100,,XYZ,400","B","DALS")
  • Balance Type is a required parameter. No action is performed if the value is invalid or empty. Possible values are ‘B’, ‘P’, ‘Budget’ and ‘Plan’

    Budget B brings the budget amount for the given period.

    Plan P brings the planning amount for the given period.

  • Site is a required parameter and accepts any valid Site Name listed under the Site drop down from the Add-in Toolbar. No action is performed when an invalid or empty site name is provided.
  • Action Type is an optional parameter and accepts either ‘GET’ or ‘SET’. ‘GET’, which is the default value when omitted, returns the current budget/plan amount. ‘SET’ is used to update the existing budget with new values or budget amount. The new value is specified by the ‘Action Value’ parameter. Users require "read" privileges to read the budget and plan data and "update" privileges to add new budget or update existing data.
  • Action Value is an optional parameter and accepts any integer values which represents the new budget/plan amount when the ‘Action Type’ has been changed to ‘SET’.