Using a Custom Export Connector with temporary table

Summary

Learn how to export Blanket Sales Orders with multiple lines, that can potentially have the more lines containing the same item, which need to be combined together into one single blanket sales line with a total sum of the quantity.

Who needs to read this article?

→ If you interested in using the temporary table functionality

→ If you need a custom connector to export data

Background on used functionality

What is a Custom Connector?

A custom connector is a plugin (codeunit) that allows you to add custom functionality to an internal document. In the Business Integration Solution, the custom connectors are provided with standard programming model which makes it much easier for a developer to recognize the triggers, both from syntactic and semantic viewpoint.

A custom connector contains very specific signature. If the codeunit does not pass the necessary requirements, you will get an error while attaching it in the internal document.

You can write your custom code under the triggers as required. Make sure a local variable Rec (passed by value) is used with data source pointing to the record table. Also, the Return Type is set to Boolean. Once the codeunit is created, you attach it to your internal document.

An Export Custom Connector only allows to have code on the OnRun trigger and the TableNo property is aligned with the Table chosen on the Internal Document.

Export Blanket Orders total sum of quantities per item

Create an export of Blanket Sales Orders, where the multiple blanket sales order lines are summarized into a single line and the quantity is a total sum of each individual blanket sales line.

To be able to successfully export this data, the following elements in Connectivity Studio are required:

Pipeline

Create an internal document

IntDoc

Add the Tables:
36 – Sales Header
37 – Sales Line
27 – Item

Use the screenshot to include the fields per table:
IntDocTF

Create a new Connection

Use the wizard to create a new connection
NewConn

Press OK and observe the following message
ConnCreated

Open the Connection Page.
ConnPage

Associate the created Internal Document by using the ribbon action “Documents”. Make sure to select the “Direction” as Export.
DocRel

Add the Elements to the Connection
AddElements

Configure each Element

Timer Event:

Select the Internal Document
Include at least the “Document Type” field with the filter Blanket Order

TimerEvent

XML Generator:

Select the Internal Document

XMLGen

Filewriter:

Enter a File Location
Define a File Name

FileWriter

Create the Pipeline:

Create a Blanket Sales Order

Make sure to use an item at least twice on different lines, to be able to generate the desired outcome.

Check configuration and Process the Connection

ProcessConn1

The first result without combining Sales Lines with the same items:
Result1

Please observe the first SalesLine (10000) and the last SalesLine (30000) have the same item (Bicycle).

Temporary table and Custom Connector

Now that the Connection works, we need to sum the total quantities per item. We can enable the “use a Temporary Table” and create a Codeunit to be used in the field “Connector Name” on the Export part.

Create a new Codeunit

Open the Development Environment and create a new Codeunit in an accessible object range. Open the properties of the Codeunit
Set the TableNo property to Sales Line
Create a variable SalesLine

Write the following code:
CustomConnCode

This code will loop through all the Sales Lines which use the current filterset from the message Connection and save the totals for each item in a Temporary state, when the “Use a Temporary Table” boolean is checked on the Internal Document.

Note! Don’t set the SalesLine variable to temporary = true.

Design the Internal Document

Make the following changes to the Document:
IntDocTempT

Enable the “use a Temporary Table”
Add the created Codeunit to the field “Connector Name”

Re-run Check Configuration and Process the Connection

When these two settings have been applied to the Internal Document is it time for the final check on the output.
Result2

Observe the result and notice that there are now only two sales lines remaining and the total quantity of the SalesLine “Bicycle” is now the sum of the two individual lines (25+13=38).

The “INSERT” action on the Sales Line in the create Custom Connector Codeunit has not caused new entries in the table because of the “Use a Temporary Table” functionality.