ACCOUNTSANDSUBACCOUNTSWITHHISTORY function

Modified on Thu, 14 Sep 2023 at 07:19 PM

Applies to

GL Module
ERP: Acumatica, CEGIC, Haufe x360, JAMIS Prime, MYOB Advanced

Description

The ACCOUNTSANDSUBACCOUNTSWITHHISTORY function returns a dynamic array with sorted groups (lowest to highest) of accounts and subaccounts (first by account, then by subaccount) and (optionally) branches.

It lists account/subaccount/branch groups for which there is account history during the specified period(s).

Velixo recommends the use of Excel 2023 or Microsoft 365 for this feature. Experimentally, Excel 2010 or higher may be used. See the Prerequisites for additional information.

TABLE OF CONTENTS

Syntax
Examples

Syntax

ACCOUNTSANDSUBACCOUNTWITHHISTORY(Connection, LedgerOrRange, BranchOrRange, AccountOrRange, SubaccountOrRange, StartPeriod, EndPeriod,IncludeInactive, IncludeUnposted, IncludeBranches)

The ACCOUNTSANDSUBACCOUNTSWITHHISTORY function uses the following parameters (see our article on Filtering Velixo functions):

Parameter	Required/Optional	Description
Connection	Required	The name of the connection as configured in the Connection Manager
LedgerOrRange	Optional	The ledger (or multiple ledgers in a delimited list) to be used in determining the list.
BranchOrRange	Optional	The branch code (or range of branch codes) to be used in determining the list
AccountOrRange	Optional	The account code (or range of account codes) to be used in determining the list
SubaccountOrRange	Optional	The subaccount code (or range of subaccount codes) to be used in determining the list
StartPeriod	Required	The starting financial period, in MM-YYYY format
EndPeriod	Required	The ending financial period, in MM-YYYY format
IncludeInactive	Optional	TRUE/FALSE indicating whether or not to include inactive accounts and sub-account codes (default is TRUE)
IncludeUnposted (requires Velixo v6.0.403 or higher)	Optional	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)
IncludeBranches (requires Velixo v6.0.403 or higher)	Optional	TRUE/FALSE indicating whether or not to include the Branch IDs in the results.

Examples

Example 1

=ACCOUNTSANDSUBACCOUNTSWITHHISTORY("demo","Actual", "PRODWHOLE",,, "12-2019", "12-2019", 1, FALSE)

Description

Lists all account/subaccount pairs with non-zero posted history in the PRODWHOLE branch during the twelfth financial period of 2019

Result

Example 2

=ACCOUNTSANDSUBACCOUNTSWITHHISTORY("demo", TEXTJOIN(";", TRUE, H3:K3),,,, $A$1, $A$1, 1)

Description

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)

Example 3

=ACCOUNTSANDSUBACCOUNTSWITHHISTORY("demo","ACTUAL",, "19000:23000",, "12-2019", "12-2019", TRUE, 1, TRUE)

Description

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.

Result

The results are sorted first by branch, then account, and finally by subaccount: