++++++++ PROBLEMS ++++++++ 1> Map one instance of an N1, N2, N3 and N4 segment set where more than one set is received. 2> String manipulation within a formula. +++++++++ SOLUTIONS +++++++++ The Following input data and three (3) reports show us examples of the following: => Mapping the N1, N2 and N3 segments such that only the address in the N1 segment with a qualifier "ST" is read in.(This example uses the qualifier "ST", but any qualifier can be used as per the user requirements) The N4 segment is not shown but the all the information mentionned in this document can be applied to the N4 segment as well. => String manipulation within a formula using EDI keywords and EDI function keywords. > In order to load the infomation from the N1, N2 and N3 segments for the qualifier "ST", the following steps are required: => Map the N1 segment position 01, if qualifier, to a non-used field in your file, in this case to order.filler1, as shown in the SEGMENT OVERRIDE REPORT below, for segment N1, position 01,line 52 and 53. => Create a Keyword for the field oder.filler1, see ORDFILLR in the EDI KEYWORD LIST below. => Create a Keyword for order.ship-address, see ORDSHADD in the EDI KEYWORD List below. => Map the N2 and N3 segments with incoming formulas that verify the field order.filler1, |ORDFILLR|. If it's equal to "ST" assign the incomming data, otherwise assign the value that's in order.ship-address[1], |ORDSHADD|[1]. When the next N1 segment is read, if there are other N1 segments like in this case, order.filler1 will be assigned "BT", thus the information will in the N2 and N3 segments will not be loaded. > In the formulas, verification can be done on the incoming data and different values can be assigned to the database fields. For example, if you look at the N1 segment, position 4, line 51, in the SEGMENT OVERRIDE REPORT, you will see that the following will be assigned to order.ship-address element 1: => If the information in element 4 begins with a "W", the word "WALK" is assigned else if element 4 begins with a "F", the words "FLY BALL" is assigned else if element 4 begins with a "H", the words "HOME RUN" is assigned else if element 4 begins with a "Z", the words "GROUND BALL" is assigned else if non of the above is true the word "STRIKE" is assigned. > Let's say that in the order.ship-address, elements 1 you want to assign as much information as possible from the N1 and N2 segments before assigning information to order.ship-address, element 2. In the SEGMENT OVERRIDE REPORT, you will see this being done in the following segments: => Segment N1, element 4, line 51 => Segment N2, element 1, lines 50 and 51 => Segment N3, element 1, line 50 These formulas use a combination of keywords from the EDI KEYWORD LIST and function keywords from the EDI FUNCTION KEYWORD LIST, in order to calculate the length of the information in the address fields and of the information in the N2 and N3 segments. Based on the space available in the order ship-address fields full words are assignned to the end of the order.ship-address fields until there is no more space available for full words. > If you look at the SEGMENT OVERRIDE REPORT, N3 segment line 54, you will see "\025" mapped to order.ship-address element 2. The "\025" is a non-printable character. The reason for this is that in standard EDI, if nothing is mapped to the shipping address fields in the order file or the fields are blank, the client's address is assigned to these fields. Should you want to avoid this, the way around it is to assign a non-printable character to the ship address fields that you want to remain blank if there's no EDI information in the incoming data. +++++++++++++++++++++++++++++ INPUT DATA AND REPORT SECTION +++++++++++++++++++++++++++++ ========================================================================================= INPUT DATA ========== ISA*00* *00* *ZZ*GTR *ZZ*GTR *970724*1002*U*00200*000000002*0*P*> GS*PO*GTR*GTR*970724*1003*2*X*003020VICS ST*850*0001 BEG*00*NE*10061*00*970715**na CUR*ZZ* HM*0001 FOB*DF*DE*LA - WAREHOUSE ITD*ZZ*ZZ*0*970725*10*970814*30 N1*SH*BASEBALL SHIP CO. N2*ATTN: FIRST BASEMAN N3*P.O. BOX 345243 N4*COLUMBUS*OH*4326777 N1*ST*XRAUSPFO FOR MARYLAND*10*W23R7B N2*SMR 301 OLD BAY LANE N3*HAVRE DE GRACE OH 21078-0206 N1*BT*DFAS - COLUMBUS CENTER N2*ATTN: DFAS-CO-SEG N3*P.O. BOX 182317 N4*COLUMBUS*OH*432186231 N1*ZZ**10*W23R7B PO1*1*103*EA*8.1*CP*IN*mno*VC*1001 PID*F****BASEBALL BAT, WOOD, 32" PID*F****BATE DE BEISBOL DE MADERA 32" CTT*2 SE*15*0001 GE*1*2 IEA*2*000000002 ======================================================================================== SEGMENT OVERRIDE REPORT ======================= XXXXXXX 74-00 with MXP 7.4a-00 Page: 0 Segment Override List (Incoming only) As-of date: 07/06/98 15:51 Partner: Version: Transaction Set: ------------------------------------------------------------------------------------- Segment Sequence Position Description Dictionary no. Level <------------------------ Incoming --------------------------> Line File-name Translation table Qualifier Field-name Element Countries Add/Assign Formula ------------------------------------------------------------------------------------- N1 030 01 Entity Identifier Code 0098 1 50 st 0 * no 51 st 0 * no 52 order st filler1 1 * no 53 order zz filler1 1 * no N1 030 04 Identification Code 0067 1 50 order ship-name 0 * no |CURRELEM| + " " + |ELEMENT|[3] 51 order ship-address 1 * no if |CURRELEM| begins "W" then "WALK" else if |CURRELEM| begins "F" then "FLY BALL" else if |CURRELEM| begins "H" then "HOME RUN" else if |CURRELEM| begins "Z" then "GROUND BALL" else "STRIKE" N2 031 01 Name 0093 1 50 order ship-address 1 * no if |ORDFILLR|[1] ne "st" THEN |ORDSHADD|[1] else if |ADDRSZN2| le 40 then |CCATADD1| else |SHIPAD1A| 51 order ship-address 2 * no if |ORDFILLR|[1] ne "st" then |ORDSHADD|[2] else if |ADDRSZN2| le 40 then "" else |SHIPAD2A| N3 032 01 Address Information 0166 1 50 order ship-address 2 * no if |ORDFILLR|[1] ne "st" then |ORDSHADD|[2] else if |ELEMENT|[3] ne "" then if |ADDRSZN3| le 40 then |CCATADD2| else |SHIPAD2B| else "" 51 order ship-zip 0 * no if |ordfillr|[1] = "ST" then (if |ELEMENT|[3] eq "" then substring(|CURRELEM|,(r-index(|CURRELEM|," ") + 1),length(|CURRELEM|)) else |SHIPZIP|) else |SHIPZIP| 52 order ship-st 0 * no if |ORDFILLR|[1] = "ST" then (if |element|[3] ne "" then |SHIPST| else substring(|CURRELEM|,|statecnt|,2)) else |shipst| 53 order ship-city 0 * no if |ORDFILLR|[1] = "ST" then (if |ELEMENT|[3] ne "" then |SHIPCITY| else substring(|currelem|,1,(|statecnt| - 2))) else |SHIPCITY| 54 order ship-address 2 * yes "\025" N3 032 02 Address Information 0166 1 50 order ship-city 0 * no if |ORDFILLR|[1] = "ST" then (if |CURRELEM| eq "" then |SHIPCITY| else substring(|CURRELEM|,1,(|statecnt| - 2))) else |SHIPCITY| 51 order ship-zip 0 * no if |ORDFILLR|[1] = "ST" then (if |CURRELEM| eq "" then |SHIPZIP| else substring(|CURRELEM|,(r-index(|CURRELEM|," ") + 1),length(|currelem|))) else |SHIPZIP| 52 order ship-st 0 * no if |ordfillr|[1] = "st" then (if |currelem| = "" then |shipst| else substring(|currelem|,|statecnt|,2)) else |shipst| 53 order ship-address 2 * yes "\025" End-of-Report. 5 segment override record(s) Printed. Memo: ==================================================================================== EDI KEYWORD LIST ================ XXXXXXX 74-00 with MXP 7.4a-00 Page: 0 EDI Keyword List As-of date: 07/06/98 15:52 ------------------------------------------------------------------------------------ Keyword Description File-name Field-name Field-type Status ------------------------------------------------------------------------------------ ORDFILLR Unused field in order order filler1 character ORDSHADD Order's ship-to address order ship-address character SHIPCITY order's shipping city order ship-city character SHIPST order's ship state order ship-st character SHIPZIP order's shipping zip order ship-zip character End-of-Report. 6 keyword(s) Printed. Memo: Please forward printout to root ==================================================================================== EDI FUNCTION KEYWORD LIST ========================= XXXXXXX 74-00 with MXP 7.4a-00 Page: 0 EDI Function Keyword List As-of date: 07/06/98 15:59 ------------------------------------------------------------------------------------ Function Keyword Description Return Value Function Formula ------------------------------------------------------------------------------------ ADDRSZN2 Size of sh-addr1 + n2 segment integer (length(|ORDSHADD|[1] + "," + |CURRELEM|)) ADDRSZN3 Size of sh-addr2 + n3 segemnt integer (length(|ORDSHADD|[2] + "," + |CURRELEM|)) CCATADD1 Concat sh-addr[1] + N2 character if length (|CURRELEM|) > 0 then substr(|ORDSHADD|[1],1, length(|ORDSHADD|[1])) + "," + |CURRELEM| else |ORDSHADD|[1] CCATADD2 Concat sh-addr[2] + N3 character if length(|CURRELEM|) > 0 then substr(|ORDSHADD|[2],1, length(|ORDSHADD|[2])) + "," + |CURRELEM| else |ORDSHADD|[2] LORDSH1 Length of |ORDSHAD|[1] integer length(|ORDSHADD|[1]) LORDSH2 Length of |ORDSHADD|[2] integer length(|ORDSHADD|[2]) SHIPAD1A Ship address 1 + substr(N2) character if length(substr (|CURRELEM|,1,r-index (|CURRELEM|," ",(40 - |LORDSH1|)))) > 0 then substr(|ORDSHADD|[1],1, |LORDSH1|) + "," + substr(|CURRELEM|,1, r-index (|CURRELEM|, " ",(40 - |LORDSH1|))) else |ORDSHADD|[1] SHIPAD2A Ship Address 2 + substr(N2) character substr(|CURRELEM|, (|LORDSH1| - (index (|ORDSHADD| [1],",") - 2)),(40 - index( |ORDSHADD|[1],","))) SHIPAD2B Ship address 2 + substr(N3) character if length(substr( |CURRELEM|,1,r-index( |CURRELEM|," ",(40 - |LORDSH2|)))) > 0 then substr(|ORDSHADD|[2],1, |LORDSH2|) + "," + substr(|CURRELEM|, 1,r-index(|CURRELEM|, " ",(40 - |LORDSH2|))) else |ORDSHADD|[2] STATECNT to get state integer length(right-trim( substring(|CURRELEM|, 1,r-index(|currelem|, " ")))) - 1 End-of-Report. 10 Function keyword(s) Printed. Memo: Please forward printout to root