15 Replies Latest reply: May 15, 2014 9:58 AM by Nick Osdale-Popa RSS

    Bug in Monarch v6.01?

    Tom Whiteside

      I've found what appears to be a bug in v6.01 - - one that is new and was not in v6.00.  I will give an example below, but the gist of it is that when trapping a multiple line part number (as character format) that wraps to two lines, v6.01 will not cleanly join the two line fragments.  The effect is that the model's reunited fragments appear to have a space in the part number field where the line break took place.  Attempts to LSplit the field into two parts at the space, then RTrim the field fragments, then concatenate them have no effect on the space.  In fact, an LSplit to break on the space and return the first fragment does not work.

       

      I've tested this under controlled conditions.  First, the situation does not arise on either Win 95, Win 2000, or Win XP, as long as v6.00 is being used.  Secondly, the problem appears on all three Windows platforms as soon as the v6.01 upgrade is installed.  One of the reasons I believe that v6.01 is not recognizing a space is that exporting the result to Excel, and then using Substitute to replace " " with "" nicely removes the offending space.  Using the Clean function to remove a control character does nothing.

       

      Here is an example - - my text report is from Oracle Financials 11i - -

       

      TC55VD1618FF150

      YJ2

       

      Trapping this multiple line part number produces as a single field - -

       

      TC55VD1618FF150 YJ2

       

      Using a new calculated field of - -

       

      RTrim(LSplit(,2," ",1))+RTrim(LSplit(,2," ",2))

       

      would produce

       

      TC55VD1618FF150YJ2

       

      under v6.00 with Win 95, Win 2000, or Win XP, but will now produce

       

      TC55VD1618FF150 YJ2

       

      under v6.01 with Win 95, Win 2000, or Win XP.

       

      Any suggestions?  Setting "Options - Input - Ignore form feed characters," and "Options - Input - Ignore unused print control characters" has no effect either way.  Also, my model is designed for usage by about twenty other people who know just enough to run a report and apply a model.  Using the PREP Utility or any report massaging will not solve the problem.

       

      Sorry to be long-winded, but this appears to be a potentially serious problem.  I will be delighted if anyone can point out any obvious mistakes I may be making.

       

      Tom Whiteside

      whitesidetom@yahoo.com[/email]

        • Bug in Monarch v6.01?
          Tom Whiteside

          Also, if it makes a difference, I am using Monarch Pro for both v6.00 and v6.01.

          • Bug in Monarch v6.01?
            Nick Osdale-Popa

            I do believe he is right. I've recreated this and found that the "space" is actually comprised of CrLf (Chr(13) and Chr(10)) so the formula works out as such:

             

            RTrim(LSplit(,2,Chr(13),1))+RTrim(LSplit(,2,chr(10),2))

             

            Hope that helps as a work-around!

            • Bug in Monarch v6.01?
              Tom Whiteside

              Thanks, Nick!!!

               

              Just revised my formula, exactly as you suggested.  The part number is once again a united whole.

               

              Mike?  Gareth?  Dee?  Be sure to flag this as something to fix with an upcoming Monarch 6.02 patch.  Part numbers that have wrapped to more than one line are some of the most common situations that need multiple line character trapping.

               

              Nick, not only do I owe you several times over, but I need to settle my tab with you.  Send me your Swiss Bank account number?

               

              Tom Whiteside

              whitesidetom@yahoo.com[/email]

              • Bug in Monarch v6.01?
                Nick Osdale-Popa

                Aw, shucks 'twernt nuttin.

                 

                Just a little investigating with the ASC() function told me all I needed to know.

                :cool: 

                Swiss Bank #: pi

                • Bug in Monarch v6.01?
                  Grant Perkins

                  Tom,

                   

                  It sort of occurs to be that this might be a 'fix' for an earlier ommission.

                   

                  I have never had to deal with a report presenting a part number or other code spread across more than one line. I do sympathise there. However I have used the selections on many occasions for dealing with multi line blocks of text which may well not have <CrLf> embedded and having to insert a space each time was a real pain ...

                   

                  One mans' meat is another's poison as they say. Perhaps there could be a compromise whereby a default could be set (insert <CRLF> or not) but with a local override as part of the field definition. That might be the answer for everyone.

                   

                  But hey,  if you think I am way off the mark that's OK as well.      

                   

                  Grant

                   

                    Originally posted by Tom Whiteside:

                  Thanks, Nick!!!

                   

                  Just revised my formula, exactly as you suggested.  The part number is once again a united whole.

                   

                  Mike?  Gareth?  Dee?  Be sure to flag this as something to fix with an upcoming Monarch 6.02 patch.  Part numbers that have wrapped to more than one line are some of the most common situations that need multiple line character trapping.

                   

                  Nick, not only do I owe you several times over, but I need to settle my tab with you.  Send me your Swiss Bank account number?

                   

                  Tom Whiteside

                  whitesidetom@yahoo.com[/email][/b][/quote]

                   

                  [size="1"][ November 14, 2002, 07:23 PM: Message edited by: Grant Perkins ][/size]

                  • Bug in Monarch v6.01?
                    Gareth Horton

                    Tom et al,

                     

                    This behavior did change with 6 and it has caused people some problems.

                     

                    As mentioned later, however, some people have taken advantage of the new behavior, so we will have to work out what the best approach for dealing with these mult-line text blocks is.

                     

                    Gareth

                     

                    Originally posted by Tom Whiteside:

                    Thanks, Nick!!!

                     

                    Just revised my formula, exactly as you suggested.  The part number is once again a united whole.

                     

                    Mike?  Gareth?  Dee?  Be sure to flag this as something to fix with an upcoming Monarch 6.02 patch.  Part numbers that have wrapped to more than one line are some of the most common situations that need multiple line character trapping.

                     

                    Nick, not only do I owe you several times over, but I need to settle my tab with you.  Send me your Swiss Bank account number?

                     

                    Tom Whiteside

                    whitesidetom@yahoo.com[/email][/b][/quote]

                    • Bug in Monarch v6.01?
                      Tom Whiteside

                      Gareth,

                       

                      Fair enough, but I'm completely missing what you mean by "some people taking advantage of the new behavior."  The technical spec sheet for the v6.01 upgrade does list revisions to multiple-line trapping as one of the new features.

                       

                      Was the CrLf addition meant to aid in trapping multi-line text, by preventing run-together of separate words in text lines?  If not, then I'm completely in the dark as to what you are talking about - - "taking advantage of the new behavior?"

                       

                      Just curious.

                       

                      Tom Whiteside

                      • Bug in Monarch v6.01?
                        Nick Osdale-Popa

                        Originally posted by Tom Whiteside:

                        - - "taking advantage of the new behavior?"

                        /quoteI'm at home right now so I can't test this.  Would leaving in the CrLf in the field cause it to wrap the text in the column when the table gets exported to Excel? If so, this maybe what I'm looking for in one of my reports.

                        • Bug in Monarch v6.01?
                          Grant Perkins

                          Hi Nick,

                           

                          I'm back at a location with a 6.01 release so have had a look at this.

                           

                          Sadly Excel seems to ignore control characters no matter how you try and get them into the cells. Maybe there is a quirk somewhere that allows another less obvious way to over ride the Excel controls, but I can't recall one.

                           

                          However if you Cut and paste or export a Memo field into Word or Wordpad and presumably other editors, it will format the data as it was on the orignal report. Strangely if you reset the field to Character C&P or export simply ignores the control characters.

                           

                          Anyway all this makes me inclined to agree with Tom - this is an anomally. Normally multi-line extracts that may need to be re-exported in the original layout will be text type info rather than data fields. So the V5 functionality seems to provide the right mix of functionality (even though intentionally split data output fields are unknown to my experience. However I have had a need to combine 2 fields to one before now for the purposes of a report, which I guess is just an unplanned version of the same thing! Simplest way of course is to treat each (could be several fields) bit of data as a field and then concatenate. I can't remember seeing any examples that would lend themselves to a multi-line solution so I have never considered it from that angle.

                           

                          If the code differences between 6.00 and 6.01 are ananlysed I wonder if there is simply an attempt to reduce the code complexity which has, effectively, made the selection rules for the memo fields common to the Character fields?

                           

                          If so reverting might be fairly easy.

                           

                          On the other hand offering some options when processing might add even more to the product?

                           

                          To solve the Excel cell format issue I guess we need some clever ideas from an Excel expert. Not me in that case!

                           

                          Grant

                           

                          Originally posted by Nick Osdale-Popa:

                          /size[quote]quote:[/size]Originally posted by Tom Whiteside:

                          - - "taking advantage of the new behavior?"

                          /quoteI'm at home right now so I can't test this.  Would leaving in the CrLf in the field cause it to wrap the text in the column when the table gets exported to Excel? If so, this maybe what I'm looking for in one of my reports.[/b][/size][/QUOTE]

                          • Bug in Monarch v6.01?
                            Gareth Horton

                            Tom

                             

                            The new behavior introduces the CrLf combination, however, a number of users were using formulae such as LSplit on these fields using the expected space as a delimiter.

                             

                            The problem is, a multiline field could be a block of text (which is where using CrLf is best) or it could be used to break out a list that wraps over multiple lines (which is where the V5 behavior is best.)

                             

                            For example, a list of information with a variable number of values on a multi-line trap 

                                               

                            Item                                                                 

                            Partxxx Partyyy Partzzz Partaaa Partqqq

                            Partlll Partvvv

                                  

                            Item                                                               

                            Partxxx Partyyy Partzzz Partaaa Partqqq Partlll                                                      

                            Lsplit is then used with a space as a delimiter to break the string into individual fields.

                             

                            When this changed, we broke these calculated fields.

                             

                            We will consider offering more flexibility when dealing with multiple line fields in the future.

                             

                            Gareth

                             

                            Originally posted by Tom Whiteside:

                            Gareth,

                             

                            Fair enough, but I'm completely missing what you mean by "some people taking advantage of the new behavior."  The technical spec sheet for the v6.01 upgrade does list revisions to multiple-line trapping as one of the new features.

                             

                            Was the CrLf addition meant to aid in trapping multi-line text, by preventing run-together of separate words in text lines?  If not, then I'm completely in the dark as to what you are talking about - - "taking advantage of the new behavior?"

                             

                            Just curious.

                             

                            Tom Whiteside[/b][/quote]

                            • Bug in Monarch v6.01?
                              Nick Osdale-Popa

                              Well, at least knowing to test for the presence of Chr(13)+Chr(10) in a string will at least help with breaking out the part #'s into fields in that scenario.

                               

                              Perhaps this can be added as a User Tip in the next Monarch Newsletter?

                               

                              [size="1"][ November 15, 2002, 06:47 PM: Message edited by: Nick Osdale-Popa ][/size]

                              • Bug in Monarch v6.01?
                                Grant Perkins

                                Hi Nick,

                                 

                                You would not believe how simple this turned out to be but the interworking with Excel led me down wrong paths for hours.

                                 

                                Anyway, text in an Excel cell can be formatted into a new line by what the Excel help calls a 'hard carriage return'. (Alt-Enter). Hard Carriage return actually means 'line feed'.     :mad:   

                                 

                                If you do that to an Excel cell (or two) and then read the worksheet as a database into Monarch, given a reasonable column width, etc., you should see the data in multiple lines. Analyse that line as you did before and you should find no chr(13) but the chr(10) will be in place. (Thinking about the original example).

                                 

                                So modify the formula you provided to simply strip any chr(13)characters from the field, leaving the Lf's in place and export to xls format.

                                 

                                As far as I can see after that, as long as your cells are an appropriate width, set to general or text and have 'word wrap' enabled you should see the data in the same format as it was on the report.

                                 

                                At least, it seems to work for me using 6.01. Have not checked earlier versions. There may be a whole load of other settings in play - so if you don't get the same results I will re-check. Let me know.

                                 

                                Grant

                                 

                                   Originally posted by Nick Osdale-Popa:

                                   /size[quote]quote:[/size]Originally posted by Tom Whiteside:

                                - - "taking advantage of the new behavior?"

                                /quoteI'm at home right now so I can't test this.  Would leaving in the CrLf in the field cause it to wrap the text in the column when the table gets exported to Excel? If so, this maybe what I'm looking for in one of my reports.[/b][/size][/QUOTE]

                                 

                                [size="1"][ November 16, 2002, 01:51 PM: Message edited by: Grant Perkins ][/size]

                                • Bug in Monarch v6.01?
                                  Nick Osdale-Popa

                                  Grant,

                                  Which version of Excel are you using?

                                  I tried using the Chr(10), but when I export it to Excel(97), I get the placeholder character where the Chr(10) should be. Once I press F2 and Enter on the cell, the linefeed takes effect. This happens both with code (.JetExport~) and manually exporting. Using v6.01

                                  • Bug in Monarch v6.01?
                                    Grant Perkins

                                    Nick,

                                     

                                    I checked it with 97 originally but have also used 2000 just now.

                                     

                                    You are getting what I got, the only difference being that I had a few records in a column so I selected the column and used Format Cells to set 'Wrap Text' which gave the desired result.

                                     

                                    I have been playing with Excel Templates today (not a subject I know anything much about I have to admit - but I think I am learning fast) and have proved to my satisfaction that I can 'Get External Data' from another Worksheet exported from Monarch 6.01 and, having defined the relevant column with 'Text Wrap'in the template, it will display multiple lines of text even though the original export file does not.

                                     

                                    Not being a coder I guess I would run this sort of thing manually but use the Automatic data refresh feature on the template.      

                                     

                                    Or if I needed an end user production version I would probably ask a colleague or the forum for help.      

                                     

                                    I figure this is either a macro or a bit of VB code to automate the process to produce an xls via an xlt, depending upon production needs. Am I wide of the mark?

                                     

                                    Grant

                                     

                                      Originally posted by Nick Osdale-Popa:

                                    Grant,

                                    Which version of Excel are you using?

                                    I tried using the Chr(10), but when I export it to Excel(97), I get the placeholder character where the Chr(10) should be. Once I press F2 and Enter on the cell, the linefeed takes effect. This happens both with code (.JetExport~) and manually exporting. Using v6.01[/b][/quote]

                                     

                                    [size="1"][ November 26, 2002, 12:52 PM: Message edited by: Grant Perkins ][/size]

                                    • Bug in Monarch v6.01?
                                      Nick Osdale-Popa

                                      I should have edit my message yesterday.  After playing around with Excel I found all I had to do to get the LF to take effect was to refresh the cell. In code it would look like this (in simplistic terms)

                                      ActiveCell.value = ActiveCell.value

                                       

                                      Apply that same technique to a range and I'm all set.