Integration Using External Tables

It is not uncommon to encounter situations where you need additional data that is not generally available either from your EDI application/interface (outbound) or from your trading partner (inbound).

Consider the situation where your trading partner sends purchase orders but omits the item descriptions…this item description being a necessary piece of information for processing the received orders.  One option is to require that your trading partner includes this description for each item on the order – good luck trying to persuade a buyer to accommodate your request.  Another option is to modify your application to eliminate this requirement – this may require a considerable amount of redevelopment.

The solution: Create an external table to host descriptions for each partner item that can be accessed during the translation process.

This is just one example that can be applied to many different scenarios – the need to supplement the EDI or application data because it is not provided by the original data source.  This can be achieved by implementing an in-house file and using the translation map to access this external table and retrieve the needed information.

Many applications will already have tabled information just for this purpose – to eliminate the need to carry redundant data into the EDI data when it rarely changes and can be maintained inside master control files.  Customer master files might carry address information; item master files might carry product measurements and descriptions; other files might carry monetary conversions/rates.  Being able to access this information is the key to removing it from the base EDI interface.

When faced with this dilemma ask yourself the following questions.  First, does the needed data exist in a file on my system?  Second, if this data does exist in an external file are the proper keys on the file and available from the input data?  Third, how many pieces of data will be needed, as this will assist if the external file must be built for this purpose (to avoid later modifications)?

Consider a second situation where an internal order number must be assigned to each incoming purchase order – the number to be unique across all trading partners.  An external table with the “last used” number can be easily accessed during the translation process to retrieve, increment, include, and then write-back this incremented number.

Many translation products will also include the ability to create tables internal to that product (as opposed to external user / master files).  These types of cross reference tables are simple to create and are valuable if there are limited values that don’t often require change.

Implementing an external table approach to supplement missing data from the EDI process will make it possible to complete the process when only partial data is available.

2 thoughts on “Integration Using External Tables

  1. Pingback: Tweets that mention Integration Using External Tables | EXTOL Technology Blog --

  2. Pingback: uberVU - social comments

Leave a Reply

Your email address will not be published. Required fields are marked *