Lasernet FO Connector 6.1 and above 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 a 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 is running 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 only is required for the current Company.

Example of use

The following example illustrates how to make a sum of the SalesQty  multiplied 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's possible to use the proper tablenames in the SQL statement.

2. Add the fields which 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, a number is required and the number-field CashDiscPercent is therefore added as placeholder-field.

4. Select Custom for field.

5. Add the following statement in the 'Direct SQL' which multiply the SalesQty  from SalesLine  with the NetWeight from InventTable.

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

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: