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

    Memo Field

    baghu _

      Hello,

       

      I've two problems with my report and one of them is memo field length. I've a calculated field and in which I'm trying to create a memo field. The data is getting exported to excel sheet with out any issues, but the "data lenght" is fixed with lenght as '10'. I wanted to change that to 20 or 30, so that my excel sheet will not look odd. In this report, I've to manually reset the row and column lenght in excel for each field and it is not practically possible.

       

      Please help me on this.

       

      Another problem is, I wanted to remove a specific information from the memo field and it has the format like '#NNNNNN'. The data is always in this format and the N will be replaced by a 6 digit number.

       

      This is the only thing pending in my report, I want somebody could help me immediately.

       

      Thanks and Regards

      Baghavathy

        • Memo Field
          Grant Perkins

          Hi Baghu,

           

          Let me try your second question first ...

           

          If the string you wish to remove always starts with "#" and always has 6 digits but its position in the record changes, PROVIDING THAT there are no ther "#" character before it in the field you can use the INSTR() function to identify the position of the "#" and so find the start position of he 7 character string you want to remove.

           

          To remove it the easiest method should be the use the STUFF() function using the INSTR value to give you the starting point. You know the length of the string is 7 so just use STUFF() to replace the string with nothing.

           

          There are alternatives. Once you have the valay from the INSTR() function you could use he LEFT() or SUBSTR() Function to select all characters up to that position and then the SUBSTR() function to pick out everything AFTER the string and concatenate the two results in a new calculated field.

           

          Similar methods might involve using INSTR() with LEN() which would offer the opportunity to calculate a number of characters required backwards from the end of the string using the RIGHT() function.

           

          It sounds like the STUFF function will be the quickest and easiest but I mentioned the others in case there is a reason why STUFF will not work.

           

          For your Excel format problem I can say that the Export process does not support setting the size of Memo fields for Excel as Excel does not understand a 'Memo' field. There is a section in the Monarch Help which discusses this.

           

          If you can set the exported Worksheet to the widths you want after the first creation and then export to it by appending data the column widths set should be retained. If that is not possible I believe you can use Excel templates to set the the format for the worksheet you are exporting to but that is Excel functionality that I have not had to use. Perhaps some of the other forum users will be able to offer their help with that question.

           

          I hope this helps in some way.

           

           

          Grant

           

          [size="1"][ November 16, 2006, 07:04 AM: Message edited by: Grant Perkins ][/size]

          • Memo Field
            baghu _

            Thanks Grant..

             

            It really helped me..