Values
"Value" is one part of a function. It can consist of several elements separated by colons. The syntax is as follows:
FUNCTION("Sign","Value","Account Number")
Example
The following value consists of the TYPE, PERIOD, and YEAR elements:
- ACT("BR:PER4:YR0","200") returns the balance report for period 4 of the current year for account 200.
Elements of a value
- TYPE
- SUB
- YEAR
- PERIOD
- ENTITY
- FOREIGN EXCHANGE
- ROUNDING
- PRIOR YEAR ADJUSTMENTS
- DISCRETE
- SIGN
- USESIGN
- CALCULATION SYNTAX
- FILTERED BALANCES
- CUSTOM BALANCES
- MAP/GROUP ADJUSTMENT NETINC AMOUNT
- EXTENDED INFORMATION
TYPE
The element representing the type of balance or adjustment to use.
Element |
Type of Balance or Adjustment |
---|---|
BU |
Balance unadjusted (no adjustments) |
BA |
Balance adjusted (normal adjustments) |
BR |
Balance report (normal + reclassifying) |
BC |
Balance consolidated (normal+ reclassifying + eliminating) |
BB |
Balance budget (See note below) |
BF |
Balance forecast (See note below) |
BTF |
Balance consolidated + "Tax - Federal Adjustments" |
BTS |
Balance consolidated + "Tax - State Adjustments" |
BTC |
Balance consolidated + "Tax - City Adjustments" |
AN |
Adjustments normal |
AE |
Adjustments eliminating |
AR |
Adjustments reclassifying |
AF |
Adjustments federal tax |
AS |
Adjustments state tax |
AC |
Adjustments city tax |
AB |
Adjustments other basis |
AU |
Adjustments unrecorded - Factual |
AL |
Adjustments unrecorded - Projected |
AG |
Adjustments unrecorded - Judgmental |
A2 |
Adjustments other adjusting 2 |
A3 |
Adjustments other adjusting 3 |
OBA |
Other basis adjustments in any balance |
OA2 |
Other adjusting entry 2 in any balance |
OA3 |
Other adjusting entry 3 in any balance |
If a type is not specified, the default balance used is "BC" (consolidated balance).
Note: Functionality of the BB and BF elements are further modifiable with a third letter to specify the adjustment type, similar to the standard balance. U, A, R, or C may be used to specify the adjustment type, meaning the BBU element would link to the unadjusted balance budget, or BFA would link to the normal adjusted forecast balance.
Example
- MAP("BA:YR2","111") returns the adjusted balance.
- MAP("YR2","111") returns the consolidated balance because the TYPE element was not specified.
- MAP("AN:YR0","111") returns the total of normal adjusting journal entries to map number 111.
- MAP("AB","102") returns the other basis adjustment only for account 102.
- MAP("BR:OBA","102") includes the other basis adjustments in the balance for account 102.
You can use an ADJ modifier element to return only the adjustments value for the balance type selected. This modifier can be added as:
- ACT("AN:ADJB","114") returns the normal adjustments-only budget balance for account 114.
- ACT("AR:ADJF","121") returns the reclassifying adjustments-only forecast balance for account 121.
- ACT("AN:ADJCAA","114") returns the normal adjustments-only custom balance (AA) for account 114.
SUB
The element representing the leadsheet, map or group number in a total to use.
Example
- MAP("BC:SUB","100") returns the balance of all mapping numbers beginning with "100". It evaluates only map numbers matching the mapping mask of the map number provided. This means it returns the sum of the balances for 100.1, 100.2 etc. and ignores the balances for map numbers 1000, 1001, etc.
- GRP("3", BA:SUB","4") returns the final balance for all accounts assigned to grouping 3 group numbers that begin with the number 4. In other words, this function links to balances from group numbers 4, 4.10, 4.20, and so on.
YEAR
The element representing the current, prior or future year balance to use.
Element |
Type of Balance |
---|---|
YR0 |
Current year for all balance types including balance forecast (BF). |
YR1 |
First prior year for all balance types except BF (see types above). First future year for balance forecast (BF). |
YR2 |
Second prior year for all balance types except BF (see types above). Second future year for balance forecast (BF). |
YR3 |
Third prior year for all balance types except BF (see types above). Third future year for balance forecast (BF). |
YR4 |
Fourth prior year for all balance types except BF (see types above). Fourth future year for balance forecast (BF). |
Example
- MAP("BA","111") returns the adjusted balance for the current year.
- MAP("BA:YR2","111") returns the adjusted balance for the second prior year.
- MAP("BF:YR2","111") returns the forecast balance for the second future year.
PERIOD
The element representing the generic period, reporting period or prior period balance to use.
Note: Select either one generic element or one reporting period element, not both.
Generic Period
Element |
Type of Balance |
---|---|
PER |
The active period |
PER0 |
Start of the year balance (opening balance in working trial balance). |
PERx |
Where x = the number of the period in the particular reporting sequence. |
If no period is specified, the active period is used. If period 13 is specified but there is no period 13 in the client file, Working Papers/Time assumes 13 represents the final period in the file, regardless of the reporting period type (i.e. monthly, quarterly, etc.).
Example
- MAP("YR0:PER0","111") returns the opening balance of the current year.
- MAP("BA:YR0:PER4","111") returns the adjusted balance for period 4 of the current year.
Reporting Period (Bucket)
Element |
Type of Balance |
---|---|
Y |
Yearly |
S |
Semi annual |
T |
Thirdly |
Q |
Quarterly |
I |
Bi-monthly |
M |
Monthly |
F |
Four weekly (thirteen periods) |
R |
Random |
For each of the bucket symbols, an optional numeric period can be specified (for example, "Q3", for quarter 3).
0 = the start of the year balance
X = Period number in the reporting sequence
If no bucket is specified, the active bucket is used.
Note: An optional '@' symbol can be placed in front of the reporting period element as it allows users to extract the reporting period from the FP database (for example, using the PERSTR() function), rather than typing it in manually. In this case, either B or I can be used to represent bi-monthly balances. This allows, for example, formulae of the form:
GRP("M","BR:YR0:@" + PERSTR(),entity(entid,"1.0000.000"))
Note: Entering an invalid period qualifier (that is, one that is not valid in the Engagement Properties) returns a balance of 0. For example, for a client file with a monthly period, entering M14, an invalid period number, returns a zero. Entering Q7 in a quarterly file returns a zero.
Example
- MAP("BA:YR0:Q","111") returns the active quarter period adjusted balance for the current year.
- MAP("BA:YR2:M4","111") returns the month 4 adjusted balance for prior year 2.
Prior Period
Element |
Type of Balance |
---|---|
PPx |
PPx returns the balance for the period that is x periods before the current one. |
Example
- MAP("PP8","111")
If the current period is 6 (that is month 6 of the current year), then PP8 returns the balance from period 10 of the previous year, which was 8 periods ago.
ENTITY
The element representing the entity to use.
Element |
Type of Balance |
---|---|
PAR |
Limits the balances to the parent entity only in a consolidated file. |
The default is to show consolidated balances.
Example
Consider the following consolidation structure:
- MAP("BA:YR0:PAR","111") returns the current year adjusted balance for only parent, when the context entity selector is set to parent.
- MAP("BA:YR0:PAR",Entity("SUB1","111")) returns the current year adjusted balance for only SUB1.
- MAP("BA:YR0",Entity("SUB1","111")) returns the current year adjusted balance for SUB1, DIV1, and DIV2.
For more information on linking balances with consolidated files, see linking consolidated files.
FOREIGN EXCHANGE
The element representing the foreign exchange to use or retrieve.
Element |
Type of Balance |
---|---|
FX |
Use this property to apply foreign exchange to the balance. The default element is the balance without the exchange rate. |
FXR |
Use this property to retrieve an account's foreign exchange rate for a specific period, reporting bucket (quarterly, monthly, yearly, etc.), and balance type (normal, budget, forecast). |
Example
- MAP("BA:YR0:FX","111") returns the current year adjusted balance with foreign exchange applied.
- ACT("M11:FXR","311") returns the foreign exchange rate for account 311 in monthly period 11.
- ACT("BB:Q2:FXR","101") returns the budget foreign exchange rate for account 101 in quarter two.
ROUNDING
The element representing the rounding to use.
Element |
Type of balance |
---|---|
RND |
Use this property to round the final balance to the nearest whole number. When rounding a group of accounts, RND sums all the accounts and then rounds the sum of the group. |
RNDACT |
Use this property to round each account individually. When used within a group, each account is rounded and then the grouping is totalled. |
RNDENT |
Use this property to enforce rounding by entity. The balances related to the entity will be summed and then rounded. |
RNDTOT |
Use this property to enforce rounding on the total of a MAP() or GRP() function. The balances assigned to the map or group will be totalled first and then rounded. |
Example
- MAP("BA:YR0:RND","111") returns the current year adjusted balance rounded to the whole number.
For RND and RNDACT, you can designate the rounding level to apply by adding a value after the element.
Value |
Rounding level |
---|---|
1 |
Set rounding to ones (1). |
2 |
Set rounding to thousands (1,000). |
3 |
Set rounding to millions (1,000,000). |
4 |
Set rounding to billions (1,000,000,000). |
5 | Set rounding to ten thousands (10,000). |
6 | Set rounding to hundred millions (100,000,000). |
Example
- MAP("BA:YR0:RND3","111") returns the current year adjusted balance rounded to the whole number in millions.
PRIOR YEAR ADJUSTMENTS
The element representing the adjustment type to use.
Element |
Type of Adjustment |
---|---|
PAJ |
Use this property for prior year adjustments made from Adjusting Journal Entries. |
YAJ |
Use this property for prior year adjustments made during year end close or directly in the trial balance adjustments. |
Example
- MAP("AN:YR1","111") returns all the normal adjustments of the first prior year for account 111.
- MAP("AN:YR1:PAJ","111") returns only the normal prior year adjustments of the first prior year for account 111.
- MAP("AN:YR1:YAJ","111") returns only the normal year end close adjustments of the first prior year for account 111.
DISCRETE
The element representing the current active period to use.
There are two ways of denoting monthly balances in CaseView linkage functions, Continuous and Discrete. Continuous refers to the summation of all periods (up to and including the current active period) while Discrete only deals with the current active period.
If you are working on a monthly file and you're currently in period four, the current year continuous balance will include all amounts and transactions from periods one, two, three and four. The discrete balance will show only period four activity.
Element |
Type of Balance |
---|---|
DISC |
Balance for the stated period only (does not include prior periods). |
The default element is continuous.
Example
- MAP("BC:YR0:M4","111") returns a year to date balance up to the end of the fourth month for map number 111.
- MAP("BC:YR0:M4:DISC","111") returns the total of all transactions in the fourth month for map number 111.
SIGN
The element representing the sign assigned to the map and group number functions to use.
Element |
Type of Balance |
---|---|
SIGN |
Used if Use Map/Group Sign in Linkage is disabled and you want to use map/group sign in the cell calculation. |
ACTSIGN |
Used if Use Map/Group Sign in Linkage is enabled and you don't want to use the map/group sign in the cell calculation. |
Example
- MAP("BR:SIGN,"102") returns the report balance posted to map no. 111 using the sign of the map no. rather than the sign of the accounts that are assigned to the map no. For example, if map no. 111 contains account 102 which is a debit account with a balance of 200 but map no. 111 has a credit sign then the balance returned would be -200.
USESIGN
The element representing the sign of the account (Debit/Credit) and whether it should be used to flip the sign of a balance.
Element |
Type of Balance |
---|---|
USESIGN |
Used if Ignore Sign Field in Linkage is enabled and you don't want to ignore the sign in the cell calculation. |
RAW |
Used if Ignore Sign Field in Linkage is disabled and you want to ignore the sign in the cell calculation. |
Example
- ACT("BR:M4:RAW","102") returns the report balance for period 4, of the monthly balances bucket, posted to account 102 and ignores the sign of the account when calculating the balance.
Notes:
- Group 1 treats all calculations as RAW, regardless of the specified element. To override this behavior, add LSSIGN to the calculation.
- Map/group cell calculations will use the sign (debit/credit) of the first account assigned to the map/group.
CALCULATION SYNTAX
The element representing the calculation syntax to use.
Calculations are in the form of:
FUNCTION(EXPRESSION1, EXPRESSION2, EXPRESSION3, &)
Each expression can refer to a cell or group of cells.
Example
For a file in which the function ACT("BA:YR0:Q2","102") returns a value of 60,000, having the following cell values and using the calculation syntax returns that same value in this way:
Cell A |
Cell B |
Cell C |
Cell D |
Calculation |
Result |
---|---|---|---|---|---|
Cell A3 A:YR0:Q2 |
|
|
Cell D3 102 |
ACT(a3,d3) |
60,000 |
Cell A4 BA |
Cell B4 YR0 |
Cell C4 Q2 |
Cell D4 102 |
ACT(a4+":"+b4+":"+c4,d4) |
60,000 |
Cell A5 BA:YR0 |
Cell B5 Q2 |
|
Cell D5 102 |
ACT(a5+":"+b5,d4) |
60,000 |
Cell A6 BA: |
Cell B6 YR0: |
Cell C6 Q2 |
Cell D6 102 |
ACT(a6+b6+c6,d4) |
60,000 |
FILTERED BALANCES
Balance ID "filt" allows an optional dBase filter to be added to the balance formula.
"FILT=dBase Filter Expression"
Example
To return the YTD "report" balance of mapping 111, for month 12, filtered on accounts where GROUP4=401, use the following the calculation:
- MAP("BR:M12:FILT=GROUP4=MASK('4','401')","111")
CUSTOM BALANCES
The element representing the custom balance to use.
The custom balance element consists of three letter codes always starting with C and ending with the two letter custom balance identifier. For custom balance AA, the element would be CAA.
Custom balances identifiers are created in Tools | Options | Custom Balances in Working Papers.
Specifying the year for custom balances is the same as other balance types:
Element |
Type of Balance |
---|---|
YR0 |
Current year for all balance types including balance forecast (BF). |
YR1 |
First prior year for all balance types except BF(see types above). First future year for balance forecast (BF). |
YR2 |
Second prior year for all balance types except BF (see types above). Second future year for balance forecast (BF). |
YR3 |
Third prior year for all balance types except BF (see types above). Third future year for balance forecast (BF). |
YR4 |
Fourth prior year for all balance types except BF (see types above). Fourth future year for balance forecast (BF). |
Example
The following is an example of using the custom balance element where AA is the custom balance identifier:
- ACT("CAA:YRO","101") will display the current year custom balance with identifier AA for account 101.
Specifying the adjustments for custom balances is the same as other balance types:
Element |
Type of Balance |
---|---|
CAAU |
Custom Balance AA unadjusted |
CAAA |
Custom Balance AA normal adjusted |
CAAR |
Custom Balance AA normal adjusted + reclassifying |
CAAC |
Custom Balance AA consolidated |
Note: If the additional character which specifies the balance type is not added to the equation (for example, CAA is used alone), the balance type will default to consolidated.
MAP/GROUP ADJUSTMENT NETINC AMOUNT
The element representing the NETINC amount to use.
When using the ACT() function with the NETINC account, include any map or group adjustments made to net income based map numbers or group numbers.
Element |
Type of Balance |
---|---|
GM |
Includes the total of all map adjustments made to income statement map numbers. |
Gx |
Includes the total of all group adjustments made to income statement group number (where x is the grouping from 1 to 9, or 0 for group 10. |
Example
- ACT("BR:G1","NETINC") will display the report balance of the net income account, including any adjustments made directly to any income statement group numbers.
Notes
- The map/group adjustment net income amount syntax should only be used with the ACT function, and should only be used when retrieving the value of the NETINC account.
- In order for this to work properly for group numbers, each group number must be denoted as a balance sheet or income statement group within Caseware Working Papers Engagement - Groupings screen using the Type column.
EXTENDED INFORMATION
The element representing the extended information to use.
The memo field EXINFO in several databases can be used for storing XML name/value pairs. CaseView linkage calculations are able to extract the XML data.
Example
The AM database record for account 102 has the following XML value collection stored in its EXINFO field:
- <values>
<value name="DescA">Open</value>
<value name="BalA">700.50</value>
<value name="DescB">Additions</value>
<value name="BalB">300.00</value>
<value name="DescC">Depreciation</value>
<value name="BalC">-80.22</value>
<value name="DescD">Final</value>
<value name="BalD">920.28</value>
</values>
- ACT("XML->BALD","102") will result in 920.28.
The MAP() or GRP() functions can also be used to access any XML balances. Assuming account 102 above is assigned to map number M1000 and group M:
- MAP("XML->BALA","M1000") will result in 700.50.
- GRP("M","XML->BALB","M1000") will result in 300.00.
For non-numeric values or for XML name/value pairs stored in other database memo fields, the FIELD() function can be used. To do so, add ”r;ValueName” to the name of the memo field.
- FIELD("AM","EXINFO->XML->DESCB","102") will retrieve the alphanumeric value Additions.
If no database is specified, as in most of the examples above, the equation defaults to looking at the EXINFO field in the AM database. However, this functionality can draw from other databases as well. Assuming the correct XML values have been created in the EXINFO field of the SH database, it is possible to extract a value, ”r;Reference2”, from a document, ”r;1. 2”, of the Document Manager using FIELD("SH","EXINFO->XML->REFERENCE2","1.2").
Notes
- Elements can be in any order and in any case (e.g. "ba:YR1" = "yr1:BA").
- These new value elements should not be used in combination with the older legacy values. Mixing legacy values and new values can cause conflicts and may result in incorrect results.