Overview
=GL (Where, What, Account, Start Period, End Period,..., Data Source)
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
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.
Where
Determines where the GL function places its data.
-
Blank or Omitted
Same as GL("Cell",...).
Will put a single value in the current cell
-
"Cell"
Will put a single value in the current cell
-
"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". -
"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". -
"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:
-
"Balance" - Returns the balance of the GL account specified in the Account parameter
-
"Budget" - Returns the budget of the GL account specified in the Account parameter
-
"AccountName" - Returns the name of the GL account specified in the Account parameter.
-
"Category Name" - Returns either:
- the category name of the category specified in the Category Number parameter
- 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:
-
"Accounts" - Returns a list of account numbers.
-
"Categories" - Returns a list of account category numbers.
-
"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:
-
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.
-
YYYY/M/D
Where YYYY is a four-digit year, M is a month (1-12) and D is a day (1-31)
-
DDDDD.DD
Which is a 5+ digit number interpreted as an Excel serial date
-
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")
Comments