Calculations - Custom / Direct SQL

Lasernet FO Connector 6.1 and later contains the option to fetch and make calculations through direct SQL in the Query wizard.

This is useful if there is a need for a calculation and/or if there is a need to use an SQL function - when rounding a number is required, for example. It is possible to use any SQL functions with the Custom/Direct SQL in the Query wizard.

The Direct SQL runs across all companies within Dynamics 365 Finance and Operations, so it is important to use Cross company and specify the current company (dataAreaId) when data is required only for the current company.

Example of Use

The following example illustrates how to multiply the sum of the SalesQty by the NetWeight.

1. Select Yes in both the Group by and in Cross company.

The tables SalesTable are renamed to SalesTableQuery to ensure it is possible to use the proper table names in the SQL statement.

2. Add the fields that are required for the WHERE-clause in the SQL statement. In the following the fields SalesId and dataAreaId are added:

3. Add a field that is used as a placeholder field - the placeholder field will return the value from the SQL statement and not the value from the field.

In the following image, a number is required, and the number field is therefore added as a placeholder field.

4. Select Custom for the field.

4. Add the following statement in the Direct SQL, which multiplies the SalesQty from SalesLine with the NetWeight from InventTable.

The tables SalesLine and InventTable are the tables at the SQL server and are used for the select statement.

Note:

It is important to use the proper table names in the SQL statement and to use the data-source name added in the Query wizard for the "WHERE” clause.

SELECT Round(SUM(SalesLine.SalesQty*InventTable.NetWeight),1) from SalesLine join InventTable on InventTable.ItemId=SalesLine.ItemId AND InventTable.dataAreaId = SalesLine.dataAreaId where SalesLine.SalesId=SalesTableQuery.SalesId AND SalesLine.dataAreaid=SalesTableQuery.dataAreaid

6. Add a range containing the dataAreaId to ensure only records from the current company are fetched.

The XML file will have the fields SalesId and dataAreaId and the field CasDiscPercent contains the sum of SalesQty multiplied with NetWeight rounded to 1 decimal for all the saleslines: