When a customer delivers us an Excel sheet with information, it seldom happens that they exactly use the same fields as our table. Therefore, we need to create a mapping. Each field in the Excel sheet needs to be mapped to a field and missing fields need to be identified and discussed.

The Excel Sheet we get from the customer looks like this:

Let's try to map this information to our Logistics Registration Worksheet table, as follows:

Most of the fields in the Excel sheet can be mapped to a field in our table.

Some fields that are needed in NAV are not populated by the Excel sheet. For some fields this is okay, for example, the Registration Batch and Line No. fields are determined by the import.

Some other fields are more difficult. Unit of Measure, Gross Weight, Units per Parcel, and Unit Volume are left blank in the Excel sheet, but they are all needed in NAV.

For these fields, we need to come to an agreement with the customer. They need to either specify these fields or tell us whether they have default values. Let's look at our gaps and fill them in:

Reading the external data into the database is just one step in creating a reliable interface.

But what happens if the customer contacts us and says, "We sent you a file with 10 lines and the shipment document shows 9 lines". When we check our database the shipment does show 9 lines, but there is no way to check whether we imported the original 10 lines. At this stage, the imported Registration lines are deleted and the shipments are generated.

If this happens, we need traceability. In a well-designed interface, we should always create a table that exactly matches the imported data. This allows us to first check whether everything matches.

The data from this table can be processed but should not be deleted from the database and periodically cleaned up. This allows us to check whether things go wrong.

We will demonstrate this in a more advanced example.

This is an import and export interface that will use data pushing for the Pick Lines and data pulling for the Finished Picks and Exceptions. The interface will be timer-driven. Every minute we will poll for new data.

For this interface, we will use a combination of technologies that we discussed in this chapter. The main technology is DotNet interoperability.

In this interface, we will enable two types of logging. The first log will be to duplicate the RF tables in Microsoft Dynamics NAV and use them as a buffer. A second log will be maintained where we will save a copy of all T-SQL statements we generate. This will enable us to see what we generated if something goes wrong.

Let's look at the design of the interface we will be developing for this project:

The interface will be controlled from an Application Server. Each minute it will execute a codeunit that checks whether there are new Storage Lines that need to be exported. These lines will first be moved to the RF Pick Lines buffer table and then moved to the RF database using ADO and T-SQL. New Finished Picks and Exceptions from the RF database will be moved to Microsoft Dynamics NAV using the same technology and can then be processed.

To run the interface, we have created three codeunits and a table. The SQL Statement table is used to log each interface session.

The RF NAS Timer (123.456.730) codeunit is started from the NASHandler function in codeunit ApplicationManagement. It uses an indefinite loop.

Let's look at the C/AL code that is required to make this work:

OnRun()

RFInt.CreateConnectionString;
RFLoop(600000);

RFLoop(MilisecondsBetweenPolls : Integer)
WHILE TRUE DO BEGIN
  IF NOT CODEUNIT.RUN(CODEUNIT::"RF Helper") THEN
    ParseError;
  COMMIT;
  MaxMilisecondsSleep := 10000;
  FOR Count := 1 TO MilisecondsBetweenPolls DIV MaxMilisecondsSleep DO
    SLEEP(MaxMilisecondsSleep);
  SLEEP(MilisecondsBetweenPolls MOD MaxMilisecondsSleep);
END;

ParseError()
SELECTLATESTVERSION;
RFIntSetup.GET;
SynchID := RFIntSetup."Synchronization ID";

SQLStat.INIT;
SQLStat."SQL Statement 1" := 'ERROR : ' + GETLASTERRORTEXT;

SQLStat.Bold := TRUE;
SQLStat.SessionID := SynchID;
SQLStat.Type := SQLStat.Type::Error;
SQLStat.INSERT(TRUE);
COMMIT;

The SLEEP function is used to make sure the interface only runs each minute. By breaking the SLEEP function into smaller intervals it is possible to stop the Windows Service that executes this C/AL code in between the SLEEP command.

The RF Helper (123.456.732) codeunit is a wrapper codeunit that is used for error catching and maintaining readability.

During each run of the interface we create a new SQL Statement ID, which we can filter on to trace any errors:

OnRun()
SELECTLATESTVERSION;
RFIntSetup.GET;
RFIntSetup."Synchronisation ID" := RFIntSetup."Synchronisation ID" + 1;
RFIntSetup.MODIFY;
SynchID := RFIntSetup."Synchronisation ID";

SQLStat.INIT;
SQLStat."SQL Statement 1" := 
  '-SYNCHRONISATION STARTED- ID = ' + FORMAT(SynchID) + ' -';
SQLStat.Bold := TRUE;
SQLStat.SessionID := SynchID;
SQLStat.Type := SQLStat.Type::StartStop;
SQLStat.INSERT(TRUE);

COMMIT;

CLEAR(RFInterface);
RFInterface.SetSynchID(SynchID);

StorageLn.LOCKTABLE;
IF StorageLn.FINDSET THEN REPEAT
  RFInterface.CreatePickLines(StorageLn);
UNTIL StorageLn.NEXT = 0;

COMMIT;

CLEAR(RFInterface);
RFInterface.SetSynchID(SynchID);
RFInterface.ReadFinishedPicks;

COMMIT;

CLEAR(RFInterface);
RFInterface.SetSynchID(SynchID);
RFInterface.ReadExceptions;

COMMIT;

SQLStat.INIT;
SQLStat."SQL Statement 1" := 
  '-SYNCHRONISATION STOPPED- ID = ' + FORMAT(SynchID) + ' -';
SQLStat.Bold := TRUE;
SQLStat.SessionID := SynchID;
SQLStat.Type := SQLStat.Type::StartStop;
SQLStat.INSERT(TRUE);

COMMIT;

Then the three interface functions are triggered to synchronize the three required tables.

COMMIT

After each command we execute the COMMIT statement. This will make sure that everything in the database is stored up to that point. This is necessary since the ADO statements we create are outside our transaction. If our interface run rolls back, it might synchronize data that is already synchronized.

The RF Interface (123.456.731). Here, the actual ADO synchronization is done in this codeunit. This codeunit is SingleInstance. This will keep the ADO connection alive during the NAS session:

CreateConnectionString()
IF ConnActive THEN EXIT;


RFIntSetup.GET;
Database := RFIntSetup."Database Name";
Server := RFIntSetup."Server Name";

ConnString := 'Data Source=' + Server + ';' + 'Initial Catalog=' + Database + ';Trusted_Connection=True;';

SaveReadSQL('Connection ' + ConnString + ' opened on ' + FORMAT(CURRENTDATETIME),TRUE,0,0,0, ''),

SQLCon := SQLCon.SqlConnection(ConnString);

SQLCon.Open;
ConnActive := TRUE;

CloseConnectionString()
SQLCon.Close;

SaveReadSQL('Connection closed on ' + FORMAT(CURRENTDATETIME),TRUE,0,0,1, ''),

CLEAR(SQLReader);
CLEAR(SQLCommand);
CLEAR(SQLCon);
ConnActive := FALSE;

For the interface we use three DotNet variables.

Let's have a look at the three DotNet variables in more detail:

• SQLConnection: This is used for the connection with the database and to execute the T-SQL statements
• SQLCommand: The result sets of a SELECT statement can be read using this
• SQLReader: The reader is used to read the data and convert data types between ADO and C/Side

CreatePickLines()
CreateConnectionString;

SaveReadSQL('CreatePickLines',TRUE, 1, 8388608, 3, ''),

PickID := COPYSTR(StorageLn."Document No." + FORMAT(StorageLn."Line No."), 1, 20);

SaveReadSQL('Pick Document : '+PickID,TRUE,3,16711680,7,''),

