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

# 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 :

Ingredient | PC1 | PC2 | PC3 | PC4 | Cost |
---|---|---|---|---|---|

ing1 | 65 | 30 | 28 | 100 | 8 |

ing2 | 70 | 28 | 30 | 100 | 8 |

ing3 | 65 | 28.5 | 60 | 100 | 8 |

ing4 | 100 | 0 | 0 | 0 | 1 |

ing5 | 67 | 33 | 67 | 0 | 100 |

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.

The SPEL formula result in a JSON object with the following structure :

```
{"components":[{"id":"","name":"ing1","value":157.81249999999991},{"id":"","name":"ing2","value":10.0},{"id":"","name":"ing3","value":532.1875},{"id":"","name":"ing4","value":250.0},{"id":"","name":"ing5","value":50.0}], "value":10850.0, "status":"optimal"}
```

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

It is possible to stock the result in an object called "glopData" and to display the values obtained by Glop optimization in dynamic columns for each component :

```
var glopData = @glop.optimize({target: {var: cost['Coût'], 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}
}});
setGlopData(#glopData);
glopData.getStatus();
```

The dynamic column is :

```
entity.glopData.getComponentValue(dataListItemEntity.nodeRef)
```

# New version of GLOP service (from 3.2.4)

A new version of Glop service makes it easier to use.

Let's start from a finished product with 3 raw materials.

We desire to optimize the recipe to reach some nutrient and composition targets.

First, we need to add the columns "**Optimization Target**" and "**Optimization value**" upon composition and nutrient lists.

Then we need to fill the targets in the column "**Optimization Target**". A target can be :

- a single value, ex: 58.2
- 2 values separated with a "-", ex: 44-48, which are the min and amx
- completed by a tolerance between parentheses, ex: 58.2 (10) ou 44-48 (15), which is the tolerance in %
- empty, which means there is no constraint for that row

Then we need to create the SPEL formula.

This is the formula used for this case :

```
var glopData = @glop.optimize({target: {var: cost['workspace://SpacesStore/db597446-3458-405a-96d1-09b9593df1bb'], task: "min"}, constraints: {
{list: compoList},
{list: nutList}}});
setGlopData(#glopData);
glopData.getStatus();
```

Here is the result :

We can see that the system found a solution in the columns "**Optimization target**".

By default, the system will try to find a solution using the same amount of total quantity in the recipe. That is why the total quantity of the solution is still 100 kg.

It is possible to add a constraint on the recipe total quantity by adding the formula :

```
{var:"recipeQtyUsed", min:90,max:90}
```

## Tolerance application

When the system cannot find a solution, it is possible to apply tolerances (in %) on the constraints.

- From the SPEL formula :
`{list: compoList, tol:10}`

(here, the tolerance is applied for the whole list) - From the column "
**Optimization target**" :`6 (10)`

(here, the tolerance is only applied for the row)

Here is the result :

We can see the "**Proteins**" optimization value is orange because the system had to applied the 10% tolerance to find a solution.

The optimization status is then "**suboptimal**".

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