11 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Multi Line Report Trap

    Stephen Reid

      Hi Guys,

       

      I have a problem with the report below and even though I have pulled the required information out I am not entirely convinced that my solution is the most effective.

       

      The report below shows billing documents in GBP and if required in Currency on the line below.

       

      /size[list][]I couldn't append the currency because it is below the relevant GBP line.[/size][/list][/size][list][]I couldn't append the GBP line because not every line has a currency line.[/size][/list][/size][list][*]I couldn't use multiple line fields because the lines are not in the same format.[/size][/list]

      In the end I trapped the GBP line as detail and the currency line as a footer and then used caclulated fields.  The calculated fields checked the currency total against the GBP total and if they matched (within 0.02p) I showed the currency against the record.

       

      The problem I can see is that if a non currency document had the same value as a currency document just below it then the currency detail would show against both documents.

       

      Is there an easier solution than this and is it just me complicating things?

       

      Thanks in advance for any help.

       

      [font="courier"]Typ    Document Number   Doc Date  Customer    Loc   Total Charges     Discount            VAT            Tax       Document Total    

      --- -


      -


      -


      -


      -


        -


        -


        -


        -


            

      C               6506518 08/04/03 A1013                     541.28-                          94.72-                         636.00-   

      C               6506519 08/04/03 I0838                    5738.81-                                                        5738.81-   

                        Curr: USD Rate:     1.5751000         9039.20-                                                        9039.20       

      C               6506530 10/04/03 V3417                     179.33-                          31.38-                         210.71-   

      C               6506531 10/04/03 AB185                     419.35-                                                         419.35-   

                        Curr: USZ Rate:     1.5500000          650.00-                                                         650.00       

      C               6506532 10/04/03 B0313                     430.35-                                                         430.35-   

                        Curr: EUR Rate:     1.4614000          628.92-                                                         628.92       

      C               6506533 10/04/03 E2956                     240.00-                          42.00-                         282.00-   

      C               6506534 10/04/03 E2956                     240.00-                          42.00-                         282.00-   

      C               6506535 11/04/03 W3290                    1430.00-                         250.25-                        1680.25-   

      C               6506536 14/04/03 W3501                     133.06-                          23.29-                         156.35-   

      C               6506537 15/04/03 C4365                      24.45-                           4.28-                          28.73-   

      C               6506538 17/04/03 A1013                      66.19-                          11.58-                          77.77-   

      C               6506539 17/04/03 A1013                      28.15-                           4.93-                          33.08-   

      C               6506540 23/04/03 A1013                     155.66-                          27.24-                         182.90-   

      C               6506541 23/04/03 A1013                      36.00-                           6.30-                          42.30-   

      C               6506542 23/04/03 H0086                     672.00-                                                         672.00-   

      C               6506543 23/04/03 A1013                      97.89-                          17.13-                         115.02-   

      C               6506544 23/04/03 A1013                      97.89-                          17.13-                         115.02-   

      C               6506545 23/04/03 L0341                      52.45-                                                          52.45-   

                        Curr: EUR Rate:     1.4590000           76.53-                                                          76.53       

      C               6506546 24/04/03 S2269                     162.00-                          28.35-                         190.35-   

      C               6506547 25/04/03 I0838                     838.75-                                                         838.75-   

                        Curr: USD Rate:     1.5611000         1309.38-                                                        1309.38        /font[/quote]

        • Multi Line Report Trap
          Steve Caiels

          Hi Stephen,

           

          The good ol’ preceding string will work here!   2nd time today!!  It helps us out a great deal on tech support.  The most common technique for using it is to create an append template (even though the data is below) using the same sample and trap that you used for the detail. Highlight the field in the position it would be in if you had selected the “correct” sample line.  In the advanced field options, set the preceding string to whatever text appears before the target filed on the line that actually contains the info.  In this case “CURR”

           

          Regards

          Steve

          • Multi Line Report Trap
            Grant Perkins

            Hi Stephen,

             

            There was a very similar requirement (must be something to do with the way people in UK write reports!) a couple of weeks ago.

             

            Have a look  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic&f=1&t=000235"]here[/url] for the responses on that one in case they are relevant in this context.

             

            Grant

             

            [size="1"][ May 18, 2006, 04:51 PM: Message edited by: Todd Niemi ][/size]

            • Multi Line Report Trap
              Stephen Reid

              Hi Guys

               

              Thanks for the replies.

               

              Grant, I had looked at this post already but I felt that this wouldn't help me as I have no way of differentiating between the lines that have currency and the lines that don't (where Andy had Inv or Cash).  The last solution seemed interesting though as you said a little more complicated.  What is the MSRP utility and can this automtically scan a directory for the print file and then, given the rules, output a new print file?

               

              Steve, I had looked at this solution but I found that I could only pull out the Curr and Rate fields as all of the other value fields where preceeded by spaces (or am I missing something?).  Saying that, as an afterthought I could capture the rate and currency using your solution and then use calculated fields for the currency values.

              • Multi Line Report Trap
                Grant Perkins

                Stephen,

                You're right. Very similar problem format for the report but different solution required! Nothing in life is simple.

                 

                Steve's solution works in my V5.02 system so I assume it will work in 6/7.  The other fields should also work of you specify Curr or rate - I used rate. As long as the text is on the line somewhere it seems to be OK.

                 

                MSRP - easiest suggestion is to search in Monarch Help where you will see a brief description and be pointed you to a readme.txt file for further info. Datawatch suggest usage in conjunction with Tech Support but I have used the utility successfully on occasion without hand holding. Very useful if no other option available. Knowledge of ASCii codes a benefit - but there is a table for those available as well.

                 

                Have fun.

                 

                Grant

                 

                Originally posted by Stephen Reid:

                Hi Guys

                 

                Thanks for the replies.

                 

                Grant, I had looked at this post already but I felt that this wouldn't help me as I have no way of differentiating between the lines that have currency and the lines that don't (where Andy had Inv or Cash).  The last solution seemed interesting though as you said a little more complicated.  What is the MSRP utility and can this automtically scan a directory for the print file and then, given the rules, output a new print file?

                 

                Steve, I had looked at this solution but I found that I could only pull out the Curr and Rate fields as all of the other value fields where preceeded by spaces (or am I missing something?).  Saying that, as an afterthought I could capture the rate and currency using your solution and then use calculated fields for the currency values. /b[/quote]

                • Multi Line Report Trap
                  Steve Caiels

                  Hi Stephen,

                   

                  The preceding string doesn’t have to be immediately preceding and can have other text in-between, so you could use the same preceding string of CURR: for all 4 fields if you wanted to.

                   

                  Using lots of spaces as a preceding string is another posibility, but you don't need it here.  If your report was like this

                   

                  [font="courier"] 

                  Typ    Document Number   Doc Date  Customer    Loc   Total Charges     Discount            VAT            Tax       Document Total    

                  --- -


                  -


                  -


                  -


                  -


                    -


                    -


                    -


                    -


                        

                  C               6506518 08/04/03 A1013                     541.28-                          94.72-                         636.00-   

                  C               6506519 08/04/03 I0838                    5738.81-                                                        5738.81-   

                                                           1.5751000         9039.20-                                                        9039.20       

                  C               6506530 10/04/03 V3417                     179.33-                          31.38-                         210.71-   

                  C               6506531 10/04/03 AB185                     419.35-                                                         419.35-   

                                                           1.5500000          650.00-                                                         650.00       

                  C               6506532 10/04/03 B0313                     430.35-                                                         430.35-   

                                                           1.4614000          628.92-                                                         628.92        /font[/quote]You could use a preceding string of  20 or 30 spaces to do the same thing, but you'd have to be careful on the last line before page breaks etc.

                   

                  Cheers,

                  Steve

                  • Multi Line Report Trap
                    Grant Perkins

                    Steve,

                     

                    I've never managed to get multiple spaces to work as a preceding string. Any special techniques required?

                     

                    Grant

                     

                     

                    Originally posted by Steve Caiels:

                    Hi Stephen,

                     

                    The preceding string doesn’t have to be immediately preceding and can have other text in-between, so you could use the same preceding string of CURR: for all 4 fields if you wanted to.

                     

                    Using lots of spaces as a preceding string is another posibility, but you don't need it here.  If your report was like this

                     

                    [font="courier"] 

                    Typ    Document Number   Doc Date  Customer    Loc   Total Charges     Discount            VAT            Tax       Document Total    

                    --- -


                    -


                    -


                    -


                    -


                      -


                      -


                      -


                      -


                          

                    C               6506518 08/04/03 A1013                     541.28-                          94.72-                         636.00-   

                    C               6506519 08/04/03 I0838                    5738.81-                                                        5738.81-   

                                                             1.5751000         9039.20-                                                        9039.20       

                    C               6506530 10/04/03 V3417                     179.33-                          31.38-                         210.71-   

                    C               6506531 10/04/03 AB185                     419.35-                                                         419.35-   

                                                             1.5500000          650.00-                                                         650.00       

                    C               6506532 10/04/03 B0313                     430.35-                                                         430.35-   

                                                             1.4614000          628.92-                                                         628.92        /font[/quote]You could use a preceding string of  20 or 30 spaces to do the same thing, but you'd have to be careful on the last line before page breaks etc.

                     

                    Cheers,

                    Steve /b[/quote]

                    • Multi Line Report Trap
                      Steve Caiels

                      Hi Grant,

                      There needs to be more spaces on the target line than the sample (or any lines in between). 

                      So this is OK with a preceeding string of between 3 and 5 spaces

                      [font="courier"]  data1     data2

                        data1     data2

                        data1     data2

                           data3

                        data1     data2

                        data1     data2

                           data3

                       

                      ^^--- 2 spaces here

                        /font[/quote]Cheers

                      Steve

                       

                      [size="1"][ July 03, 2003, 09:38 AM: Message edited by: Steve Caiels ][/size]

                      • Multi Line Report Trap
                        Stephen Reid

                        Of course! Just me being stupid.

                         

                        Thanks again guys, Steve's solution works a treat.  smile.gif[/img]

                        • Multi Line Report Trap
                          Grant Perkins

                          Steve,

                           

                          You're right. (I think I missed out the word 'reliably' from my post above - as in "I've never managed to get multiple spaces to work reliably as a preceding string. ")

                           

                          Odd 'cos I went back to the problem I referenced above (Andy Rowland post) and tried the blanks again before I posted the query. Got the field OK but failed to populate it.

                           

                          Hmm. Must have missed something somewhere since it now works OK.

                           

                          It might be worth cross referencing Andy's problem to this one to complete the loop for future searchers.

                           

                          Thanks for the help.

                           

                          Grant

                           

                           

                            Originally posted by Steve Caiels:

                          Hi Grant,

                          There needs to be more spaces on the target line than the sample (or any lines in between). 

                          So this is OK with a preceeding string of between 3 and 5 spaces

                          [font="courier"]  data1     data2

                            data1     data2

                            data1     data2

                               data3

                            data1     data2

                            data1     data2

                               data3

                            /font[/quote]Cheers

                          Steve /b[/quote]

                           

                          [size="1"][ July 03, 2003, 09:45 AM: Message edited by: Grant Perkins ][/size]

                          • Multi Line Report Trap
                            Stephen Reid

                            Grant,

                             

                            I don't know if this is your problem but it is something that caught me out slightly while I was looking at something similar to this a couple of weeks ago.

                             

                            I used the append with preceeding field to capture a 5 character string on the second line.  It seemed to work fine and capture in the report window but in the table view it was just full of nulls.  It turned out to be that my field was set to numeric and therefore wouldn't display the string.

                             

                            Simple I know, but it usually is something simple   smile.gif[/img] .

                             

                            Good suggestion about referencing the two posts.  I'll just go and do that know.

                            • Multi Line Report Trap
                              Grant Perkins

                              Stephen,

                               

                              Good suggestion. I did check for that (maybe too quickly to be completely happy ...) but it looked OK.

                               

                              The model had a lot of experimental templates and I wonder if something in the definition of one of them was creating an odd logic which partly 'blocked' the new template. But in reality I probably just got something wrong and did not notice it and now, having re-modelled a little, my error has been corrected!

                               

                              As you say, it's often the simple things. And it's always worth getting someone else to check the logic if possible - saves hours sometimes.    

                               

                              Originally posted by Stephen Reid:

                              Grant,

                               

                              I don't know if this is your problem but it is something that caught me out slightly while I was looking at something similar to this a couple of weeks ago.

                               

                              I used the append with preceeding field to capture a 5 character string on the second line.  It seemed to work fine and capture in the report window but in the table view it was just full of nulls.  It turned out to be that my field was set to numeric and therefore wouldn't display the string.

                               

                              Simple I know, but it usually is something simple    smile.gif[/img]  .

                               

                              Good suggestion about referencing the two posts.  I'll just go and do that know. /b[/quote]