SPEL Formula

A minimum of knowledge in Java / Javascript programming is required.

Formulation often requires the use of SpEL Formulas to calculate results (for example to get the production cost of a product, or to calculate its energetic value).

This page is dedicated to the creation of these formulas in the system, and gives examples of simple and complex formulas to calculate automatically a result.

Formulas Localization

Formulas can be found in two main places:

• The list of administration characteristics, e.g. for nutrients, physico-chemical characteristics...
• The list of dynamic characteristics of a product/entity model.

But you can also find them on :

• Labeling rules on a product / entity model
• A .spel file linked to an entity model
• Catalogs
• Search exports
• Connectors

SPEL Context in beCPG

There exists several contexts where SPEL formulas are being used.

Formulation

Formulation is the main context where SPEL formula is used, it's important to understand what is the formulation chain and when formulas are triggered. The formulation chain's main goal is to load an entity from alfresco models and apply several transformations on it at the end the entity object is saved.

There are several formulation chains, one for a project and one for a product for example. On the product formulation chain we load an inherited ProductData JAVA object (RawMaterialData/ SemiFinishedProductData/ FinishedProductData), then we apply the corresponding formulation chain. At the end the ProductData is saved.

Important at the end of the formulation the engine can decide to save the ProductData only in memory (it's the case for OM simulation for example). You should take care of not saving data during the process it's why it's important to only manipulate the productData during SPEL formula.

The context of the formula is usually asked by most of SPEL functions. It's the element on which the formula is working.

• #this displays current entity
• entity displays the productData type context of the formula.
• dataListItem displays the line of a datalist, for example a line of the composition.
• dataListItemEntity displays the entity (type ProductData) associated to a line of the datalist, for example the Raw Material in a composition list.
• variantData displays the variant linked to a line of the composition list, packaging list,...
• compoList[0].product displays the first composition lin's entity

Formulation context:

Pre/post composition/packaging/process column formulation context:

Cost/Nut/Physico/Claim formulation context

Labeling context

Prefs or Render rules:

Labeling filter rules:

Catalog Spel context

Same context than formulation

JXLS report context

Same context than formulation

Export search context

No access to entity neither custom spel fonction

Connector Spel context

No access to entity neither custom spel fonction

SPEL Language

The Spring Expression Language (SpEL) is a powerful expression language that supports querying and manipulating an object graph at runtime. It can be used with becpg at several places

There are several operators available in the language:

Operators

Arithmetic Operators

All basic arithmetic operators are supported.

Divide and modulo operations have alphabetic aliases, div for / and mod for %. The + operator can also be used to concatenate strings.

Relational Operators

All basic relational and logical operations are also supported.

All relational operators have alphabetic aliases, as well. For example, in XML-based configs we can't use operators containing angle brackets (<, <=, >, >=). Instead, we can use lt (less than), le (less than or equal), gt (greater than), or ge (greater than or equal).

Logical Operators

SpEL supports all basic logical operations:

As with arithmetic and relational operators, all logical operators also have alphabetic clones.

Conditional Operators or Ternary

Conditional operators are used for injecting different values depending on some condition:

"Test" ? "valeur si test vrai" : "valeur si test faux"

The ternary operator is used for performing compact if-then-else conditional logic inside the expression. In this example we tried to check if it was true or not.

ex: 2 == 1 ? true : false // false

Another common use for the ternary operator is to check if some variable is null and then return the variable value or a default:

 ex : qty != null ? qty : recipeQtyUsed

The Elvis operator is a way of shortening the ternary operator syntax for the case above used in the Groovy language. It is also available in SpEL. The code below is | the code above:

ex : qty ?:0d > Injecte 0 si qty est null

Using Regex

The matches operator can be used to check whether or not a string matches a given regular expression.

Variables

Variables can be referenced in the expression using the syntax #variableName. Variables are set using the var keyword

var myVar = "This is a test";
#myVar // This is a test

The #this and #root variables

The variable #this is always defined and refers to the current evaluation object (against which unqualified references are resolved). The variable #root is always defined and refers to the root context object. Although #this may vary as components of an expression are evaluated, #root always refers to the root.

Accessing beCPG Java Field

With the help of SpEL, we can access the contents of any Java Object in the formulation context. For example in case of product formulation we have access to ProductData object:

public class ProductData {

     NodeRef hierarchy1;
     NodeRef hierarchy2;
     MLText legalName;
     MLText pluralLegalName;
     String title;
     String erpCode;
     SystemState state | SystemState.Simulation;
     ProductUnit unit | ProductUnit.kg;
     ProductData entityTpl;
     List<NodeRef> plants | new ArrayList<>();
     Double qty;
     Double density;
     Double yield;
     Double manualYield;
     Double secondaryYield;
     Double yieldVolume;
     Double netWeight;
     Double weightPrimary;
     Double netWeightSecondary;
     Double weightSecondary;
     Double netWeightTertiary;
     Double weightTertiary;
     Boolean dropPackagingOfComponents;

    /*
     * DataList
     */
     List<AllergenListDataItem> allergenList;
     List<CostListDataItem> costList;
     List<PriceListDataItem> priceList;
     List<IngListDataItem> ingList;
     List<NutListDataItem> nutList;
     List<OrganoListDataItem> organoList;
     List<MicrobioListDataItem> microbioList;
     List<PhysicoChemListDataItem> physicoChemList;
     List<LabelClaimListDataItem> labelClaimList;
     List<ControlDefListDataItem> controlDefList;
     List<LabelingListDataItem> labelingList;
     List<ResourceParamListItem> resourceParamList;
     List<ReqCtrlListDataItem> reqCtrlList;
     List<PackMaterialListDataItem> packMaterialList;

    /*
     * View
     */
     CompoListView compoListView | new CompoListView();
     ProcessListView processListView | new ProcessListView();
     PackagingListView packagingListView | new PackagingListView();
     LabelingListView labelingListView | new LabelingListView();

    /*
     * Variants
     */

     List<VariantData> localVariants;
     VariantPackagingData defaultVariantPackagingData;
     VariantData defaultVariantData;

    /*
     * Product specifications
     */

     List<ProductSpecificationData> productSpecifications;
     List<ClientData> clients;

    /*
     * Origin geo
     */

     List<NodeRef> geoOrigins | new ArrayList<>();

    /*
     * Completion scores
     */
     String entityScore;
     List<String> reportLocales;
     List<ProductData> compareWithEntities;

}

To access members variable in SpEL just indicate the variable name:

You can also call any method available in the ProductData object:

isLiquid()
isRawMaterial()
isPackaging()
isPackagingKit()
isSemiFinished()
isLocalSemiFinished()

Collection Selection

Selection is a powerful expression language feature that allows you to transform some source collection into another by selecting from its entries.

Selection uses the syntax .?[selectionExpression]. This will filter the collection and return a new collection containing a subset of the original elements. For example, selection would allow us to easily get a list of CostListDataIteltem :

In addition to returning all the selected elements, it is possible to retrieve just the first or the last value. To obtain the first entry matching the selection the syntax is ^[…] whilst to obtain the last matching selection the syntax is $[…].

In addition to returning all selected items, it is possible to retrieve only the first or last value. To obtain the first entry corresponding to the selection, the syntax is ^[...] while to obtain the last corresponding selection, the syntax is $[...].

Collection Projection

Projection allows a collection to drive the evaluation of a sub-expression and the result is a new collection. The syntax for projection is ![projectionExpression]. Most easily understood by example, suppose we have a list of costs but want the list of cost value. Effectively we want to evaluate 'value' for every entry in the cost list. Using projection:

Formula editor

Formula editor is presented as a text area, with numeroted lines, and two tables below.

Generalities on a simple formula

Most of the time, SpEL formulas will display a single result; which is the last calculated expression. That means that it's possible to use intermediate variables in calculation, if the variable containing the final result is called at the end of the formula.

For example, the following formula will give the result, stocked in the result result

    var result = qty;
    var yieldCoeff = (yield/100);
    #result = #result / #yieldCoeff;
    #result;

However, most of the formulas will have only a single expression, and the use of intermediate variables won't be necessary. Then, the previous expression could be shortened:

    qty / (yield/100)

One of the main limitation of SpEL language is the absence of conditions with if ... else. You need to use ternary structure which can accumulate and make the reading complex.

e.g. Salt quantity calculation (based on sodium quantity):

    nut['Sodium'].value == null ? 0d : nut['Sodium'].value * 2.5 / 1000

(Sodium is here the nodeRef of the nutrient Sodium)

The formula first checks that the Sodium value is not null. If null, the formula displays 0. Most of the reading access to the propertu or calculation values have to include a default value, in case the value is not present. This value must have the suffix d (or D) to indicate to the sustem that this number contains comma, and not a character (example: 1d is a number with comma, while 1 can represent the entire number 1, or the text character "1").

Multiplications work with 0d, but divisions must have 1d, and formulas which display text must have "" as default value. These values must be used in condition formulas.

Helpers SPEL

CTRL + SPACE shortcut displays a help menu to have a quick access to the advanced fonctions.

Dynamic characteristic

Dynamic characteristics can be done in a formulated product, or in every lines of the linked list. Every list with components (packaging, composition,process) owns its own dynamic characteristic list.

It's possible to apply a formula to all elements of a datalist by choosing a column. The results will then be displayed in a column.

eg:"Costs (€)" is calculated for every components of the list.

Characteristics

Tables under the text area enable users to quickly select fragments of formulas to insert in the text zone where the cursor is.

You can choose elements on the left, and choose the information to display from this elements at the right (value, minimumValue,...)

Advanced

beCPG Spel custom functions

beCPG defines a set of customs functions accessible from the SPEL formula.

Syntax to use this functions is @beCPG.function

Warning: if java fields are available, functions of type propValue, assocValue, setValue, assocValue, assocValues will not work. ex: @beCPG.propValue(#this, “bcpg:productQty”) is equivalent to the java field “qty ”. For more information on java fields Click here

Example

• propValue : Displays a property. we want to extract the custom:name field from the entity with a fixed dynamic characteristic.

@beCPG.propValue(#this,"custom:name")

we want to extract the custom:code field from the entities of a list with a characteristic in column.

@beCPG.propValue(dataListItemEntity,"custom:code")

you want to extract the value of a hierarchical family (d:noderef). In this case, propValue : must be typed twice.

@beCPG.propValue(@beCPG.propValue(#this,"bcpg:productHierarchy1"),"bcpg:lkvValue")

• setValue : Set a value to a property.

set "kg" to custom:productUnit :

@beCPG.setValue(#this,"custom:productUnit","kg")

we want to assign the custom:description value to the custom:legalName field

@beCPG.setValue(#this,"custom:legalName",@beCPG.propValue(#this,"cm:description"))

• assocValue :Displays the nodeRef value of an association

displays association trademarkRef :

@beCPG.assocValue(#this,"bcpg:trademarkRef")

• assocValues : Displays the values of a multiple associations in table.

we want to extract the multiple association "bcpg:productGeoOrigin":

@beCPG.assocValues(#this,"bcpg:productGeoOrigin")

Please note that you can extract the first element with [0]

@beCPG.assocValues(#this,"bcpg:productGeoOrigin")[0]

Which is then similar to:

@beCPG.assocValue(#this,"bcpg:productGeoOrigin")

• setAssocs : Set one or several assoc values.

we want to set the plant with nodeRef "workspace://SpacesStore/169a0cf8-2f0a-4011-9b19-dea14676e136" to the "bcpg:plant" association:

@beCPG.setAssocs(#this,"bcpg:plants", "workspace://SpacesStore/169a0cf8-2f0a-4011-9b19-dea14676e136")

we want to add the client with nodeRef "workspace://SpacesStore/844aa160-f344-43be-a659-434d319e584f" to the "bcpg:clients" association:

var clients = @beCPG.assocValues(#this,"bcpg:clients");
#clients.add("workspace://SpacesStore/844aa160-f344-43be-a659-434d319e584f");
@beCPG.setAssocs(#this,"bcpg:clients", #clients)

• assocPropValues : Displays properties from the associations extracted.

we want to extract title for client :

@beCPG.assocPropValues(#this,"bcpg:client","cm:title")

• assocAssocValues : Displays associations from the associations extracted.

we want to extract bcpg:plantCertifications from bcpg:plant :

@beCPG.assocAssocValues(#this,"bcpg:plant","bcpg:plantCertifications")

• propMLValue : Extract a multilingual property in the specified locale.

we wan to extract the bcpg:legalName in english US:

@beCPG.propMLValue(#this,"bcpg:legalName","en_US")

we wan to extract the bcpg:legalName all translation:

@beCPG.propMLValue(#this,"bcpg:legalName",null)

• updateMLText : Set a locale value to a multilingual field.

we want to set tthe value "Finish Legal Name" to the legalName in finish:

@beCPG.updateMLText(legalName,"fi","Finish Legal Name")

Beware: this works for multilingual fields as helpers only.

• copy : Copy associations and/or lists from a source to the current product.

The function is working like this:

@beCPG.copy(sourceNodeRef, assocToCopyList, listToCopyList)

we want to copy the associations "bcpg:trademarkRef","bcpg:subsidiaryRef","bcpg:clients", and "bcpg:suppliers" from the first entity of the compoList:

@beCPG.copy(compoList[0].product,{"bcpg:trademarkRef","bcpg:subsidiaryRef","bcpg:clients","bcpg:suppliers"},"")

we to copy the microbioList from the association productMicrobioCriteriaRef:

var productMicrobioCriteriaRef = @beCPG.assocValue(#this,"bcpg:productMicrobioCriteriaRef");
@beCPG.copy(#productMicrobioCriteriaRef,"",{"bcpg:microbioList"})

we want to copy the list and properties from the parent entity:

@beCPG.copy(parentEntity,{"bcpg:legalName", "bcpg:productHierarchy1", "bcpg:erpCode"},{"bcpg:compoList","bcpg:packagingList","mpm:processList"})

• applyFormulaToList : Applicate a formula to a list, like a dynamic column.

we want to set compoListQtySubFormula of compoList as 0 :

@beCPG.applyFormulaToList(getCompoList(),"@beCPG.setValue(dataListItem,'bcpg:compoListQtySubFormula', 0d)")

This works on most of standards listes, like:

• getCompoList() ( or compoList )
• getPackagingList() ( or packagingList )
• getProcessList() ( or processList )
• getControlDefList() ( or controlDefList)
• priceList
• costList
• reqCtrlList

• filter Use to filter. Is generally combined with other formulas, like min, max, avg or sum.

we want to find the reqCtrlList elements, which have a reqMaxQty value and are not associated to "Info" type:

@beCPG.filter(reqCtrlList, "reqMaxQty!=null && reqType.toString() !='Info'")

we wan to extract the compoList elements of the main variant which have a qty > 0.5 kg:

@beCPG.filter(getCompoList(new fr.becpg.repo.variant.filters.VariantFilters(true)), "dataListItem.qty > 0.5")

• max Use to find the maximum value of an array:

@beCPG.max($arrayOfDouble)

we want to find the maxmim reqMaxQty of the reqCtrlList:

@beCPG.max(reqCtrlList.![reqMaxQty])

we want to find the maximum qty of the compoList:

@beCPG.max(compoList.![qty])

• min Use to find the minimim value of an array:

@beCPG.min($arrayOfDouble)

we want to find the minimum among the reqCtrlList elements which have a reqMaxQty and are not associated to "Info" type:

@beCPG.min(@beCPG.filter(reqCtrlList, "reqMaxQty!=null && reqType.toString() !='Info'").![reqMaxQty])

• avg Use to find the average value of an array:

@beCPG.avg($arrayOfDouble)

• sum Use to sum the values of an array.

  @beCPG.sum(range, formula)
  

You can have the sum of the elements in a datalist.

@beCPG.sum(getCompoList(new fr.becpg.repo.variant.filters.VariantFilters(true)),dataListItem.qty)

We add the filter to only consider the quantity of the composition elements with the default variant. However, we can go further and only takes in account the quantity of the componenents which are not local semi finished products.

@beCPG.sum(
    @beCPG.filter(
        getCompoList(new fr.becpg.repo.variant.filters.VariantFilters(true)), 
        !(dataListItemEntity instanceof T(fr.becpg.repo.product.data.LocalSemiFinishedProductData))
    ),
    dataListItem.qty
)

Functions isLiquid() to isLocalSemiFinished() are shotcut to not use instanceof seen in the last formula.

Second example, you want to do a sum of the elements in a dynamic column. The fomula is the following using column 7 in the composition as an example:

@beCPG.sum(compoList, "@beCPG.propValue(dataListItem,'bcpg:dynamicCharactColumn7')" )

• children Used to get the children of entity, used to go down in the composition levels.

  @beCPG.children(entity)
  

• findOne Used to get the entity designed with the nodeRef

@beCPG.findOne(nodeRef)

• sourcesAssocValues Displays the where-used nodeRef array, based on a target association.

we want to have the project list using my current product:

@beCPG.sourcesAssocValues(#this,"pjt:projectEntity");

we want to have the number of product using my current product in their composition:

var sources = @beCPG.sourcesAssocValues(#this,"bcpg:compoListProduct");
var size = #sources.size();
#size;

• updateDate Modify a date value by adding day/ week / month / year:

@beCPG.updateDate($date, $field, $amount )

we substract 5 days to the today's date:

@beCPG.updateDate(new java.util.Date(), T(java.util.Calendar).DAY_OF_MONTH, -5 )

on ajoute 1 an à la date de création :

@beCPG.updateDate(@beCPG.propValue(#this, "cm:created"), T(java.util.Calendar).YEAR, 1 )

We add 3 months to the modified date:

@beCPG.updateDate(@beCPG.propValue(#this, "cm:modified"), T(java.util.Calendar).MONTH, 3 )

• formatNumber : you can specify the format for numeric fields

we want to display the protein nutrient with 2 significant digits

@beCPG.formatNumber(nut['workspace://SpacesStore/d49d02aa-3443-48f8-b6ee-9e66fe2ba4c4']?.value, "0.##")

• formatDate : you can specify the date format.

we want to display the month in which the product was last formulated

@beCPG.formatDate(formulatedDate,"MM/YYYY")
@beCPG.formatDate(formulatedDate,"dd/MM/YYYY")

Date format

Nutriscore formula (beCPG 4.0)

To extract the nutriscore details, we need to initialise the context as variante

var context = T(fr.becpg.repo.product.formulation.score.NutriScoreContext).parse(nutrientDetails);

Then, we can extract a summarize:

#context.toDisplayValue();

We can also extract details on each nutrient:

• getNutriScore()
• getEnergy()
• getSatFat()
• getTotalFat()
• getTotalSugar()
• getSodium()
• getPercFruitsAndVetgs()
• getNspFibre()
• getAoacFibre()
• getProtein()

We can also extract details on nutriscore:

• getCategory()
• getNutrientClass()
• getClassLowerValue()
• getClassUpperValue()
• getAScore()
• getCScore()

We can use the value to extract from them:

• getLowerValue()
• getUpperValue()
• getScore()
• getValue()

For example:

#context.getProtein().getValue();
#context.getTotalSugar().getLowerValue();
#context.getAScore();
#context.getClassUpperValue();

Accessing java type

The special 'T' operator can be used to specify an instance of java.lang.Class (the 'type'). Static methods are invoked using this operator as well.

T(java.util.Date)
T(String)
T(java.math.RoundingMode).CEILING < T(java.math.RoundingMode).FLOOR

Constructors

Constructors can be invoked using the new operator. The fully qualified class name should be used for all but the primitive type and String (where int, float, etc, can be used).

Expression templating

Essentially used in labeling Render formula. Expression templates allow a mixing of literal text with one or more evaluation blocks. Each evaluation block is delimited with prefix and suffix characters that you can define, a common choice is to use #{ } as the delimiters. For example,

"random number is #{T(java.lang.Math).random()}"

Inline lists, maps and array construction

Lists can be expressed directly in an expression using {} notation. Maps can also be expressed directly in an expression using {key:value} notation. Arrays can be built using the familiar Java syntax, optionally supplying an initializer to have the array populated at construction time.

"{1,2,3,4}" // List construction
"{name:'Nikola',dob:'10-July-1856'}" // Map construction
"new int[]{1,2,3}" // Array construction

Examples of formulas

• This formula calculates the sum of wheat and wheat flour % (in ingredient slist) in the product. ('Wheat' is the nodeRef, not text).

    (ingList.^[ing.toString() == 'Wheat']?.qtyPerc != null ? ingList.^[ing.toString() == 'Wheat']?.qtyPerc : 0d) +
    (ingList.^[ing.toString() == 'Wheat flour']?.qtyPerc != null ? ingList.^[ing.toString() == 'Blé']?.qtyPerc : 0d)

• This formula calculates the sum of the dynamic column 2, for the components which are not local semi finished products.

@beCPG.sum(getCompoList(new fr.becpg.repo.variant.filters.VariantFilters()), 
"dataListItemEntity instanceof T(fr.becpg.repo.product.data.LocalSemiFinishedProductData) ? 0d : @beCPG.propValue(dataListItem.nodeRef, 'bcpg:dynamicCharactColumn2')")

• In this formula, in following the entity model of the component, will be sum: The property "Reconstitution Rate", multiplied by the quantity of the composition line, if the template of the component is "Reconstituted FP". The physicy-chemical characteristic "Dry matter" of this component if the previous is not applicable

@beCPG.sum(getCompoList(), "@beCPG.findOne(dataListItemEntity).getEntityTpl() != 'Reconstituted FP' ? @beCPG.propValue(dataListItemEntity, "bcpg:reconstitutionRate") * dataListItem.qty : dataListItemEntity.physico['Dry matter']?.value")

• Used to get the today's date in the dd/MM/yyyy format.

new java.text.SimpleDateFormat("dd/MM/yyyy").format(new java.util.Date())

• Used to get personalized format on number.

new java.text.DecimalFormat("0.#").format(nut['Protein']?.value)

• Used to replace the text "to replace" to "new text" in the field cm:description.

@beCPG.propValue($nodeRef,"cm:description").replaceAll("toreplace","new text")

• Find out if a claim is claimed

entity.labelClaim['Kasher']?.isClaimed

• Extract the text of the field cm:name after the first occurance of a text string.

var productName = @beCPG.propValue(#this,"cm:name");
#productName.substring(#productName.indexOf("test"));

• Test the presence of a text string at the beggining of a text and display true / false.

@beCPG.propValue(#this,"cm:name").startsWith("X");