Table Conversions - Demystified
Written by Jeff Geiger
In my several years of working with J.D. Edwards OneWorld, I have come across many implementations that needed to import data into or out of the new system either as a one time conversion or as an ongoing interface to external systems. Most of the time, we chose table conversions because the functionality that UBEs provided was not enough to fulfill our needs. In this article, I will clear up some of the most common questions about table conversions. I do not go into the technical nature of table conversions (that is documented in J.D. Edwards’ documentation and it is dependent on what you want to accomplish), but I will make you more comfortable with what table conversions are and what they can do for you.
What is a table conversion?
A table conversion is a method for getting data from one set of les or tables to another set of les or tables. Table conversions allow for text input and output along with the ability to write to tables in configured databases in the OneWorld system. In UBEs, you only have the ability to write from one table to another or to output text to a -at le containing the information on the report.
How are table conversions used?
Table conversions can be used to interface with any internal or external system including legacy, replaced, external or other J.D. Edwards systems. A couple of specific examples might include initial customer master loads, loading of supplemental data, creating A/P bank les, interfacing with a payroll system and creating les to send to the bank for auto deposits.
J.D. Edwards contains some table conversions that are part of the payroll, A/P and Z-le processing. These write to and read from external text les, but out of the box may not be exactly what customers and their interchange partners need.
How do you know to use a table conversion?
Anytime you need to read data in from a flat file, a remote database or another J.D. Edwards table, possibly in another environment or you need to manipulate the data, putting it in either a remote database, a J.D. Edwards database or a text le, you may need a table conversion. Table conversions allow you to span data sources. For instance, you may want to write a routine that refreshes custom data from one environment to another.
One key difference between UBEs and table conversions is that table conversions do not create output. If you need output, you may need to get creative and initiate a write to a text le or a database that can be reviewed later. Simply puttable con versions are very similar to UBEs, but allow for almost any input to be translated, manipulated and written to almost any output.
Steps to create a table conversion.
In OneWorld Xe, open the Object Management Workbench (OMW) and choose ”Add.” Click on the batch application radio button. Click ”OK.” On the next screen, you will be asked to fill in the table conversion name, description and additional data. Fill this in according to your company’s standards.
Now, you are ready to design your table conversions, so enter the table conversion design aid. You must set the following up within the tool:
What Source & Target data sources to use.
Inputs
Outputs
All the other settings (see the documentation manual for these
Inputs
If you are reading a text file, you can either read in line by line (whole line as a single column in a record) or you can format the data before it comes in. This is handy when formatting dates and numbers. You can even set up multiple line types with different layouts.
Outputs
If you are writing to a Database or Text File, create your mappings and click on ”Issue Write for This Event” check box. This will insert a line in your event rules that inserts into each of your output tables.
More information is available in the Technical Documentation portion of your OneWorld Xe documentation on CD or available on the Knowledge Garden. I suggest you print and read the entire portion to understand the power table conversions contain.
What are some common models for table conversions?
There are always many ways to solve the same problem. With table conversions there are several models you can use to export to information in remote tables or text files. It all depends on the amount of security, safety and redundancy you need for the data. If you are creating a one-time extract, it doesn’t make sense to make it a complicated process, but if you are planning on handling recurring exports of sensitive and important data, you may want to build it robustly.
Simple In/Out
The pros with the simple in/out process are that it is simple, easy and straight forward, you have one or more inputs and one or more outputs and the table conversion handles the whole thing.
The cons are that it is not easy to re-map the inputs or outputs using the same mapping logic because when you change an input or an output, all of the mappings disappear. This makes sense because the mappings cannot be kept if the things being mapped to or from have changed.
Hybrid (Payroll uses a process like this)
The pros with the hybrid process are that it is easy to set up and is a simple concept to follow; you can easily map the output to any location by creating another second table conversion.
Complex (A/P uses a process like this.)
The only differences with the complex method from the hybrid method are that the complex has a series of applications and purge programs that modifies the data and manages the data in the output and does not get directly written to the output file. There is a manual process that must be kicked off to write the file from the input to the output. Once the output is written, the batch is marked as exported.
Event Rules and Mappings
Within table conversions you have the ability to utilize all of the power of event rules. You can create run time variables just as any event rule in OneWorld allows and you can format your inputs and outputs as variables.
Once you have all of the fields mapped and have the runtime variables defined, you can manipulate the inputs to create your outputs by using any of the available functionality of event rule programming. It is truly limited only to your imagination and creativity.
Other Alternatives
Table conversions and UBEs are two methods to get data from one place to another with the power to manipulate that data in just about any way you wish. Other methods may include direct data access from an external system through ODBC or using the Auto Pilot toolset.
Using ODBC, you can virtually write a program in any programming language (C, C++, Visual Basic, Delphi, PowerBuilder, etc.), connect to the database through ODBC and insert or update records in any J.D. Edwards table. It is recommended, however, that you use existing J.D. Edwards tools for as much of your data conversions as you can.
The Auto Pilot tool can also be used in place of some table conversions. It basically takes input from an Excel spreadsheet and processes it with an online application according to a script that you create. If you have to perform complex data validation while processing of data that would normally happen through an interactive application, you can use Auto Pilot. With the tool you merely write a script and process your conversion data just as if someone was typing that data into the fields and clicking on the menus and buttons in the application.
It is a very valuable utility, so make sure you check into the other benefits it can provide to see if it is a worthwhile investment for your organization. The drawback to the Auto Pilot tool is that you need to be on a version of J.D. Edwards OneWorld that supports it and you must own a license for it.
Summary
Table conversions are very powerful tools in your toolbox. Deciding whether to use a UBE or a table conversion comes down to the following:
Can you do without output such as a report?
Do you need to write out to or read from a text file or table in another environment?
If you answered yes to either or both questions, table conversions are most likely your choice. Just understand that a Table Conversion is nothing more than a process that can take one or many inputs and write to one or many outputs, no matter where the data resides. You can even write directly to any J.D. Edwards table using Table I/O without using the insert event of the table conversion.