ACCOUNTSANDSUBACCOUNTSWITHHISTORY function

Created by Harry Lewis, Modified on Tue, 16 Jul at 9:33 AM by Gabriel Michaud

Applies to:

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

 

 

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 Prerequisitesfor additional information.

 


TABLE OF CONTENTS



Syntax

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

 


Parameters


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

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

Optional

TRUE/FALSE indicating whether or not to include the Branch IDs in the results.

UseMasterFinancial CalendarOptional

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).


Possible values:

True

False (default)



Examples


Example 1 - data from a specific branch

 

=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

acct_pair3.png

Example 2 - examine multiple ledgers

 

=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)

 

acct_pair4.png



Example 3 - show a subset of accounts with subaccounts and branches

 

=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:

 

history_group01.png


Example 4 - change column order and sort order


The Excel SORT and CHOOSECOLS functions provide substantial control over how data is displayed.

 

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

Description

Display the same data as in Example 3, but change the order of the columns (Account, Subaccount, Branch) and then sort on the new ordering.

 

Result

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



Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article