=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
-
To retrieve the balance of G/L account 000-1100-00, you would type the following:
=GL(,"Balance","000-1100-00")
-
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")
-
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")
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.
Comments