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.

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

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.

Example of optimisation with the Glop service

Let's consider a recipe using 5 ingredients : ing1, ing2, ing3, ing4 et ing5.

Each ingredient has its own physico-chemical characteristics (PC) and its own cost, which are defined as follows :

IngredientPC1PC2PC3PC4Cost
ing16530281008
ing27028301008
ing36528.5601008
ing41000001
ing56733670100

Our goal is to minimize the total cost while respecting the following constraints :

  • the total volume must be 1000
  • PC1 must be between 70000 and 80000
  • PC2 is a quantity expressed as a percentage and must be between 20% and 30%
  • PC3 must be between 40000 and 50000
  • PC4 must be between 70000 and 80000
  • ing1 must be between 1% and 100% (of total volume)
  • ing2 must be between 1% and 100% (of total volume)
  • ing3 must be between 10 and 1000
  • ing4 must be between 10% and 25% (of total volume)
  • ing5 must be between 50% and 100% (of total volume)

The SPEL formula must be written as follows :

@glop.optimize({target: {var:   cost['Cost'], task: "min"}, constraints: {
               {var:    physico['PC1'] , min: 70000, max: 80000},
               {var:    physico['PC2'], min: 20000, max: 30000},
               {var:    physico['PC3'], min: 40000, max: 50000},
               {var:    physico['PC4'], min: 70000, max: 80000},
               {var:    compo['ing1'], min: 10, max:1000},
               {var:    compo['ing2'], min: 10, max:1000},
               {var:    compo['ing3'], min: 10, max:1000},
               {var:    compo['ing4'], min: 100, max:250},
               {var:    compo['ing5'], min: 500, max:1000},
               {var: "recipeQtyUsed", min: 1000, max: 1000}
               }})

As the physico-chemical characteristic PC2 a quantity expressed as a percentage in beCPG (for instance : carbohydrate level), you have to multiply its constraints by the total volume because the system cannot predict it has to divide by the total volume, therefore we have PC2 min = 20 x 1000 = 20000 and PC2 max = 30 x 1000 = 30000.

And the SPEL formula result is :

{coefficients={ing1=157.81249999999991, ing4=250.0, ing3=532.1875, ing2=10.0, ing5=50.0}, value=10850.0, status=optimal}

The displayed result is 10850.0, it is the minimized total cost.

results matching ""

    No results matching ""