Demo 2

12 videos
Back to XTranslator page

#1. Setup data source name (DSN) for ODBC connection

  • We use ODBC connection to extract data for EDI output files
  • Setup data source name using ODBC applet in Windows Control Panel
  • In this example MySQL database is used
  • Add datasource and test connection. MySQL allows multiple databases stored on one database server
  • We setup our database called 'edi'
  • We use 64bit ODBC driver because our Windows is also 64bits. Do not pick 32bit driver if your Windows is 64bit

#2. Setup data source name (DSN) in XTranslator

  • We setup ODBC connection inside translator
  • Once connection is tested and working we setup SQL queries and import field names
  • Check fields to make sure data types and sizes match database schema
  • Run map. This loads database data for preview.
  • Check that loaded data populates fields
  • We add second query. Both queries are independent at this point. Please watch next video.

#3. Link queries in master-detail relationship

  • We setup INVOIC table as master and INVOIC2 as detail
  • MasterQuery property and special @ symbol is used to setup master-detail relationship between queries
  • ID_NO field ties queries together. It is one-to-many relationship
  • Single record of INVOIC may have many INVOIC2 records pulled from database
  • Translator will fetch single INVOIC record, then query INVOIC2, fetch one-or-more INVOIC2 records and then fetch next INVOIC record, and so on
  • Once queries are mapped to the output side this lets us produce looping segments on the output side
  • We run translation to make sure our queries still work

#4. Import EDI template

  • We import EDIFACT template using Template Wizard
  • Make sure EDI release number and message name match requirements
  • Then we set output file name and add #13#10 to segment separator. This will place carriage return and line feed at the end of each segment.
  • #13 is carriage return and #10 is line feed character
  • We also set Encoding to European. This will allow export of certain European umlaut and accent characters
  • Create first mapping and test the map to make sure output path is writable

#5. Modify queries by removing and adding fields

  • We add another detail query INVOIC3 and tie it to INVOIC query
  • Then run the map to make sure it still works
  • Delete and add fields via popup menu

#6. Add more segments on the output side

  • We have requirement to produce two different NAD segments with qualifiers SR and BY
  • Make sure input fields are setup to add additional mappings
  • Then map first NAD to two input fields
  • Add new segment by Copy and Paste. Select parent segment during Paste
  • Map newly added segment
  • Test changes by running the map

#7. Steps for mapping subelements

  • We map subelements. They are initially mapped same way as elements
  • But once mapping is established it is modified to target specific subelement
  • While this video shows EDI subelement on output side same steps apply for mappings when EDI subelement is on the output side
  • In all cases Mappings screen is used to alter the mapping from element mapping to specific subelement mapping

#8. Generate control numbers on the output side

  • EDI control numbers should be generated and remain unique
  • Translator stores control numbers inside file set via IniFile property
  • In this example we name it controlnumbers.ini
  • We also setup unique names for each generated control number attached to specific elements using Sequence property
  • UNH element 1 is mapped so it does not have to be set to Mandatory=true
  • But UNT element 2 is not mapped so it has to be set to Mandatory=true to get produced on the output side
  • If you want to reset control numbers to 1 simply delete file controlnumbers.ini

#9. Setup global properties that affect whole translation

  • We adjust input side properties that affect whole translation
  • It is better to make such changes at the start of the mapping
  • ChangeCase property sets specific lowercase or uppercase rule
  • NullValues property changes how database fields with NULL or blank values are treated
  • Translator can ignore NULL values or treat them same as fields with blank values
  • The difference is subtle but important: depending on it blank values will cause empty EDI elements appear on the output side

#10. Adjust field size and padding

  • If field has to be fixed size adjust Processing Fixed Length properties
  • We adjust both length and padding to showcase few different formats

#11. Setup formatting properties on the output side

  • Translator comes with number of formatting functions available via Format and ExternalFunction properties
  • We use =Form(11_) function to prepend '11' to the output element. Underscore '_' is used as placeholder to insert mapped data.
  • In our case input contains '85'. Resulting output is '1185'. If we would have =Form(_11) then result would be '8511'
  • We also setup fixed constant value on last element using =Value() function
  • Since element is not mapped it is not enough to just set function. Mandatory property has to be set to 'true' as well
  • If Mandatory=false and when element is not mapped then no output is produced
  • Rule is simple: you either map the element or set it to Mandatory=true
  • If segment has no mappings and you want it produced then segment also has to be set to Mandatory=true

#12. Segment counting functions

  • We need to count segments produced in the transactions and output segment count in one of the elements
  • SpecialFunction property has number of functions to count segments
  • For EDI X12 maps use ProductSECount or ProduceMultipleSECount
  • For EDIFACT maps use ProduceUNTCount or ProduceMultipleUNTCount
  • We have to set both output side segment and element to Mandatory=true in order to execute function and get segment count
  • If segment has elements mapped then leave Mandatory=false

Bonus. Pro tips

  • Design your database tables first. When you have all the fields it is faster to map then all at once rather than adding fields gradually
  • Most EDI formats only have 2 or 3 major loops. Something that can be called Header, Detail and Subdetail
  • Try to avoid creating lots of tables to represent EDI in your database. Create only 2 or 3 tables that will hold all data for specific EDI message type
  • Avoid loading data right into business production tables. Use staging tables instead. Validate loaded data and only then move it into production tables