Match Fields

[Ballycroy]

I am tasked with importing data (for reporting) from an existing database that is a DOS based billing system and over 25 years old.

Part of the data is formatted as follows:

3 00314476 09/22/07 17 40 13

3 00314476 09/22/07 17 40 13

3 00314476 09/23/07 17 40 13

3 00314476 09/24/07 17 40 13

I import this at half hour intervals.

As you can see from the above sample you can have many records with the same order number.

These are viewed through a portal that contains the Master Order Number.

I am told that there is no primary key from the DOS system which I could use to match incoming data.

Even if they created one, the DOS system handles any field changes by deleting the existing record and creating a new one using the same order number.

All fields that i import are changeable by the user in the DOS system except the Order Number.

The programmer who maintains the DOS system told me that there is no unique non-modifiable field in the system.

Even if there was the fact that it handles any changes the user makes by deleteing the existing record and creating a new one makes this a moot point.

Is there anyway to have a unique match field to sort out any changes that come from the DOS system.

[vi]

could they add a time-stamp to the DOS system? If the DOS system deletes the older records, then the latest time stamp is your ticket....

[Ballycroy]

Hi Vi

Thanks for your response.

I believe a creation time stamp can be added to the DOS system but only as precise as hours and minutes.

But because I have many records with the same order number how would that help me sort out which one(s) should be updated.

[Ballycroy]

Here is more detail on the process as it currently stands:

3 00338155 02/02/08 1 20

3 00338155 02/02/08 17 20

3 00338155 02/02/12 1 20

2 90435953 90435953 00338155 02/02/08

1 90435953 721 4579878 Acme Industries

Each import is comes in as the above sample.

Line item that starts with 1 is the account information (#90435953).

Line item that starts with 2 is the Order number (#00338155)

Lines item that start with 3 are the sub Order numbers.

I have a dump table for the import and then do a search for all line 1 items and import them to an accounts table. Search for line items 2 and dump them into an order table. Search for line items 3 and dump them into an child order table.

If the DOS user changes the date (or any other changable field) on a specific line item 3. Then another

import comes through with lines items 1, 2 and that changed line item 3. This import could come through at any time.

[sbg2]

Hi Ballycroy, am I correct in the assumption that you are sent a complete order, as it currently stands in the DOS system, each time an order is changed?

If so, this might be a case where you would want to deactivate (or if brave, delete) the old sub orders and account information before/after adding the current information. Without a better understanding of what you are provided from the DOS system and what you must maintain in your own system it is hard to offer advice.

A sample Database file and samples of the files you receive from the Dos system would probably make it easier for us to help you.

[Ballycroy]

Hi:

Yes a complete order is generated each time if it is new or has been modified.

Lines 1, 2 and 3 are sent each time.

If a change is made to an individual Line 3 item then just that item is sent from the DOS system along with the relevant Line 1 and 2 items.

I have attached a sample db and import data file and have included the script that imports the data.

Sample.tab.zip

TestImport.fp7.zip

Edited January 22, 2008 by Guest
Incorrect file reference in TestImport file

[sbg2]

Yes a complete order is generated each time if it is new or has been modified.

If a change is made to an individual Line 3 item then just that item is sent from the DOS system along with the relevant Line 1 and 2 items.

Those statements don’t match. In the first sentence you are saying you receive the complete order when an order has been modified but in the second you say you only receive the modified line item. Which is it?

This is how I am envisioning things work but I can only guess with what you have provided…

Say you distribute Widgets for the company that is providing you this DOS file. When Acme first places the order you would receive a file such as:

3 00338155 02/02/08 1 20

3 00338155 02/02/08 17 20

3 00338155 02/02/08 1 20

2 90435953 90435953 00338155 02/02/08

1 90435953 721 4579878 Acme Industries

The line beginning with 1 tells you that Acme Industries (account 90435953) is the customer. The line beginning with 2 tells you that the order number is 00338155, the bill to and ship to are for Acme (Account 90435953) and the order was placed on 02/02/08. Each line beginning with 3 tells you the quantity they have ordered and the price of each widget.

On 02/02/12 Acme changes the quantity of the last item from 1 to 125. What would the next file you receive look like?

A)

3 00338155 02/02/08 1 20

3 00338155 02/02/08 17 20

3 00338155 02/02/12 125 20

2 90435953 90435953 00338155 02/02/08

1 90435953 721 4579878 Acme Industries

or

3 00338155 02/02/12 125 20

2 90435953 90435953 00338155 02/02/08

1 90435953 721 4579878 Acme Industries

or something else?

[Ballycroy]

Sorry for not being clear on the process description.

It would be B.

[sbg2]

Then I'm not sure how you are supposed to determine which of the three sub orders (line items?) was changed?

[Ballycroy]

I was afraid of that.

Thanks for trying.

[Ballycroy]

Just got some more information.

It seems that a combination of order number + date + field3 + field4 is unique

Let's assume that:

3 00338155 02/02/08 1 20

3 00338155 02/02/08 17 20

3 00338155 02/02/12 125 20

2 90435953 90435953 00338155 02/02/08

1 90435953 721 4579878 Acme Industries

already exists in the database

In this import example below line 3 would be added to the database as a new record.

3 00338155 02/02/08 125 20

2 90435953 90435953 00338155 02/02/08

1 90435953 721 4579878 Acme Industries

If on the other hand the new import was:

3 00338155 02/02/12 125 20

2 90435953 90435953 00338155 02/02/08

1 90435953 721 4579878 Acme Industries

then because

3 00338155 02/02/12 125 20

already exists in the database it would have to be replaced by the newer item.

Is a scripted approach the best way to handle this?