|Changes-Only Interfaces||KEVIN RESCHENBERG|
Today we start a discussion of changes-only interfaces. Your PeopleSoft system probably connects with many other
systems, located both within your organization and externally. Among the outbound interfaces, most will be either transactional
or the "full-file" type, where all applicable data is sent. But a few recipients may require a "changes-only" file that
includes only records where one or more fields have changed since the last time the file was sent.
There are a few different ways to code a changes-only file. First, though, a bit of advice. Before building one of these,
verify that it is really needed! There will be occasions where a recipient could accept either type of file but requests
a changes-only file simply to reduce the file size and processing time. But programs that produce these files are generally
more complex, require more careful design and testing, and can even be risky. (What happens if the recipient loses a file
or forgets to process it? Will the situation ever be caught? After all, the next file will not include the data that
existed on the lost file.)
Is saving five seconds of processing time worth the increased complexity, the extra development effort,
and the increased risk of error that often applies to these files? No. Be sure that there is an actual need for
the changes-only file requirement before you even begin.
Now that you've verified the need, how will you develop the interface? I've seen three basic methods.
The first method involves monitoring data entry dates. The user might be asked to specify a date range, and the program
then selects the rows that fall within that range. ("Action" dates or entry dates are generally used, instead of effective
dates—but this depends on particular circumstances.)
This method will work, but it can be error-prone. First, we are relying on the user to enter the correct range. We hope that there
is no overlap between the previous and current runs. Similarly, we don't want to have any gaps in the date ranges. And will
correction-mode changes be picked up? Will changes entered on the end date, after the interface is run, be picked up in the
A second method involves comparing the old and new values of each field. This is often difficult to do unless the results
of the previous run were stored. In practice, a program might use a combination of this method and the date-driven method
to select the old and new data and compare it.
I'd like to suggest another technique. This method requires a new custom table that will be used to store entire records from
previous interface runs. The program uses simple "full-file" logic to select the data and format the interface file records.
Before each record is written, the program looks into the table to find the corresponding record from a previous run.
If an exact match is found, the record is not written. If the corresponding record is not found, or if it is found but
the data on the record is different, then the new record is written to the file and saved to the table.
This may sound complicated but it is actually very simple in practice. The custom table can be shared by all of your
changes-only interface programs. It can be used to force a full refresh of all of the data if needed.
It will even support interfaces that require an "add/change/delete" indicator.
Next week I'll explain all this in detail.