WITH RFPickLines DO BEGIN
  "Pick Code" := PickID;
  Quantity := StorageLn.Quantity;
  "Terminal ID" := 1;
  "Display 1" := StorageLn.Description;
  "Display 2" := 'Warehouse ' + StorageLn."Warehouse Code";
  "Display 3" := 'Region ' + StorageLn."Region Code";
  "Display 4" := 'Shelf ' + StorageLn."Shelf No.";
  INSERT;
  SQLStatement := 'INSERT INTO [RF Pick Lines]' +
                  '([Pick Code],'+
                   '[Quantity],'+
                   '[Terminal ID],'+
                   '[Display 1],'+
                   '[Display 2],'+
                   '[Display 3],'+
                   '[Display 4])'+
                   'VALUES('+
                   Quote + PickID + Quote      +','+
                   FORMAT(Quantity)            +','+
                   '1'                         +','+
                   Quote + "Display 1" + Quote +','+
                   Quote + "Display 2" + Quote +','+
                   Quote + "Display 3" + Quote +','+
                   Quote + "Display 4" + Quote + ')';
END;

ExecuteSQL(SQLStatement);

StorageLn.Exported := CURRENTDATETIME;
StorageLn.MODIFY;

ReadFinishedPicks()
CreateConnectionString;

SaveReadSQL('ReadFinishedPicks',TRUE, 1, 8388608, 3, ''),

RFIntSetup.GET;
LastSync := RFIntSetup."Last Finished Pick";


SQLCommand := SQLCon.CreateCommand();
SQLCommand.CommandText := SaveReadSQL('SELECT ' +
              '[Reference Entry No],' +
              '[Terminal ID],' +
              '[Duration],' +
              '[Ready Date Time]' +
    ' FROM [RF Finished Pick] WHERE [Reference Entry No] > ' + 
      LastSync,FALSE,2,0,2, ''));

WHILE SQLReader.Read() DO BEGIN
  RFFinishedPick.INIT;
  RFFinishedPick."Reference Entry No." := 
    ReadInteger('Reference Entry No'),
  RFFinishedPick."Terminal ID" := ReadInteger('Terminal ID'),
  RFFinishedPick.Duration := ReadInteger('Duration'),
  RFFinishedPick."Ready Date Time" := 
    ReadDateTime('Ready Date Time'),
  RFFinishedPick.INSERT;
END;

RFIntSetup."Last Finished Pick" := Quote + FORMAT(RFFinishedPick."Reference Entry No.") + Quote;
RFIntSetup.MODIFY;

AdoRecordSet.Close;

Let's test the interface we have just designed and developed. In order to do this, we need to have records in the Storage Line table and the RF database needs to exist somewhere.

The RF database

To test the objects we have created for this solution, the RF database should exist on your system. This database can be created using a T-SQL script and should be executed on a Microsoft SQL Server machine.

Note

The script RF database.sql is part of the object files downloaded for this book.

Open the script in SQL Server Management Studio and click on Execute.

The test

Even though the C/AL code can run in the Windows client, we will run the test in the classic client. The reason for this is that the interface will run in the NAS, which will execute the C/AL code the same way as the classic client. Another reason to use the classic client is that this is the interface for the DBA to perform all their tasks.

To start a test run, open page RF Interface Setup (123.456.780) from the Object Designer.

Make sure that the Database and Server are correct. The server should be the SQL Server instance where the SQL Script was executed.

Note

The ADO connection uses the Windows Account NT AUTHORITYNETWORK SERVICE with Trusted connection. This user should have enough rights to insert and read data from the RF database.

To start a test run, click on the Test button.

If everything went well, the results should show both in the log and in the buffer tables and the RF database. Let's check them all.

SQL Statements

The SQL Statement log can be opened by either pushing the Log button on the RF Interface Setup form or opening the SQL Statements (123.456.781) form from the Object Designer.

SQL statements

The information on the form shows us exactly what the interface did during this run.

The buffer tables

When we open the buffer tables from the Object Designer, we can see that the interface moved the data from the Storage Line table into the RF Pick Lines table.

The RF Finished Pick and RF Exceptions are also populated with the records from the RF database.

The RF database

The last thing to check is the data in the RF database. The data in both databases should now be exactly the same.

This can be checked from the SQL Server Management Studio.