5 Replies Latest reply: May 15, 2014 9:55 AM by Alan _ RSS

    Variable Line Record EDI

    Alan _

      I have an EDI file that I want to capture information on.  The info I want to

      capture always starts with the line IT1 and ends with the line PO4.  The

      problem that I am having is that some of the records contain tax information

      (TXI), or have an additional line for description (PIDFZZ). The only lines

      that show up in every record are IT1, PIDF08 and PO4. I’ve tried using both

      IT1 and the PO4 lines in the detail trap, but I end up with tax amounts in

      records where they should not be.

       

      How would be the best way to handle this? Also, is there a way to get the

      invoice amount, line TDS, to only show up on the last record in the file?

       

      Thanks

       

       

      THIS IS A SAMPLE RECORD (There are no blank lines in the real file)

       

      ISA0000010051463111277055515800372890699U045010000000050P>

      GSIN00552861177055515802003111906004X004010

      ST81040001

      BIG2003111855515055200310248105991***PR

      N1STPSA913425583267

      ITD013**0*30

       

      IT116EA22.5*VC9Z0042

      TXILS8.1

      PIDFZZ***Part Number  ASHOP

      PIDF08***ANDOVER SHOP LBR (EA-15 MINS) ASHOP

      PO4*1

       

      IT131EA16.6*VC9Z0042

      PIDF08***ASSY BATTERY COVER WO UL     B079120003RP

      PO4*1

       

      IT12100EA869.53*VC060353         

      PIDFZZ***Part Number  B079120005RP

      PIDF08***RAHF,RECOMB,US,801-1240IU   

      PO41977*EA                           

      REFCTPABE7DAA                        

      REFVSAA                              

      DTM01120031117                       

       

      IT122EA2.5*VC9Z0042

      TXILS0.3

      PIDFZZ***Part Number  801040

      PIDF08***BATTERY, 9V DC                801040

      PO4*1

       

      TDS*87268.00

      TXILS8.40

      CTT*7

      SE6340001

       

       

      THIS IS WHAT I WOULD LIKE THE TABLE TO LOOK LIKE.

       

      INVDATE  INVNO    PONO    LINE QTY_INV PRICE  NET_AMT   TAX INVOICEAMT PRODUCTDESC  

      11/18/03 55515055 8105991 1       6.00  22.50    135.00 8.10           ANDOVER SHOP L

      11/18/03 55515055 8105991 3       1.00  16.60     16.60                ASSY BATTERY C

      11/18/03 55515055 8105991 2     100.00 869.53 86,953.00                RAHF,RECOMB,US

      11/18/03 55515055 8105991 2       2.00   2.50      5.00 0.30 87,268.00 BATTERY, 9V DC

        • Variable Line Record EDI
          Grant Perkins

          Alan,

           

          The following is a fairly detailed description as I don't know how much exposure you have had to Monarch. Forgive me if I am covering things that are obvious to you, hopefully the description (if I got it right) will be helpful to new users at a future point.

           

          See how you get on with this idea.

           

           

          This is an approach that should work but I have to say that this sort of file often produces anomalies (later, with other data samples) that make life more challenging.

           

          I'm not sure I fully understand what you want to to with the invoice total at the end of each record. Also the detail lines starting "PID" may be a special challenge, as would any other 'duplicated' lines. HOWEVER, if you can differentiate the lines CONSISTENTLY and RELIABLY by more that just the first 3 characters, life eases a little.

           

          Whatever happens this suggestion will need to use data splitting functions such as LSPLIT and RSPLIT to extract the info required. Calculated fields will be required to receive the extracted characters, format them and in one case do the 'qty x price' calculation.

           

          Here we go - it's not as complicated as it looks!

           

          Use the IT1 line to set the trap for you detail. Trap using IT1. Make the template selection area have as many ROWS as you will need to select.

           

          IT1 will always exist. A single field in ROW 1 should be defined. Make it as wide as the maximum width you are likely to get on the report. Ensure that the the field is defined as CHARACTER.

           

          Other rows may or may not exist. If we assume that you need 3 additional rows (PO4, A PID row and TXI(maybe)), then you need 4 lines in your seletion area. ON the second row create another field starting 3 chars from the left and for the full width of the possible output. Go to Field Definition>Options and make "Start Field on" be a PRECEEDING STRING of "PO4". On the next line do the same thing for the TXI possibility. On the next line the same thing for the PID option BUT, as there can be more than one line starting PID I am hoping that you can always differentiate using a few extra characters as you indicated. "PIDF08" for example. That would also mean starting the field further right on the line. (Note that the vertical order of the field definitions in the rows does not matter. You can defined them in any order.)

           

          If there are any other rows that may need to be included just make the selection area longer and add another field with its own preceeding string.

           

          You should be able to do the same sort of thing with anything you need from the 'header' record (Set up as an append).

           

          The Invoice total can be dealt with the same way but as a footer. (This will mean the values will appear on every detail line selected not just the last one but you can sort that out for display purposes later by using a calculated field to decide whether or not the line is the right one to show the value.

           

          OK, So assuming all that has worked you now have a table with some extended lines of text fields which, in some cases will be blank. Now you need to split those up into the data fields required. EDI being a structured record definition but with variable length means we should expect to find some consistency of relative data position. So it should be possible to create calculated fields for each of the required data fields by slicing the selected text fields and formatting the resulting output. LSPLIT using the "*" seems like a good option. RPLIT may be better for the TXI and PID description lines

           

          For example

           

           

              Val(RSplit(TXIFIELD,2,"*",1)).

           

          Where TXIFIELD is the field extracted from any line starting TXI, will give a numeric output in a new calculated field. The field definition would need to define how many decimal places are required.

           

           

          LSplit(,3,"*",2)

           

          will pick the Line Number, as a CHARACTER  field, from the TX1 line.

           

          To include the Invoice value from the footer area on the last row only you may need to do some comparative checking using something like ROWNO() or RECNO() in combination with some other data, then include the value in your final output (via a calculated field) when the rule is applicable. We can revisit that later if required.

           

          Most (All?) of the final output fields are likely to be calculated fields. Simply hide the others to prevent printing or export. If you are producing another report it is really your choice of whther to print from the Table or a Summary.

           

          I hope this helps. Let me know if I have missed something, the data throws up any more problems or it simply won't work (The PID rows may be trickier than they appear as far as "trapping" or defining is concerned.)

           

          Good luck,

           

          Grant

           

          Originally posted by Alan:

          I have an EDI file that I want to capture information on.  The info I want to

          capture always starts with the line IT1 and ends with the line PO4.  The

          problem that I am having is that some of the records contain tax information

          (TXI), or have an additional line for description (PIDFZZ). The only lines

          that show up in every record are IT1, PIDF08 and PO4. I’ve tried using both

          IT1 and the PO4 lines in the detail trap, but I end up with tax amounts in

          records where they should not be.

           

          How would be the best way to handle this? Also, is there a way to get the

          invoice amount, line TDS, to only show up on the last record in the file?

           

          Thanks

           

           

          THIS IS A SAMPLE RECORD (There are no blank lines in the real file)

           

          ISA0000010051463111277055515800372890699U045010000000050P>

          GSIN00552861177055515802003111906004X004010

          ST81040001

          BIG2003111855515055200310248105991***PR

          N1STPSA913425583267

          ITD013**0*30

           

          IT116EA22.5*VC9Z0042

          TXILS8.1

          PIDFZZ***Part Number  ASHOP

          PIDF08***ANDOVER SHOP LBR (EA-15 MINS) ASHOP

          PO4*1

           

          IT131EA16.6*VC9Z0042

          PIDF08***ASSY BATTERY COVER WO UL     B079120003RP

          PO4*1

           

          IT12100EA869.53*VC060353         

          PIDFZZ***Part Number  B079120005RP

          PIDF08***RAHF,RECOMB,US,801-1240IU   

          PO41977*EA                           

          REFCTPABE7DAA                        

          REFVSAA                              

          DTM01120031117                       

           

          IT122EA2.5*VC9Z0042

          TXILS0.3

          PIDFZZ***Part Number  801040

          PIDF08***BATTERY, 9V DC                801040

          PO4*1

           

          TDS*87268.00

          TXILS8.40

          CTT*7

          SE6340001

           

           

          THIS IS WHAT I WOULD LIKE THE TABLE TO LOOK LIKE.

           

          INVDATE  INVNO    PONO    LINE QTY_INV PRICE  NET_AMT   TAX INVOICEAMT PRODUCTDESC  

          11/18/03 55515055 8105991 1       6.00  22.50    135.00 8.10           ANDOVER SHOP L

          11/18/03 55515055 8105991 3       1.00  16.60     16.60                ASSY BATTERY C

          11/18/03 55515055 8105991 2     100.00 869.53 86,953.00                RAHF,RECOMB,US

          11/18/03 55515055 8105991 2       2.00   2.50      5.00 0.30 87,268.00 BATTERY, 9V DC /b[/quote]

          • Variable Line Record EDI
            Alan _

            Grant,Thanks for the info. The one problem I had with your suggestion, and

            I'm sure I'm missing something, is that I lost the 3rd record.  Because my

            template covers 5 rows and the 2nd record is only 3 rows, the 3rd record gets

            dropped.   How can I avoid that?

            I am using LSplit and Calculated fields to get the data I want.  That is the

            easy part.  I have been using Monarch since it's beginning, I have just been

            lucky enough not to have run into this problem before.

            • Variable Line Record EDI
              Grant Perkins

              Alan,

               

              Ah,yes!   [img]redface.gif[/img] 

               

              If you need 5 lines (or even 4 in this case     ) there will be a problem I'm afraid. It didn't register with me as I forgot your comment about the real file not having the blank lines and I was only using 4 rows to capture the 'fields'. (The reason for my caution at the top of the response!)

               

              Anyway, now I know you are along term user we could consider plan B and possibly even C.

               

              An option would be to modify the file (MSRP? or another favourite) to insert an appropriate number of Blank lines before each IT1 line. So replace IT1 with <ctrl-lf><ctrl_lf>IT1 would add a couple of blank lines and the reformat might well be enough.

               

              Next Idea - I have not yet tried this and don't have time right now (maybe later) but you could consider using IT1 lines to create a single multi-line field for each record and then slice and dice it into the specific lines/parts of lines required after that.  INSTR and SUBSTR to get the start and end positions and extract the original lines from the multi-line. If you have V7 there are other functions which may come into play.

               

              If you are comfortable with the functions that underpin the slicing and dicing I will leave it to you. If not I can have a play over the weekend and offer a 'success or failure' prognosis on Monday!

               

              Which version of Monarch can I use for that? I may need to be compatible with what you have available.

               

              Best regards,

               

              Grant

               

               

              Originally posted by Alan:

              Grant,Thanks for the info. The one problem I had with your suggestion, and

              I'm sure I'm missing something, is that I lost the 3rd record.  Because my

              template covers 5 rows and the 2nd record is only 3 rows, the 3rd record gets

              dropped.   How can I avoid that?

              I am using LSplit and Calculated fields to get the data I want.  That is the

              easy part.  I have been using Monarch since it's beginning, I have just been

              lucky enough not to have run into this problem before. /b[/quote]

              • Variable Line Record EDI
                Grant Perkins

                Alan,

                 

                Just had another thought. Based on some sort of consistency on the 3 lines that always appear and the TXI line (what is your 5th line?)

                 

                If you can set the PO4 to a detail line. The PID line of your choice to an append (always occurs above the PO4 line?

                 

                Then the IT1 line to a 2 line append with the first part being IT1 which always exists and the second the TXI line which may or may not exist as the line immediately below the IT1 line, so use the original preceeding string idea, does that get the required result?

                 

                Grant

                • Variable Line Record EDI
                  Alan _

                  Grant, your last response worked. I was heading in that direction only using a calculated field for the second line.  Your solution is much easier and cleaner.  I was trying to avoid the MSRP solution. Our IS dept uses Data Junction and that would have been the route I would have taken. I was just looking for something quick and dirty to do this.  By the way, I am using V7. I like it a lot.

                   

                  Thanks