Created by Harry Lewis, Modified on Fri, 17 Nov 2023 at 12:43 PM by Harry Lewis
- GL Module
- ERP: Acumatica, CEGIC, Haufe x360, JAMIS Prime, MYOB Advanced
Velixo recommends the use of Excel 2023 or Microsoft 365 for this feature. Experimentally, Excel 2010 or higher may be used. See the Prerequisitesfor additional information.
TABLE OF CONTENTS
ACCOUNTSANDSUBACCOUNTWITHHISTORY(Connection, LedgerOrRange, BranchOrRange, AccountOrRange, SubaccountOrRange, StartPeriod, EndPeriod,IncludeInactive, IncludeUnposted, IncludeBranches, UseMasterFinancialCalendar)
The ACCOUNTSANDSUBACCOUNTSWITHHISTORY function uses the following parameters (see our article on Filtering Velixo functions):
The name of the connection as configured in the Connection Manager
The ledger (or multiple ledgers in a delimited list) to be used in determining the list.
The branch code (or range of branch codes) to be used in determining the list
The account code (or range of account codes) to be used in determining the list
The subaccount code (or range of subaccount codes) to be used in determining the list
The starting financial period, in MM-YYYY format
The ending financial period, in MM-YYYY format
TRUE/FALSE indicating whether or not to include inactive accounts and sub-account codes (default is TRUE)
1 - Include posted transactions only (default)
2 - Include Unposted transactions only
3 - Include Posted and Unposted transactions
(note: Transactions with a status of "On Hold" are excluded. When Unposted transactions are included, the following two statuses are included: Balanced, Unposted)
TRUE/FALSE indicating whether or not to include the Branch IDs in the results.
Use Acumatica's Master Financial Calendar instead of the financial calendar defined within the specific tenant associated with the connection being accessed (this can be useful for consolidation reports).
=ACCOUNTSANDSUBACCOUNTSWITHHISTORY("demo","Actual", "PRODWHOLE",,, "12-2019", "12-2019", 1, FALSE)
Lists all account/subaccount pairs with non-zero posted history in the PRODWHOLE branch during the twelfth financial period of 2019
=ACCOUNTSANDSUBACCOUNTSWITHHISTORY("demo", TEXTJOIN(";", TRUE, H3:K3),,,, $A$1, $A$1, 1)
Examines all the ledgers specified in cells H3 through K3 and lists all account/subaccount pairs with non-zero posted history in the financial period listed in cell A1 for any of the specified ledgers.
(Note that when using multiple ledgers, the ledger IDs must be contained in a delimited list)
=ACCOUNTSANDSUBACCOUNTSWITHHISTORY("demo","ACTUAL",, "19000:23000",, "12-2019", "12-2019", TRUE, 1, TRUE)
Lists accounts 19000 through 23000 (and their respective branches and subaccount) with non-zero posted history in the all branches during the twelfth financial period of 2019.
The results are sorted first by branch, then account, and finally by subaccount:
Was this article helpful?
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
We appreciate your effort and will try to fix the article