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

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.

Related Topics