Predefined DB schemas and Bots mappings for EDIFACT

[Chris Simmons]

Bots comes with all the grammars for EDIFACT messages defined out-of-the-box. Is there a place--in Bots or elsewhere--where I can find DB schema definitions that match those messages? I think that a predefined DB schema and, optionally, Bots mapping for the message types I'm using (IFTMIN,IFTMCS, FTFCC, IFTSTA), would save a lot of time, even if I had to make some tweaks.

[Wassily]

Hi Chris

I do not think that exists (or even makes sense) as these messages are simply a way to exchange something that in your business app makes sense. Also the messages standard provides a lot of options which are then made specific with message implementation guides removing some or many elements again. 

What is it you try to accomplish with a database schema? Maybe I can point you to a different way. 

Maybe this is what you are looking for https://unece.org/untded-iso7372 ? 

Greets

Wassily

[Tom Westrick]

I was gonna say the same thing. With EDI, you are typically mapping the standard format to an internal format very specific to your business so every mapping is unique and sharing wouldn't be very useful. I've also never saved the raw incoming data directly to a DB. That defeats the purpose of Bots.

Can you share some more info about your use case Chris?

-Tom

[Chris Simmons]

Use case: My company chops down trees and sells those logs to sawmills. We use shipping lines such as Hapag-Lloyd to send the logs to our overseas customers. Part of the process is telling the shipping line what information they need to tell customs agencies, what we're putting on their boat, and who the intended recipient is. We can automate all of that by sending IFTMIN messages to the shipping lines. We can also automate the receipt and review of related information, such as the Bill of Lading that we must send to banks involved in the transaction, by receiving an IFTMCS message from the shipping line.

> the messages standard provides a lot of options which are then made specific with message implementation guides removing some or many elements again

Yes, however:

1. With the one partner I've worked with so far, they use the standard's messages exactly as described in the standard, with no customization of how specific fields are used. I expect that variations between the partners will be few.
2. As for data elements that aren't used at all by the partner, defining data elements but not populating them doesn't cost me any time if I'm using a template that defines all the data elements out-of-the-box. Even if there were a need to explicitly remove them, removing them would be a lot simpler than defining them.

> you are typically mapping the standard format to an internal format very specific to your business so every mapping is unique

I'm trying to confine code that deals with EDIFACT and its idiosyncrasies to a minimal module that outputs the data in a format that abstracts over EDIFACT's structure and instead presents something like a "shipping_instruction" SQL table or JSON object. I want to work with an abstraction that:

•  Uses descriptive names such as:
  • "consigner" rather than "CO" (DE 3035)
  • "measurement.value" rather than C174.6314
• Hides the concept of a "loop" entirely and just represents each instance of a loop as a table or object that has a many-to-one relationship with a parent.

I think it would be possible to define one such abstraction for each message type, and that abstraction application-independent. Application-specific code could then be written against that abstraction. This separation of concerns would allow me to--among other things--write the application-specific aspects of EDIFACT processing in Typescript, which is the language that 99% of my codebase uses.

[Tom Westrick]

You are on the right track. From my experience, we did one incoming mapping for each document type as it was easy enough to handle different scenarios within the standard for each customer. For outgoing, we went with a separate mapping for each customer as they were different enough that it was easier to maintain. There is less risk of causing issues with separate outgoing mappings when a change is made too. I'd rather affect one customer and not all of them.

My experience is mostly with goods being sold. So we had multiple formats of purchase orders coming in (X12, EDIFACT, XML, etc.). We took all of these and mapped them to a single internal purchase order object. From there, an internal program picked them up and did what we needed to do with them. We used XML files as the intermediary format. Bots does support writing directly to a database. JSON is possible too but from other conversations here, it sounds like Bot's might not support JSON arrays so that is limiting. You could look at the new Bots to see if JSON support has been improved - https://bots-edi.org.

Like I said though, mappings are very specific to your business use case - what you call each field (the "descriptive names") and what fields you map. I've never mapped every single field :)

[Wassily]

Hi Chris

Having worked myself with almost all the IFT* message types and all carriers that support them, I think you are fighting an uphill battle if you try to do what you will do. If you have implemented your messages with Hapag Lloyd (most others don't support IFTFCC, and I believe personally it is the wrong message for the job considering all the cross industry invoice standards being pushed into legislation), it will most likely not fit with any other carrier. 
I believe you would be best served in creating an "intermediate model" for yourself and abstracting what you need from and to your business model. Obviously you need to understand what are the important elements and what is not (looking at https://service.unece.org/trade/untdid/d11a/trmd/iftmin_c.htm I can be sure that at least 50% of this is not used....). And once you have your shortlist of important elements and how and/where you can and will maintain them, then have a conversion of that model from/to the EDIFACT messages. As a reference, there are the DCSA standards out there that give you a good json model for the shipping instruction. It would not be very difficult to map that into an EDIFACT, so you may then be able to use both technologies in parallel. 

Above is, what I believe to be the "normal" process for almost everyone that requires to integrate the same business objects with multiple external parties. 

Greets
Wassily

P.S. ConsignOr is "CZ", so if you do use "CO" than someone is not sticking to the codelist (https://service.unece.org/trade/untdid/d24a/tred/tred3035.htm) 

[Chris Simmons]

> There are the DCSA standards out there that give you a good json model for the shipping instruction. It would not be very difficult to map that into an EDIFACT, so you may then be able to use both technologies in parallel.

The DCSA model looks like what I need. That is incredibly helpful, and your suggestion to map from it to EDIFACT makes sense!

> ConsignOr is "CZ", so if you do use "CO" than someone is not sticking to the codelist

Haha, that was my mistake and goes to show why I want to use an intermediate model.

> most others don't support IFTFCC, and I believe personally it is the wrong message for the job

I meant to say IFTMCS. Is that what you consider the right message for receiving the Bill of Lading?