• Values
  • Values
  • Legacy Values
  • To flip a cell value
  • To flip the bank balance
  • To switch the sign of a cell value

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
• 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 with map and group number functions uses the sign assigned to the map or group rather than the sign applied to the account itself.
RAW	This property ignores the sign field of any account when calculating the balance. This overrides any current sign settings in Document | Settings | Client Options.

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

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:

				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.

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

Related Topics

• About Cell Calculations
• Legacy Values
• CLP codes