Skip to main content

GL Function for Dynamics GP

GL Function

Purpose:

Returns the budget, balance, or net change of the Dynamics GP G/L Account of a given company, based on filters.

How-To Video

Introduction to the Dynamics GP GL function

This video provides a step-by-step introduction to the Jet GL function for Dynamics GP.  For more videos information, please see the GL Videos for Dynamics GP

The GL function has many parameters. Thus, the Jet Function wizard (Jfx) is a convenient tool for entering the GL function.

Where

Determines where the GL function places its data.

  1. Blank or Omitted

    Same as GL("Cell",...)

    Will put a single value in the current cell

  2. "Cell"

    Will put a single value in the current cell

  3. "Columns"

    Copies the current column and all of its contents for each unique budget/balance/credit/debit values which match the filter.

    The values returned are sorted.

    To copy more than one column, put "Columns= n " where n is the number of columns to copy. For example, to copy the current column and the next two columns, use "Columns=3".

  4. "Rows"

    Copies the current row and all of its contents for each unique budget/balance/credit/debit values which match the filter.

    The values returned are sorted.

    To copy more than one row, put "Rows= n " where n is the number of rows to copy. For example, to copy the current row and the next two rows, use "Rows=3".

  5. "Sheets"

    Like "Rows" and "Columns" but copies the entire current worksheet.  The name of the copy sheet is set to the value returned by the function.

    "Sheets=n" is not supported.  Only the current worksheet can be copied. If the name is too long or already exists, Jet will create a new name.

What

Determines what type of data the GL function returns.  The options available for this parameter depend upon the Where parameter specified above.

If the Where parameter is " Cell " then the What options are:

  1. "Balance" - Returns the balance of the GL account specified in the Account parameter

  2. "Budget" - Returns the budget of the GL account specified in the Account parameter

  3. "AccountName" - Returns the name of the GL account specified in the Account parameter.

  4. "Category Name" - Returns either:

    1. the category name of the category specified in the Category Number parameter
    2. the category name associated with the GL account specified in the Account parameter.

If the Where parameter is " Rows ", " Columns ", or " Sheets " then the What options are:

  1. "Accounts" - Returns a list of account numbers.

  2. "Categories" - Returns a list of account category numbers.

  3. "SegX" (where X is a segment number) - Returns a list of specific account segments.

Account

Traditional segmented account number filter

Start Period

Specifies the start period or date in one of several formats:

  1. YYYY/PPP

    Where YYYY is the four-digit fiscal year and PPP is the 1 to 3-digit period number.

    This is the fastest and, thus, preferred format.

  2. YYYY/M/D

    Where YYYY is a four-digit year, M is a month (1-12) and D is a day (1-31)

  3. DDDDD.DD

    Which is a 5+ digit number interpreted as an Excel serial date

  4. M/D/YY or D/M/YY (depending on your computer's locale settings)

    Where M is a month, D is a day, and Y is a 2 or 4-digit year.

Specifying a start period and an end period will give you the net change between the first day of the start period and the last day of the end period.

Specifying a start period with no end period will give you the net change between that start date and the present.

Specifying no start period will give you the balance/budget as of the end period.

Specifying no start period or end period will give you the present balance/budget.

You cannot use a range or other special filter here. For information on Period 0, see Fiscal Year Beginning Balances in the GP GL function

Remaining Parameters

  • End Period

    Specifies the end period in the same format as start period. You cannot use a range or other special filter here.

  • Category Number

    Specifies filters for the category number(s).

  • Seg1Filter

    Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

  • Seg2Filter

    Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

  • Seg3Filter

    Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

  • Seg4Filter

    Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

  • Seg5Filter

    Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

  • Seg6Filter

    Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

  • Seg7Filter

    Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

  • Seg8Filter

    Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

  • Seg9Filter

    Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

  • Seg10Filter

    Specifies filters for specific account segments. You can use either an account argument or segment filters, not both.

  • Budget

    Specifies the budget filter (leave blank for "all budgets").

    Note that budgets are associated with a specific year in Dynamics GP.  So, if your budget and fiscal year filters do not coincide, you will get a 0 value.

  • Include Unposted

    "True" to include unposted transactions in Balance queries.  Default is "False".

  • Company

    Company Name.  If this parameter is blank, the default company is used.

  • Data Source

    Data source name. If this parameter is blank, the default data source is used.

Examples of the GL function

To retrieve the balance of G/L account 000-1100-00, you would use the following:. 

=GL("Cell","Balance","000-1100-00")

This GL returns the net change of account 000-1100-00 for the 1st quarter of 2002 

=GL("Cell","Balance","000-1100-00","2002/1","2002/3")

With GP, you can filter on the account number, as a whole, or you can filter on the individual segments of the account number.  So, this GL *also* returns the 1st quarter net change for account 000-1100-00: 

=GL("Cell","Balance",,"2002/1","2002/3",,"000","1100","00")

While some numbers (such as 2002) and the start period and end period do not need quotes, "000" and "00" must be quoted so that Excel does not interpret them as a number and change them to single zeros.

Related Articles

Was this article helpful?

We're sorry to hear that.