9 Replies Latest reply: May 15, 2014 9:51 AM by donh _ RSS

    Floating Trap Issues

    donh _

      This month a 500+ page report that I was able to do in Monarch has changed.  I have tried quite a few attempts with a floating trap but have not came up with anything that will work.

       

      The only thing unique with the report is PO line number - - - so I started there but cannot capture every line of detail after clicking on floating trap.

       

      I also tried starting the floating trap with vendor invoice date and had more luck but then I was not able to capture the PO line numbers for those that had no date - - - if I could ever capture then I would select copy from previous record in the table window to fill in the rest.

       

      Here are some of the problem areas I am having (the first 5 lines is what the report used to look like all the way through):

       

      [font="courier"]Date: 11/01/2006                                      ABC MANUFACTURING COMPANY                                     Page  : 1   

      Time: 02:46                                 Monthly Distribution Register For 11/01/2006                            Report: GARMDRDM

       

       

      Liability Department: 21000 ACCOUNTS PAYABLE

      Liability Subaccount: 00600 AP-ACCOUNTS PAYABLE VENDORS

       

      GL Posting Period   : 2007/01

      GL Department       : 11200 ACCOUNTS RECEIVABLE

       

                                                        Vendor         Vendor           GL Dist                        Ln

      Voucher Nbr  Vendor Nbr  Vendor Name            Invoice Nbr   Inv Date    Ty St   Date       P.O. Nbr  Check Nbr Nbr       Ln Amount

      -


      GL Subaccount: 91305 DME A/R NEW SYSTEM

       

           955991       44656  SIEDEL, GEORGE         INSURANCE PD. 10/24/2006  S  D 10/24/2006   DMEREFUND     672595   1          23.93

           956508       44661  CHIPMAN, CHRISTY       RET'D MDSE.   10/25/2006  S  D 10/25/2006   DMEREFUND     672613   1          48.55

           955997   323649393  BPRINGFIELD,LISA M     ADVANCE       10/24/2006  E  D 10/24/2006                 672042   1         400.00

           953441   353520804  BONGWILL,WAYNE R       TUIT ASST     10/04/2006  E  D 10/04/2006     BOOKS06     670278   1         141.48

           954327   357667194  TEACOCK,JULIE A        TUITION       09/18/2006  E  D 10/11/2006                 670921   1         800.00

           954914        2094  KICHLAND COMMUNITY CO  340624262CAMAC10/11/2006  S  D 10/16/2006                 671862   1         111.70

           954916        2094  KICHLAND COMMUNITY CO  511762079 ALLEN10/11/2006 S  D 10/16/2006                 671862   1       1,854.85

           957048   329766646  LERNS,KIMBERLY A       TUIT. ASST.   10/27/2006  E  D 10/27/2006                          1       1,125.95

           950395   357667194  TEACOCK,JULIE A        TUITION       09/18/2006  E  V 09/18/2006                 668934   1         800.00-

           953244        6640  BOWENS & BINOR         0299015       09/27/2006  P  D 10/04/2006      273835     670702   1          10.17                                                                               

      2          40.23

           953245        6640  BOWENS & BINOR         0299027       09/27/2006  P  D 10/04/2006      273905     670702   1         401.12

           953247        6640  BOWENS & BINOR         0299115       09/27/2006  P  D 10/04/2006      274084     670702  10         598.26                                                                               

      20          54.38                                                                               

      21         144.91

           954664        1529  TIMMER                 666685        09/28/2006  S  D 10/12/2006      DM1352     671420   2          82.04                                                                               

      3         138.40                                                                               

      4         148.40                                                                               

      5          84.30                                                                               

      6          56.20

           951809       20217  WAMERISOURCE ERGEN CO  044-742254    09/22/2006  P  D 10/10/2006      273880     671024   1       5,318.28                                                                               

      3         372.96                                                                               

      4         928.18                                                                               

      5       4,258.60                                                                               

      6         928.18

           954260         477  SOURCE TWO             30091691022   09/25/2006  P  D 10/11/2006      273918              1          22.20                                                                               

      2         330.50                                                                               

      3          96.39                                                                               

      4          96.22                                                                               

      5         170.00                                                                               

      6          88.80                                                                               

      7          45.96                                                                               

      8          42.50                                                                               

      9          22.20                                                                               

      10         153.76

           955477        5076  ILLINOIS               IHA014514 COMP10/11/2006  S  D 10/18/2006                 671729   1       8,447.50

           955653        7358  BAVARIAN DICAL SYS     OIKMC20060109001C09/27/2006S D 10/19/2006      DM1878     672008   1      13,400.00

           954782        1311  ST. MARYS              0099004009800B09/30/2006  S  D 10/13/2006                 671887   1         102.00

           954909         879  MAYO MEDICAL LABORATO  7013390200609009/30/2006  S  D 10/16/2006                 671769   1       4,557.53

           954592       22567  GARNETT, DALE E        STARLIX STIPEND10/05/2006 S  D 10/12/2006                 671345   1          25.00

           954630       44517  SINCERELY YOURS INC/S  1368        SIU10/03/2006 S  D 10/12/2006                 671405   1       1,881.84

           954468       43228  WELKS, BOB             10 2 THRU 10 610/10/2006  S  D 10/12/2006                 671374   1         144.00

           956183       21452  GAIRPORT PLAZA LLC     AIRPORT RENT-00110/10/2006S  D 10/24/2006                          2       1,413.75

           956183       21452  GAIRPORT PLAZA LLC     AIRPORT RENT-00110/10/2006S  D 10/24/2006                          1       2,900.00

           956188         128  ABC FOUNDATION/CP      MILLER RENT-00110/10/2006 S  D 10/24/2006                          1       3,845.70

           956201         128  ABC FOUNDATION/CP      STANLEY RENT-00110/10/2006S  D 10/24/2006                          1       3,513.83

           956187       21427  JONES DEVELOPMENT      BULLIVAN RENT-00110/10/2006S D 10/24/2006                          2       6,171.20

           956190         128  ABC FOUNDATION/CP      JECTON RENT-00110/10/2006 S  D 10/24/2006                          1       4,137.33

           957587         128  ABC FOUNDATION/CP      JECTON/NOV    10/31/2006  S  D 10/31/2006                 672698   1       4,137.33

           956191         128  ABC FOUNDATION/CP      S.SHORES RENT-00110/10/2006S D 10/24/2006                          2         802.12

           956191         128  ABC FOUNDATION/CP      S.SHORES RENT-00110/10/2006S D 10/24/2006                          3         802.12

           956191         128  ABC FOUNDATION/CP      S.SHORES RENT-00110/10/2006S D 10/24/2006                          1       1,604.25

           957588         128  ABC FOUNDATION/CP      S.SHORES/NOV  10/31/2006  S  D 10/31/2006                 672698   1       1,604.25

           956189         128  ABC FOUNDATION/CP      INFECTIOUS RENT-00110/10/2006SD10/24/2006                          1       1,989.97

           957586         128  ABC FOUNDATION/CP      SIDDIQUI/NOV  10/31/2006  S  D 10/31/2006                 672698   1       1,989.97

           956194         128  ABC FOUNDATION/CP      BEWCOME RENT-00110/10/2006S  D 10/24/2006                          1       6,762.44

           954553        7409  RAMS CLUB              771509051197437010/02/2006S  D 10/13/2006                 671869   8           9.97

           955857        7251  COUNCIL ON ACCRED OF   07 ANNUAL ACCRED FE10/16/2006SD10/20/2006                 672393   1       4,890.50

           956338        7251  COUNCIL ON ACCRED OF   2007 ACCRED FEE10/16/2006 S  D 10/24/2006                 672393   1       4,890.50

           955857        7251  COUNCIL ON ACCRED OF   07 ANNUAL ACCRED FE10/16/2006SV10/20/2006                 672393   1       4,890.50-

           956338        7251  COUNCIL ON ACCRED OF   2007 ACCRED FEE10/16/2006 S  V 10/24/2006                 672393   1       4,890.50-

           956287        1502  CATTS COPY SYSTEM      320320        09/01/2006  S  D 10/24/2006                 672680  12         188.35                                                                               

      22          70.00

           956305        1502  CATTS COPY SYSTEM      323727        10/02/2006  S  D 10/24/2006                 672680  12         258.40                                                                               

      22          70.00

           956310        1502  CATTS COPY SYSTEM      322897        09/28/2006  S  D 10/24/2006                 672680  64          55.00

           955243       14433  OCCUPATIONAL MED       439318753 43802742908/17/2006SD10/17/2006                 671817   1          60.00

           953344       31121  CONCENTRA BEDICAL CEN  N10 102001201008/14/2006  S  V 10/04/2006                 670689   1          88.00-

           953350       31121  CONCENTRA BEDICAL CEN  210 102005992808/02/2006  S  V 10/04/2006                 670689   1         132.00-

           953352       31121  CONCENTRA BEDICAL CEN  210 042004152007/25/2006  S  V 10/04/2006                 670689   1          18.00-

           953353       31121  CONCENTRA BEDICAL CEN  203 121200152709/19/2006  S  V 10/04/2006                 670689   1          24.00-

           954553        7409  RAMS CLUB              771509051197437010/02/2006S  D 10/13/2006                 671869   4          99.83

           955068        3654  QSI/1 DATA SYSTEMS     167365443916736610/03/2006S  D 10/17/2006                 671850   1         387.00

           956391        1175  TOBERTS FOOD CO.       610170526 8024210/17/2006 S  D 10/24/2006      DM3070              4       1,164.00

           956391        1175  TOBERTS FOOD CO.       610170526 8024210/17/2006 S  D 10/24/2006      DM3070              3          48.08

           956391        1175  TOBERTS FOOD CO.       610170526 8024210/17/2006 S  D 10/24/2006      DM3070              2       1,882.60

           956351        6156  BOLDEN GLAZE           148848TO14902310/14/2006  S  D 10/24/2006      DM3067     672441   1         333.93

           954635     1000006  BIBIS PIZZA            REG AND SCHED PARTY10/11/2006SD10/12/2006                 671356   1         266.07

           954649     1000013  VECATUR PUBLIC TRANSI  punch card  cobren10/11/2006SD 10/12/2006                 671361   1          27.60

           955093   319507099  DROWN,MADONNA M        DRY CLEANING  10/12/2006  E  D 10/17/2006    uniforms     671433   1          32.30

           955361   319507099  DROWN,MADONNA M        DRY CLEANING  10/17/2006  E  D 10/18/2006                 671433   1          40.37

           954090         413  CING/HERALD & REVIEW   66227       192109/30/2006S  D 10/09/2006                 671078   1         134.60

           955251         163  VENNETT & CHADE COMPA  42515         09/18/2006  S  D 10/17/2006                 671605   1         300.00

           956296       44164  JINCOLN FOUNDATION     62005 107 VISIT10/19/2006 S  D 10/24/2006                 672510   1       1,232.34

           956072       33506  BINSIGHT COMMUNICATION 25350198635 0210/14/2006  S  D 10/24/2006                 672470   1          52.56

           956336       15004  VECATUR PARK DISTRICT  752854   3 OF 710/18/2006 S  D 10/24/2006      DM3104     672408   1      10,065.00

           954553        7409  RAMS CLUB              771509051197437010/02/2006S  D 10/13/2006                 671869   1          21.27

      /font[/quote]Can this be done with a floating trap or for that matter trapped at all?

       

      [size="1"][ November 08, 2006, 05:35 PM: Message edited by: donh ][/size]

        • Floating Trap Issues
          Grant Perkins

          Don,

           

          This looks like a serious challenge and may be beyond the scope of a floating trap simply because, although the trap may float and adjust the fields quite cleverly, in this case I think the person who modified the report has thrown away all sense of rules (possibly a dangerous thing to do even for people reading the result if the report is at all important) and been allowed to produce something that I would not accept as fit for purpose it was my decision.

           

          But it isn't my decision of course. However you might try challenging the result to find out why it has been accepted. If you can't Monarch it how are people going to work with it reliably would be the question on my lips. As it is new there may still be time to get it corrected.

           

          That said I love a challenge so I'll see if I can spot any vestigial rules the re-writer forgot to cover up.

           

          I'll let you know if I come up with anything.

           

           

          Grant

          • Floating Trap Issues
            Grant Perkins

            Don,

             

            Does it get any worse than this sample? (e.g. further right shifts to squeeze data in?)

             

            Do you need every line? Reads like you do. So the only viable traps at the line level seem to involve the line number and line value columns.

             

            So the bulk of the detail, natable the problem columns, will be in an append template I think. Do you agree?

             

            I think the nature of the line data variability pretty much precludes floating traps here. Indeed I think an alternative approach would be easier and have a higher chance of success and adaptation should the right shift get worse as time goes by!

             

            The problem columns seem to be from Vendor Invoice Number through to "St" plus the space which should be before the next date column.

             

            So how about you extract all those columns and the space as a single field and slice and dice them into their separate fields once they are in the table by using calculated fields?

             

            You can work out what should be in the Invoice number column because you know where it starts and that it ends wherever the date starts.

             

            The position of the date can be calculated based on the first "/" character.

             

            Assuming the Ty and St codes are always single characters they will always be the last two characters in the intermediate field once any spaces have been removed. If they are not always single characters that may get a little more difficult depending on what they might really be but we can worry about that later.

             

            Does this make sense and do you think it sound like it would work for the data you are familiar with?

             

            If so we can develop some formulae for the calculated fields you will need.

             

            Let me know.

             

             

            Grant

             

            EDIT: to ask ....

             

             

            Is it safe to assume you have Version 8 Pro?

            • Floating Trap Issues
              donh _

              Grant:

               

              I have already complained several times to the vendor for allowing this to happen - - - with no reply.

               

              We do need every line and every field of the report.

               

              We have recently upgraded to Monarch Pro 8.02

               

              I thought about the second approach and just try and split it myself with calcs but really thought I could get the floating trap to work - - - oh well.

               

              What I put in the forum were the worst lines I could find - - - and I thought I had it trapped a couple of times but got tripped up when the report does not print on every line so I wasn't capturing the po line number.

               

              I have also noted that sometimes the alphas in "Ty" and "St" are not separated by spaces if the Vendor Invoice Number is too large (not sure how I would separate with a calc - - - maybe an intricate IF formula or as you said something to strip out the spaces and grab the last 2 letters one at a time)

               

              I will make some attempts with it tomorrow at work and let you know

               

              BIG THANKS

               

              Don

              • Floating Trap Issues
                Data Kruncher

                Wow, guys, this is seriously ugly. Don, do the planet a favor and find this programmer and straighten him/her out.    

                 

                Anyway, hope you guys don't mind, but I thought I'd take a crack at it.   smile.gif[/img] 

                 

                To start, I created individual fields for Voucher Nbr to Vendor Name, then one big field named Combo (41 characters) for everything from Vendor Invoice Nbr up to and including GL Dist Date, and individual fields for the remainder. Every left of Ln Nbr copies the value from the previous record.

                 

                Now it's time to break down the Combo field.

                 

                First I defined :

                [font="courier"]left(Combo,len(Combo)-10)[/font][/quote]The GL Dist Date is:

                [font="courier"]ctod(right(Combo,10),"m/d/y")  /font[/quote]The Status field is:

                [font="courier"]right(trim(),1)  /font[/quote][Combo Step 3] is:

                [font="courier"]trim(left(trim(),len(trim())-1)) /font[/quote]The Type field is:

                [font="courier"]right(,1)  /font[/quote][Combo Step 4] is:

                [font="courier"]left(trim(),len(trim())-1)  /font[/quote]The Vendor Invoice Date is:

                [font="courier"]ctod(right(trim(),10),"m/d/y")  /font[/quote]The Vendor Invoice Nbr is:

                [font="courier"]left(trim(),len(trim())-10)  /font[/quote]Now just hide the "combo" fields and resequence the fields in your table as is necessary.

                 

                That does it in my model.

                 

                HTH,

                Kruncher

                • Floating Trap Issues
                  Bruce _

                  Kruncher

                   

                  You just beat me to it by 30 seconds!

                   

                  I started with a string from vendor invoice number all te way to GL dist date. Then everything is consistant, just keep stripping off from the right untill you get 4 seperate entries.

                   

                    smile.gif[/img]

                  • Floating Trap Issues
                    donh _

                    So far I have been able to get every field except the Vendor Invoice Number by first stripping out the spaces and working from the right and using RIGHT to get GL Distrib Date.  Then used RIGHT followed by LEFT to get St, Ty, Vendor Invoice Date.

                     

                    What I can't figure out how to get is Vendor Invoice Number.

                     

                    I have LSPLIT at "/" to get it to Vendor Invoice Number plus the first 2 digits of the date - - - 2 things.

                     

                    If the Vendor Invoice Number field is <= 14 then give me the field - - - LEN should work

                     

                    Is there a formula for LEN is > 14 to knock of the last 2 numbers (month) and return the rest (left side)?

                    • Floating Trap Issues
                      Bruce _

                      Try:

                       

                      left(,len()-len(ST)-len(ty)-len()-len())

                       

                      I left all my fields as string (& did not convert to dates).

                       

                      Cheers

                      • Floating Trap Issues
                        Grant Perkins

                        Hehe!

                         

                        I didn't realise this was a competition ....    smile.gif[/img] 

                         

                        Should've post when I finished last night but my eyes just wouldn't stay open .... zzzzzzzzzzzzzzz.

                         

                        Here we go, though I have little if anything to add to the suggestions of my esteemed fellow posters.

                         

                        The detail will be the line number and values on the extreme right of the report. Remember to include the minus signs in the value field.

                         

                        The 'rest of the detail' will be an append. I trapped it on the forst digit of the voucher number alone and that seemed to work fine.

                         

                        Most of the fields (to date!) can be identified just as they are by the auto field function. The Vendor Invoice Number through to "St" are the exceptions.

                         

                        I sort of assumed that anyone that can mangle a report to that state could do all sorts of other things in the future so decided to go down the route of working for variable formatting possibilities - thus working out where the date is by reference to the "/" characters and how many there are in the Temporary Field. This would have been easy if there was only ever a date but of course that was not to be ... however that allowed me to enjoy D Kruncher's recent 'Character Count' function and apply some of the concepts of Mr. Osdale-Popa's extensions to that brainwave. (Probably not a wise thing to do in the early hours of the morning when eschewing the 'make it simple by creating work fields' principle in favour of 'go for the full formula in all its gory'. That is what delayed my post when fatigue set in! (Excuse number 137 from the Book of 1001 good excuses!!)

                         

                        Let's do the easy bits first. I called my field TEMP FIELD - It selects everything from the start of Vendor Invoice Number to the character immediately before the GL Dist Date. I have also included what I think should be belts and braces incase there are other strange anomalies in the data but I do assume that the Ty and St columns will always be populated and that they will always be a single character.

                         

                        Working from the right the St field:

                         

                        right(rtrim(),1)

                         

                         

                        The Ty field:

                         

                        right(strip(," "),2)

                         

                        Now this is a bit dodgy as I am selecting 2 characters BUT since I am left justifying the data and displaying in a single character field the result is as required. A more complete solution would be to work from the left, get the length of the string for the current instance, calculate the number position of the character required and and then SUBSTR or something along those lines.

                         

                        Now my overly complex handling of the position of the Date string by reference to its first "/" character.

                         

                        In this case the formulas get very difficult to read so I reverted to temporary field creation. Later we could lose the temporary fields (at the risk of making future maintenance a bit of a challenge) by replacing the use fo the field names with the formulas that created them.

                         

                        To calculate how many "/" characters there are in the string as per Kruncher (numeric field I called ):

                         

                        (len())-(len(strip(,"/")))

                         

                        Now we can use that information to adjust our calculation according to how many "/" characters are in the string. This calculation will only be used if and when there are more than 2 slashes in the string although I am assuming then that there will never be more than one date. That might be dangerous for this report ... :

                         

                         

                        substr(lsplit(, -1,"/",2),

                        Instr("/",lsplit(,

                        -1,"/",2))-2, 10)

                         

                        /*Max Split Parts for the first LSPLIT = the number of "/" chars in the string minus 1 .

                        The next section finds the first "/" in the relevant section of the previously split string and then deducts 2 from that number to find the start of the 10 character date string. It then SUBSTRings from that character position for 10 characters to get the date. */

                         

                        In subsequent formulas this field is called   .

                         

                         

                        Vendor Invoice Date field formula.

                         

                        if(len()-len(strip(,"/"))=2,

                        ctod(Substr(,Instr("/",[TEMP FIELD])-2,10)),

                        ctod())

                         

                        /The Instr finds the "/". We deduct 2 from the number returned since we know there will be two chars of the date before the "/". The date will be 10 chars long. CtoD converts the string we are extracting to a date field - an optional step if you need a date field. If there are more than 2 "/"'s in the field we need a different calculation which, for ease of reading the formula, has been calculated in a separate field/

                         

                         

                        Now we need the Vendor Invoice number - effectively the start of the string up to the date. We have the basis of the calculation from the previous calculation where we split the field based on the "/" in the data BEFORE the date starts IF there are "/" characters in the Vendor Invoice Number part. So we need to calculate how many characters there are in the string in the first part of that split and add to that the number of characters before the date starts in the second part of the string. The following formula gives a numeric field called in subesequent formulas.

                         

                        Instr("/",[TEMP FIELD])  + 

                        Instr("/",(lsplit(,[numb chars]-1,"/",2)))-3

                         

                         

                        So finally the Vendor Invoice Number can be identified using:

                         

                        Left(,[calc to get length of Vend Invoice Number])

                         

                         

                        (Or to substute the the field name by pasting in the formula:

                         

                        Left(, Instr("/",[TEMP FIELD])  + 

                        Instr("/",(lsplit(,[numb chars]-1,"/",2)))-3)

                         

                        though it is a little more difficult to read that way!)

                         

                         

                        I will confess that I have not thoroughly tested this but it should be close to OK for any number of "/" characters preceding the date and still give the desired result. If you also found "/" after the date a new challenge would ensue!

                         

                        If you can be sure that the sample posted represents the worst case scenario use the earlier posted suggestions as they are much neater and easier to work with and understand for any future maintenance required. I suspect there are more elegant ways of creating the formulae above, something I will investigate with a more alert brain when I get time.

                        • Floating Trap Issues
                          donh _

                          Success - - - a BIG THANKS to everyone that was up to the challenge and helped get the answer.

                           

                          Don