Sign Up for Training |
Jet Global Company Site
Community
Downloads
Submit a Request
Give Feedback

Jet Basics - GL Function for GP


Related Articles...

=GL (What, Arg1, Arg2, Arg3,..., Arg22)


Purpose:  Returns the budget, balance, net change, debits or credits of the G/L Account of a given company based on filters.

 

Examples of the GL function

  1. To retrieve the balance of G/L account 000-1100-00, you would type the following:
     =GL(,"Balance","000-1100-00")
  2. If you wanted to know the net change of account 000-1100-00 for the 1st quarter of 2002, you could type the following:
    =GL(,"Balance","000-1100-00","2002/1","2002/3") 
  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 you could also create the last GL function to find the 2002 1st quarter net change for account 000-1100-00 like the following:
    =GL(,"Balance",,"2002/1","2002/3",,"000","1100","00")
    Note that 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 numbers and change them to single zeros.
    The GL Formula has many parameters so the Jet Function wizard (Jfx) is a convenient tool for entering the GL Function.

     

    GL Function Introduction GL Function Next Steps
     
     

    __________________________________________________________________

     

    GL Function Reference (for GP)

    Dynamics GP Parameter

    Description

    Where

    Determines where the GL function returns its data. Options are "Rows", "Columns", "Sheets", and "Cell".

    • "Rows" will insert rows, 
    • "Columns" will insert columns, 
    • "Sheets" will insert sheets, and 
    • "Cell" will put a single value in the current cell

    Additionally, you can use "Rows=x" and "Columns=x" to insert multiple rows or columns for each result, which works just like the NL function. If you leave this argument blank, it defaults to "Cell".

    Note that the options available for the What argument depend on the Where argument. See the next argument for more details.

     What  

    Determines what the GL function returns. If the Where argument is "Cell", then the What options are:

    • "Balance" which returns a G/L balance, 
    • "Budget" which returns a G/L budget, 
    • "AccountName" which returns the name of a G/L account, or 
    • "CategoryName" which returns the name category name for a specified category.

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

    • "Accounts" which will give a list of account numbers, 
    • "Categories" which will give a list of account category numbers, or 
    • "SegX" where X is a segment number and which gives a list of that specific account segment.
    Account

    Traditional segmented account number filter.

    Start Period

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

    • YYYY/PPP where YYYY is the 4 digit fiscal year and PPP is the 1 to 3 digit period number
      (note: this is the fastest and, thus, preferred format)
    • YYYY/M/D where YYYY is a 4 digit year, M is a month, and D is a day; 
    • DDDDD.DD which is a 5+ digit number interpreted as an Excel serial date, and 
    • 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.

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

    ShowQuery

    "True" to show the finhlink string that will be used for drilldown. Defaults to "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.


Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments