Use calculations in a message
Concept
You can make a calculation on a message. For example, you can calculate the total quantity of all sales lines of a sales order. The calculation is applied to each run of the message.
BisHistoryCalculation table
Use the BisHistoryCalculation table to apply calculations to a message.
The BisHistoryCalculation table has these fields:
- HistoryRecordId: Refers to the applicable BisHistory table record. For each message run, a record is added to the BisHistory table. If a calculation is done, the calculation record is linked to the message run record in the BisHistory table. You can use this field to get the message run RecId to get the applicable calculation. To fill the HistoryRecordId field in the BisHistoryCalculation table, use the custom class BisGetHistoryId. Use this field as a key field in the message mapping.
- RefRecId: You can store the record ID from the source document based on which you want to group the calculation results. So, the calculations are done separately for each instance of the source record in the message run. If you use this field, use it as a key field in the message mapping.
- Name: You can define the name of the calculation. If you use several calculations in a message, use the calculation name to distinguish the calculation results in the BisHistoryCalculation table. If you use this field, use it as a key field in the message mapping.
- Qty: Stores the calculation result in the BisHistoryCalculation table. In the filed mapping, for this field you define which calculation is done. You can also use this field as a target field in the field mapping of a next record.
The BisHistoryCalculation table has several edit methods. These edit methods calculate a new value using the current value in the Qty field of the BisHistoryCalculation table and the new value from the source field. the result of the calculation is set as the new value of the Qty field of the BisHistoryCalculation table.
You can use these edit methods:
- editAddValue: Adds the new value to the current Qty field value.
- editMultiplyValue: Multiplies the current Qty field value by the new value.
- editPercentageValue: Calculates the new Qty field value with this formula: Current Qty field value / 100 * new value = new Qty field value.
- editMinusValue: Subtracts the new value from the current Qty field value.
- editDevideValue: Divides the current Qty field value by the new value.
Source document setup
If you want to use the RefRecId field of the BisHistoryCalculation table, to indicate the source document record for which to group the calculations, add the RecId field.
Target document setup
To the target document, add the:
- BisHistoryCalculation table as a record. Make sure, for the record, the History field is set to 'Yes'.
- BisHistoryCalculation table fields to the record.
Message setup
To the message mapping, add a mapping to map the fields of the BisHistoryCalculation target document record:
- HistoryRecordId:
- Key field: Select
- Custom (Field options): Select
- Handler: BisGetHistoryId
- RefRecId:
- Key field: Select
- Map this field to the RecId field of the applicable source record.
- Name:
- Key field: Select
- Constant: Select
- Constant value: Enter the freely definable calculation name.
- Qty:
- Map this field to the source record field which value must be used in the calculation.
- Edit method (Field options): Select
- Method location (Field options): From table
- Edith method (Field options): Select the applicable edit method.
To the message mapping, also add a next mapping to get the calculated value from the Qty field of the BisHistoryCalculation table and write it to a target field. For this record, in the Source record field, on the lookup, change the Direction to Target, and select the BisHistoryCalculation record. Make sure, the BisHistoryCalculation mapping is done before this mapping.
Example
You want to export the total ordered quantity for each sales order. So, all sales line quantities are added together. You can, for example, use this total sales order quantity to check the total number of items to be in the delivery.
This example shows the setup that is required to calculate the total sales order quantity.
Source document:
The source document is a D365 FO document with these records:
- SalesTable: Add the desired fields and, at least, add the RecId field.
- Salesline: Add the desired fields and, at least, add the QtyOrdered field.
- EndOfSalesTable: Add the SalesId field
Target document records:
The target document is an external file-based document, for example, an XML document with these records:
- SalesOrder
- Add the desired fields.
- SalesLine
- Parent record: SalesOrder
- Add the desired fields.
- OrderTotals
This record is required to export the calculated total quantity ordered.
Set up the record as shown in this picture: - BisHistoryCalculation
Set up the record as shown in this picture:
Message setup
To the message mapping, add a mapping to map the fields of these target document records:
- SalesOrder:
Source record: SalesTable
Map the target record fields as desired to the fields of the SalesTable source record.
SalesLine: - Source record: SalesLine
Map the target record fields as desired to the fields of the SalesLine source record. - BisHistoryCalculation:
Source record: SalesLine
Set up the field mapping as shown in this picture:
For the HistoryRecordId field, define the required handler:
For the Qty field, define the desired edit method: - OrderTotals:
Source record: EndOfSalesTable (Used to indicate the end of each sales order that is processed in the message run. As a result, the calculation stops, and the calculated quantity can be used. For the next sales order in the message run, the calculation is restarted.)
Set up the field mapping as shown in this picture:
Note: In the Source record, on the lookup, change the Direction to 'Target' and select 'BisHistoryCalculation'.