Using formulas in Xentral allows you to perform mathematical operations on a wide variety of fields from your documents. Below we explain the basic settings for preparing and applying the formula concept in Xentral.
Basically, you should think about which fields you want to include in the formulas in advance.
Xentral offers a variety of options here. Please make sure that the required fields are maintained accordingly. For example, a free field should be filled with numeric values so that mathematical operations can be performed.
In Xentral, you can basically address the price and quantity in the documents. If you use free fields, you have the possibility to map your individual requirements and include them in calculations. For general information about free fields, see the article Free Fields.
In order to use free fields in formulas, please note the following:
-
Basically, the content in the free fields is drawn from the master data, provided that it has been maintained
-
Manual adjustments are possible, but must then be made in the item list of the document
This means that you can access the free fields of the item at any time and do not have to create them again in the document.
Under Administration > Settings > System > Basic settings > Free fields the following fields must be noted:
-
The checkmark at Show free fields in article must be set
-
Display in PDF: Here you can determine where the free fields should be displayed
This is relevant for the calculation, because only the displayed free fields are included in the calculation.
If the name of the free field is not to be displayed, the checkbox at Positions with free fields must remain empty (Basic settings > Settings > System). This way you can prevent too much text from being included in the description. If you still need the description, please check the box.
Display without check mark in the Positions with free fields checkbox.
Display with a check mark in the Positions with free fields checkbox.
You have the option to create a formula in the editor and then use it as an abbreviation. This is for clarity and allows you to manage the formulas centrally. However, you can also perform the calculations directly in the item description. Basically, the free fields, quantity and price are addressable.
In the App Center you will find the app Formulas. Here you can define formulas for various purposes, which you can use as variables in Xentral via the associated ID in the following: {FORMULAID}, e.g. {FORMULA1}.
After you have assigned an identifier and a name for the formula, you can store the formula via the pen icon.
Interpretation: In this example, the entered value of the item from free field 1 is to be multiplied by the quantity of item1 from the document and finally divided by 1000 to output the target unit (kW). The usual arithmetic operators "*", " / ", " + " and "-" as well as "sin" and "cos" can be used here. In the case of nested formulas, the use of brackets "( )" must be observed in accordance with the mathematical calculation laws.
The formula can then be stored as follows in the offer, for example in the article free text, and output accordingly.
The value is then output on the document, here in the offer, as follows.
A use in the text templates under Administration > Settings > System > Basic settings > System in the tab Text templates can be done analogously.
In order to be able to access quantities and prices of the other items in the item view of the documents, it is now possible to refer formulas to prices and quantities (furthermore also free fields) under Master data > Product in the details.
In the following example, the item has been added as the third quote item and the above calculation has been performed for the quantity and price of the item.
Interpretation: quantity of item1 = 15* price of item1 = 62 = quantity of item3 (added pos with formulas) divided by 10 → result rounded to 2 digits = 93.
In principle, it is possible to address individual fields from the product master in Xentral. The syntax for this is: {{ARTICLE:<FIELDNAME>}} A field name can, for example, be an item in the product master. Here you could use the height of the article. The corresponding formula then looks like this {{ARTICLE:HEIGHT}}:
A possible use case would be the calculation of the sales price based on the area of an article. This allows you to process the different fields according to the calculation rules. In our example, the following formula must be used: {{ARTICLE:LENGTH*ARTICLE:WIDTH/100}}
You can store this directly in the product master and use it to immediately calculate the price in the receipt (Product > Details).
And produces the following result:
In this section we would like to introduce you to further possibilities of working with formulas. If you are familiar with column operations, such as from Excel, you have sufficient prior knowledge to use the special functions. To begin, we will show you the basic formatting options that you can use to customize the result. For example, you can output the result of an arithmetic operation in such a way that only two decimal places are displayed.
The numbers are listed as examples and can be changed accordingly:
-
{{format(number,4)}}: always shows 4 decimal places
-
{{round(number,2)}}: rounds to 2 decimal places
-
{{abs(number)}}: truncates all decimal places without rounding
For special applications, we provide these mathematical operations:
-
{{sin(radian)}}: returns the sine value of the input radian
-
{{cos(radian)}}: returns the cosine value of the input radian
-
{{sqrt(number)}}: takes the square root
-
{{root(radicant, root exponent}}: takes the root with arbitrary radicant and root exponent
In Xentral you can perform advanced column operations. The position, in the formula POS: expects as reference a field you want to operate with. In the example below we have used the item price. The same is true for the Quantity item. The following rules will help you to use all possibilities efficiently:
-
The notation POS: does not contain any restrictions concerning the position. All positions are passed through
-
The notation POS1: refers only to position 1
-
{{columnsum(POS:price * POS:quantity)}}: Returns the sum of (POS1:price * POS1:quantity) + (POS2:price * POS2:quantity) + (POS3:price * POS3:quantity) etc. This formula runs over every item in a document. With this example, the total would be the "Total Net".
-
{{columnsumto(POS:price * POS:quantity)}}: Behaves like formula above but returns the sum of the column items up to its own row. So not all positions are run through, but only up to the own position (exclusive).
-
{{subtotal(POS:price * POS:quantity)}}: Returns the sum of all positions between two special fields.
You can select the following as special fields:
Example: In free field 1 you have stored the respective input voltage of a server. To evaluate the total voltage in the group, you can add the free fields 1 over all positions. Since you are only interested in the group special equipment, please use the formula "subtotal(POS:free field1)".enter the formula in the position of the document as shown in the picture.
The result looks as follows on the receipt:
The following variables are available in the formula module*.
Please replace the X with a number (when referencing a specific row) or omit it (when applying the formula to the current row). Alternatively, use the word "article" instead of "posX" if you want to access the article master data directly without considering item-specific modifications.
-
posX:id
-
posX:order
-
posX:article
-
posX:project
-
posX:description
-
posX:description
-
posX:internal comment
-
posX:number
-
posX:quantity
-
posX:price
-
posX:currency
-
posX:delivery date
-
posX:vpe
-
posX:sort
-
posX:status
-
posX:sales tax
-
posX:remark
-
posX:delivered
-
posX:quantity_delivered
-
posX:exploded
-
posX:exploded_parent
-
posX:logfile
-
posX:points
-
posX:bonus points
-
posX:mlmdirectpremium
-
posX:nobatteryallowed
-
posX:basic discount
-
posX:discount sync
-
posX:discount1 ... posX:discount5
-
posX:unit
-
posX:webid
-
posX:discount
-
posX:reorderxternpurchase
-
posX:tariff number
-
posX:country of origin
-
posX:article number customer
-
posX:free field1 ... posX:free field40
-
posX:delivery date truck
-
posX:subproject
-
posX:tax rate
-
posX:tax text
-
posX:erloese
-
posX:erloesefestschreiben
-
posX:purchase price maintenance
-
posX:purchase price
-
posX:purchase price original
-
posX:purchase priceid
-
posX:purchase price
-
posX:contribution margin
-
posX:formula quantity
-
posX:formula price
-
posX:without price
-
posX:potential delivery date
-
posX:discount amount
-
posX:tax amount
-
posX:discount block
-
posX:hide_in_pdf
-
posX:cost center
-
posX:sales_net_individual
-
posX:sales_net_total
-
posX:sales_gross_individual
-
posX:sales_gross_total
-
posX:customs_individual
-
posX:customs_total
-
posX:customs_duty
-
posX:customs_individual_weight
-
posX:customs_total_weight
If one of the values is not filled by the formula module, it may be due to the document type - not all values are available in all documents.
I have a production bill of material in which various items are stored, including an item that is only available as a bulk pack (pens) in a 12-pack. For the production item only 8 pieces are needed from the bulk pack, how can the BOM be created so that the employee from the warehouse knows that he has to take 8 pieces. If "8" were entered in the BOM, it would be 8 x 12 packs instead of 8 individual pens. In the bill of materials itself, 0.666 pieces of the bulk pack would have to be entered for the corresponding portion of one pack for the example. So that the warehouse employee does not have to calculate the actual number of pieces, this information can be additionally output in the production note with a formula in a free field.
To do this, first create two article free fields, one for the VPE quantity of the article and one for the formula that is to be output on the production slip. In the bulk article the formula below must be entered in one field and in the other free field the PU quantity, which flows into the formula for calculation.
Create free fields (Administration > Settings > System > Basic settings > Free Fields):
Create formula: The following formula can be used for this. Formula with rounding to a whole number: round(pos:quantity*article:freefield1,0)
Replace free field number accordingly with the number of the free field
Fill article free fields:
View of the production instruction with corresponding number of pieces (rounded up):
