• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Using a foreign table in a JDE Application


Hi All,

I would like to create a JDE application to access historical data from our old GL. This data has been ported to a separate SQL7.0 database on the JDE database server.

I have created a data source and environment to the database as per instructions in JDE document ott-99-0045 (Table Conversion - preparing a foreign table data source).

I can see the foreign tables in the Table Conversion Input Table tab. So i think that i have set this part up correctly.

From the FDA I still cannot see the tables (or use table I/O) to get at the tables in this DB.

Researching jdelist i see that Sebastian Sajaroff answered this question briefly by saying that: "you have to create this datasource on OneWorld and all its related tables as OW regular objects."

Sebastian, can you or anyone, else elaborate what is meant by the second part of the answer. ie: create related tables.
Do i have to create these tables using the TDA?

The tables already exist in the DB with data, though not in the JDE Object librarian. If so, am i right in thinking that the data in the table will get blown away when i generate the table?

Or am i way off track?

Cheers and thanks in advance,

B7332 SP11.1 SQL7.0 NT4.0 Fat & Citrix 1.8
Noel Adlington
Financial Systems Admin
Nelson Marlborough District Health Board
New Zealand

Did you define the table in Object Librarian? I think it has to be
defined there before you can get to table I/O in event rules.

Also, if you don't want to lose your data, you might want to make a
backup copy before defining it in OL and then copy it back in
after creating the table in OL.

Danny McMillian


Reputable Poster

The foreign table definition does have to exist in Object Librarian. You do not have to actually 'generate' the tables, since they already exist. Just create the definition.

The tricky part is making sure the OW definition matches the physical table definition.

Best wishes,


Xe SP13.1, AS400 V4R3, CO-Oracle806, Co-A73c10, Citrix, NT JAS


VIP Member

You can not access any foreign table from any OneWorld design tool (TDA,
FDA) unless it has OneWorld specifications. You have two options:

1. Table Conversion
-if you only want to access data in the foreign table periodically then use
a TC (table conversion)
-you will not be able to access the table through RDA, FDA, etc as there are
no OneWorld specs
-everytime you want to access something in the foreign table you need to run
the UBE (TC) to import the data
-this is usefull for interfaces that are run at a pre-determined time
interval (ie. nightly)
-we use this to bring data into the Z tabes (F0911Z1, F0311Z1, F0411Z1, etc)
-NOTE: you can not access a foreign table through a view

2. Table Recreation (The Sebastion Solution)
-if you want to be able to access the foreign table as if it were a OneWorld
table (ie no UBE) then you need to recreate the table in the foreign
-this is usefull if your foreign database is a frequently accessed table
such as a Customer Master, A/P Ledger, etc.
-first backup the table in the foreign database
-next, recreate this table using TDA (table design aid) in the foreign
database using the exact column specifications as the original foreign table
(column titles do not need to match)
-generate the table in the foreign database
-add an OCM that maps this OneWorld table to the foreign database (System
and Server Map)
-copy the data from the foreign table back into this table
-create a view on this table for the foreign database system. Use this view
to rename the table and the column names for the foreign database system
-the advantage of this is that the foreign database has no idea that all you
actually did was create OneWorld table specifications for the table. The
foreign system is using the newly created SQL or Oracle view to access data
in this table
-since you now have specs for this table you can do anything you want with
it in OneWorld
-NOTE: OneWorld limits the naming of columns in tables to 20 characters
(doesn't matter since you're using a view to access the table)

Good Luck


Colin Dawes, MSc
City of Guelph
B7332 SP13.1, Oracle 8.1.6, NT 4.0, Fat & WTS


Legendary Poster
Are the specifications identical for the histhorical and current tables?
If yes then you can consider to Open.Handle the histhorical table.
It enables to open a table in different environments (I haven't tried it yet).
Please, check the appropriate sections of the Development Tools documentation to know more about how to use handles in Table I/O ER statements.
/... and of course, do not forget to share your results with us. Thanks./

Good luck,

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)