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.

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

Simple formulas localization

Formulas will be found mainly in two differents locations:

  • In the administration, for example for nutrients, physico-chemical characteristics,...
  • On Products and product templates, in dynamic characteristics

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.

In this screenshot, "Costs (€)" is calculated for every components of the list.

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.

Formula editor

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

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

Those variables come from ProductData class, which is used in every products, or from a list data (NutListDataItem, CostListdataItem...).

Helpers SPEL

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

Those functions come from the FormulationHelper facilitate the use of sum, multiplication, or fields value extraction.

Context

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

Functions

  • propValue

Displays a property.

Example : we want to extract cm:name:

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

Example 2 : we want to extract the bcpg:erpCode of compoList's entities:

@beCPG.propValue(dataListItemEntity,"bcpg:erpCode")

Example 3 : we want to extract hierarchy name (d:noderef):

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

Set a value to a property.

Example: set "kg" as productUnit:

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

Example 2 : set description value as legalName

@beCPG.setValue(#this,"bcpg:legalName",@beCPG.propValue(#this,"cm:description"))
  • assocValue

Displays the nodeRef value of an association

Example : displays association trademarkRef :

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

Displays the values of a multiple associations in table.

Example : 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.

Example: 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")

Example 2: 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.

Example : we want to extract title for client : 

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

Displays associations from the associations extracted.

Example : 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.

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

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

Set a locale value to a multilingual field.

Example : 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)

Example: 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"},"")

Example 2 : we to copy the microbioList from the association productMicrobioCriteriaRef:

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

Exemple 3 : 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.

Example : 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'")

Example 2 : 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)

Example : we want to find the maxmim reqMaxQty of the reqCtrlList:

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

Example 2 : 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)

Example: 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)

Example: 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.

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

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

Exqmple 2 : 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 )

Example: we substract 5 days to the today's date:

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

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

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

Example 3: We add 3 months to the modified date:

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


@beCPG

# Examples of formulas
-----------------------------

@beCPG.propValue(@beCPG.assocValue(entity, "bcpg:clients"), "cm:name")

This fonction extracts the field Name "cm:name" of the association bcpg:clients, @beCPG.assocValue(entity, "bcpg:clients") gives its nodeRef.
Useful in the case the association has a single value.

(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 wheat and wheat flour % (in ingredient slist) in the product.
('Wheat' is the nodeRef, not text).

@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')")

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

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


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

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


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

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

Used to get personalized format on number.

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


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

entity.labelClaim['Kasher']?.isClaimed


Displays *true* if Kosher is claimed.

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

productName.substring(#productName.indexOf("test"));


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

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


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

# Nutriscore formula

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();

```

results matching ""

    No results matching ""