EDI Integration Problem

[Sonny Jim]

We have a general invoice table that has a shipper ID and Consignee ID that are linked to tables with details regarding these two entities, such as name, address, etc... We also have a carrier's invoice billed to us that will be coming in via EDI and will have shipper and consignee names and addresses but without our associated shipper or consignee ID's. I would like to eliminate manual entry to our invoice table by append its records with the invoice billing data from our carriers. (The data would come to us as an .csv file and we would convert this to an Excel file and copy/paste this into our Access invoice table.) Can anyone foresee a way to do this without adding all of the consignee and shipper address fields to our invoice table and rewriting a bunch of report queries? (Any recommendations on how to do this more efficiently is highly welcome.)

 

[gemma-the-husky]

not 100% sure exactly what yuo are saying

but

1. dont touch the csv - if you need to inspect it open it , and save as an excel sheet, but dont change the csv. - just import the csv into access

2. you can then add extra columns for the additional fields in yuor tables and not in the csv

3. it depends how you want to assign your ids to this table - do it manually by inspection for a start, and then see if you can find a way to automate it. although if its an EDI filoe, it shouldnt be too hard - as presumably there is only one id per file?

4. you can then have an append query to insert this data into your maion database

break it into logical stages, and it should work ok.

 

[Sonny Jim]

Thanks for the tips, I can sure use all that I can get. So, I can import the .csv directly that's great that will save some steps, and I should have remember the update query, I should review that.

Basically I have a form that thus far, we have been manually entering. The data we enter typically involves (3) customers and a service provider. There are hundreds of nearly each of these parties to choose from and we need to capture data regarding their addresses, and various other specifics. To aid the manual entry I use separate tables for each of these parties and a combo box to pick who is involved with each transaction.

Appending a single table doesn't seem like it would do the trick. It would seem as though I would need to compare the data coming in with all of the tables involved and make associations with all of their primary keys. When there are no matches then I would need to append them.

I'm not sure how I would make all the matches never mind append any of tables when necessary. It seems like a lot of work for a single transaction and a lot of coding unless I'm missing something.

 

[gemma-the-husky]

when the carrier's invoice comes in, you need to allocate your appropriate ids for the key data fields - you will probably have to set these values manually - maybe by using combo boxes

alternatively you might be able to create a correspondence table to translate the EDI file code, to your system code

in some cases the EDI file might include your reference

There's no magic bullet - either someone has to modify their system to record reference numbers for the trading partner - or you have to develop an appropriate mechanism to set them

 

[Atomic Shrimp]

It sounds like you need to build a cross-reference junction table, containing your customer IDs in one column and their corresponding unique reference that is used in the incoming CSV file in another - you can then use this table to bridge the gap between the two different sets of data.

 

[Sonny Jim]

Thank you Atomic Shrimp, I will look into that. Something has got to work, I can't be the only one with a relational database that imports data via EDI.

 

[Atomic Shrimp]

I've worked with EDI applications in a number of contexts - both generating outgoing files and parsing incoming ones, but it sounds to me like your file is coming out of an application that translates the highly-structured raw EDI message into a flat file table - am I right?

 

[Sonny Jim]

Yes, that is what I am expecting and I am told that the data will come from 7 - 8 different companies. The client I am working for has no IT department and the setup seems to have been very hastily conceived.

What is the best way to integrate this with my database?

 

[gemma-the-husky]

the thing is that this isnt a a simplistic problem.

each edi file from each supplier is likely to be in as slightly different format, even if nominally agreeing to a certain standard

the techniques to import and manipulate the data are likely to be quite complex (tortuous perhaps - you need to import the data, validate it, parse it perhaps, and then process it), though not difficult technically

however, its just very difficult to expect simple answers to general questions of this nature - sunce really there are none. each file needs careful examination. as an example, an EDI solutions provider I know, tends to charge about £1000 to do this for each additional trading partner. I think this is on the high side, but it may give an indication of the need for care in this area

 

[Atomic Shrimp]

the thing is that this isnt a a simplistic problem.

each edi file from each supplier is likely to be in as slightly different format, even if nominally agreeing to a certain standard

If the data is coming out of an EDI communications package, it ought to be structurally fairly consistent - although there will probably be unpopulated columns for some companies, depending on their own data.