ACCOUNTSWITHHISTORY 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 ACCOUNTSWITHHISTORY function returns a list of accounts for which there is account history during the specified period(s)

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

TABLE OF CONTENTS

Syntax
Parameters
Examples
- Example 1
- Example 2

Syntax

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

Parameters

The ACCOUNTSWITHHISTORY 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 Calendar	Optional	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

=ACCOUNTSWITHHISTORY("demo","Actual",,,,"12-2019","12-2019",1)

Description

Lists all accounts with non-zero posted history in the Actual ledger during the twelfth financial period of the 2019 fiscal year.

Result

Example 2

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

Description

Lists accounts 19000 through 23000 (and their associated branch) with non-zero posted history in the Actual ledger during the twelfth financial period of the 2019 fiscal year.

Result

Results are sorted first by branch and then by account: