Perfion makes use of formulas to dynamically compose feature data into a display values. Composed display values are available in a variety of outputs, including the grid, reporting and exports.

The grid operates in a similar way to Excel, where a formula is seen in edit-mode, whereas the Display value is seen in non-edit-mode.

Formula Examples

Formula	Example Result
[ShortDescription]	BMW 330i
[ShortDescription@{Value}]	BMW 330i
The new [ShortDescription] with [EnginePower@{Value} {Unit}] and an [EngineVolume@{Caption} of {Value} {Unit}]	The new BMW 330i with 345 hp and an Engine Volume of 3,5 l
[ExternalColor@FormatMultiValues(● {Value}\|\r\n)]	● Red ● Green ● Blue
The car has a [Weight@{Caption}] of [Weight@{Value:#,##0.00} {Unit}].	The car has a Weight of 1,512.00 Kg.
[Price@{Value:c}]	12.55$
[Height@{Caption}] is in the info group [Height@{Group}]	Height is in the info group Technical Specs

Formula composition

A formula is encased within square brackets. Following the opening bracket you have a feature identifier. This can either be in the form of a name or a number. Following the feature name is a @ symbol that acts as an expression indicator. A Perfion expression comes next followed by the end square bracket.

e.g.

[Feature@Expr]

For access to base values, use the special keyword Value

e.g.

[Value@Expr]

Multiple formulas can be entered into text values, to allow composition across a range of values.

e.g.

W: [Width] H: [Height] L: [Length]

Formula value can be hidden if feature value is empty using syntax Feature{}

e.g.

[Feature{}value:@{Value@Expr}]

NOTE: If a formula does not contain the expression indicator then the value of the feature is used. See the two first examples in the Formula examples table, the results of the expressions are identically but the formulas are slightly different.

Getting started with formula

Text and string features can be setup to process formulas.

To enable formulas on a feature, first login into Perfion as an Administrator, then edit the feature with the feature editor. Select the checkbox ‘Formula’ on the Feature Definition tab.

The Formula checkbox can be enabled or disabled at any time, regardless of any data entered into the feature.

If a formula refers to a feature value that cannot be resolved then the display value will be blank.

Grid

This grid will display the results formulas as display values. When editing formulas, the editor will use the underlying formula.

Showing a formula display value

When a formula has been entered but give no results, then a black triangle indicator is displayed in the top left corner of the formula’s cell. This is to help the user see that there is a value, but it is not resulting in a display value.

When the grid cells are updated any formula’s that reference the updated cells won’t immediately be updated. However they will be marked with a red triangle in the top left corner. The triangles indicate the formulas that need to be refreshed (F5). Perfion does not automatically refresh these formulas to avoid degrading performance of the system.

Formula Editor

A formula editor can be activated to aid the user with the formula syntax. Activation is available from the advanced tab of the Feature editor.

The Formula Editor is available for features marked as ‘Formulas’ that are non-selectable under the advanced tab of Feature Definition.

Once enabled and saved the Formula editor will available to edit values of the feature from both the Grid and Item Editor editors.

The formula editor is divided into three tabbed areas. Within the Edit area the user can edit and make changes to the formula text. Pressing F5 at any time will display the results of the formula in the Result tab. The Formula Insert Helper tab can be used to help the user insert difficult to remember formulas.

Formula Insert Helper overview

The formula insert helper provides a list of available features in the system. These can be filtered by entering part of the feature name, caption or even the feature ID into the ‘Filter features’ area.

The user can locate the feature that they wish entered as a formula, then double click on it to insert the formula.

For example double clicking on Height would insert the following formula: [Height]

By right clicking on a feature, a context menu is shown that allows additional formulas to be entered for the feature. For example right clicking on Height will provide the following context menu:

Selecting Caption would insert [Height@{Caption}] and Selecting Value->2 Decimal Places would insert [Height@{Value:#,##0.00}].

Expressions

Expressions are used throughout the Perfion application, and not just in formulas. An expression is able to convert a features value in some way.

Expression to control Single Values

Expression:            @FormatValue
Keywords:                        Caption, Value, Unit, Abbr, CaptionAlternative, Group
Form:                               @FormatValue(1)
                                              1 – Value part

The value part allow the composition of data using the keywords and free text. An example might be {Value} {Unit} which would output the value and the unit of the feature with a space in between.

Examples:

@FormatValue( {Caption} {Value:#,##0.00} {Unit} )

Also it is possible to use formatvalue by just starting to use keywords. E.g.
{Caption} {Value:#,##0.00} {Unit}

{Value} can be formatted with additional specifiers.

Number formatting

Specifier	Type	Example	Output
c	Currency	{Value:c}	-$12,400
d	Decimal(Whole number)	{Value:d}	-12400
e	Scientific	{Value:e}	-1.240000e+004
f	Fixed point		-12400.00
g	General		-12400
n	Number with comma/dots for thousands		-12,400
0	Zero placeholder	{Value:00.0000}	-12400.0000
%	Percent	{Value:0%}	-1240000%
#	Digit placeholder	{Value:#,##}	-12,400

Date formatting

Specifier	Type	Example
d	Short date	10/12/2002
D	Long date	December 10, 2002
t	Short time	10:11 PM
T	Long time	10:11:29 PM
f	Full date & time	December 10, 2002 10:11 PM
F	Full date & time (long)	December 10, 2002 10:11:29 PM
g	Default date & time	10/12/2002 10:11 PM
G	Default date & time (long)	10/12/2002 10:11:29 PM
M	Month day pattern	December 10
r	RFC1123 date string	Tue, 10 Dec 2002 22:11:29 GMT
s	Sortable date string	2002-12-10T22:11:29
u	Universal sortable, local time	2002-12-10 22:13:50Z
U	Universal sortable, GMT	December 11, 2002 3:13:50 AM
Y	Year month pattern	December, 2002

Expression to control Multi Values

Expression:          @FormatMultiValues
Keywords:                              Caption, Value, Unit, Abbr, CaptionAlternative, Group
Form:                                     @FormatMultiValues(1|2)
                                                                1 - Value part.
                                                                2 – Concatenation Value part

The value part allow the composition of data using the keywords and free text. An example might be {Value} {Unit} which would output the value and the unit of the feature with a space in between.

The concatenation value part is used to specify free text between the multiple values. An example might be \r\n which is the carriage return characters.

Examples:

@FormatMultiValues(●{Value}|\r\n)
@FormatMultiValues({Value}-{Unit}|<br>)

Expression to control Barcode values

Expression: @Convert
Keywords: fixdevxupca, tofontean13, tofontean13-fromupca, tofontupca-fromean13, tofont2ean13, tofont2ean13-fromupca, tofont2upca, tofont2upca-fromean13, tofont3upca, tofont3upca-fromean13

Form: @Convert(keyword)

This expression converts a value such that if can be displayed with a barcode font.

Examples:

@Convert(tofontean13)

Expression to replace values

Expression: @CaseReplace
Form: @CaseReplace(nonMatchValue|replacePair|replacePair|nullmatchValue)

The nonMatchValue is a value that will be used when all of the other matches fail except for null values. The nonMatchValue is the first value specified. When no value is entered then there will be no nonMatchValue used.

The replacePair is a key value pair. The key will be searched for and replaced with the value. The replacePair can repeat.

The nullmatchValue is a value that will be used for null values. The nullmatchValue is the last value specified. If no value is entered then there will be no nullmatchValue used.

Example:

@CaseReplace(nn|50=L|10=X|5=V|=-)

The example above produces – for null values, X where the value is 10, V where the value is 5 and nn for all other values.

@CaseReplace(|10=X|5=V|)

The example above does not use the nonMatchValue and the nummMatchValue.

In the following screen shot, the “Status Formula” is a formula field using @CaseReplace to output different values based on the ProductStatus feature.

The formula for this is:

[ProductStatus@CaseReplace(New|ERP=Pending|=Missing)]

Expression to control variant titles

Expression:           @FormatVariantTitle
Keywords:                             Caption, Value, Unit, Abbr, CaptionAlternative, Group
Form:                                     @FormatVariantTitle(1)
                                                                1 – Value part

The expression works in the same way as @FormatValue, except {Value} represents a variant title.

Example:

@FormatVariantTitle({Value:#,##0})

Reference Errors

Disallowed Reference(1)

This error message is produced when evaluating a formula that refers to another formula.

Security

Formulas have been implemented with security. It will not be possible to retrieve a value using formulas that the user does not have access to.

Output Channels

Formula display values will be used in all of the output formats, except for Excel for re-import. For this case the formula value is outputted. This is because it is intended for re-importing into Perfion.

The XML result from the API query will only contain the values resolved by the formula. This means that all solutions already built on top of the API will run with the new formulas without changes.

e.g.

<Product id="136" parentId="47" brand="Normal" order="0" …>
              <Value language="EN" seq="0" modifiedDate="2011-05-04T11:07:29.287">BMW 325i Coupe</Value>
              <CalculatedDescription>The ItemNumber is BM325ICOU</CalculatedDescription>
       </Product>

Consideration

Referencing a localizable value from a non-localizable formula will resolve the value by using the first language that is available.

Limitations

Formulas referencing remote values. Formulas that reference remote values, will not be resolved. It has not been implemented due to performance reasons.
Remote value emitting a formula. Formulas that are retrieved from remote values will not be resolved. It has not been implemented due to performance reasons.
Item Card Editor. The Item Card Editor does not show resolved formula display values.
This will be added in a future version.
Formulas require refresh. When values are altered in the grid, the formulas do not automatically update. Refreshing the grid will update the formulas. Auto refreshing is not implemented for performance reasons.
Outputs. Formulas are only resolved reliably if the view.xml queries request their features using named configurations. For example <Feature id=’*’ name=’config’ />. Additional features, including formula features, can be requested explicitly, but the query need to request a wildcard ‘*’ featureID.
Grid. Formula in the grid are only resolved reliably if you use the grid in a named view mode. E.g. ‘Item’
Queries. Formula results are not queryable using the API.
Calculations. Formulas do not perform any form of aggregation at this point.
Formula editor within the Item Editor. When making formula changes across a hierarchy of items, it is possible that the top most change is discarded when moving back to a lower level via the bread crumb control.

Trouble Shooting

The Formula does not resolve

When a formula is referring to a feature that cannot be found, then the display value is resolved blank.

Formula	Display Value
The result is :[MissSpelledFeature@{Value}].	The result is :.

This is because the feature MissSpelledFeature could not be resolved.

This will also occur for correctly entered features, when:

A view is not specified, in the query or the grid. E.g. <Feature id=’*’ config=’Item’ />
The feature is a remote. Remote features are not resolved for performance reasons.

The Formula does not resolve in all languages

When a formula is entered on a localizable value, you need to enter the formula for each of the languages:

In example above, the Feature Additional info has only been entered in English value. Hence, Formula display values will only be available for the English value.