3 Replies Latest reply: May 15, 2014 10:11 AM by Olly Bond RSS

    How to populate extra digit when upload?

    Jjmcevilly _

      Monarch pulls out the data's from Tandem then I upload the fille from Monarch into anexcel spreadsheet.

      One of the column includes invoices #. Sometimes our invoices # are 5 digits, 6 digits or 7 digits. When I upload the file from Monarch, I want the invoices number to have 7 digits (if the invoice # is 12345 will become 0012345) plus the date and the month, so it would like 00123450811. THANK YOU.

        • How to populate extra digit when upload?
          Olly Bond

          Hello Jjmcevilly, and welcome,

           

          Monarch will let you do this easily with the str() functions. Assuming the field is numeric, then str(;7;0;"0") should do the trick.

           

          If is already a character field, then str(val();7;0;"0") should work.

           

          The arguments are str(_source field_;_length_;_decimals_;_pad character_) - I hope I've got them in the right order

           

          Adding the date indicator requires the dtoc() function (date to character) - try: right(dtoc(Today());4)

           

          So something like: str(;7;0;"0") + right(dtoc(Today());4) should put you on the right path.

           

          Best wishes,

           

          Olly

            • How to populate extra digit when upload?
              Jjmcevilly _

              Hello Olly,

               

              Thank you for you help. I am sorry but I am not really familiar with Monarch. Can you please explain:

               

              -str() functions

              -dtoc()

               

              Thank you again,

                • How to populate extra digit when upload?
                  Olly Bond

                  Hello,

                   

                  OK - when you have your data in Monarch, navigate to the Table window. Find the column with the Invoice #s in, and double click on this. This should bring up the Field Properties dialog for that field. From here you can see the field name, and see the type (Numeric or Character). Make a note of these details, then click on Cancel to close the dialog.

                   

                  Now, from the Data menu, selected Calculated Field, then select New, then select Formula-based. This will bring up the field properties dialog for a new field, so we can give it a name like GoodNumbers, define it as type Character, make sure the data length is at least 11 characters, then click on the second tab and enter the formula from my previous post in the dialog, then click OK.

                   

                  This should give you two columns in the table - the original Invoice # column, and a new field called GoodNumbers with the data in the format you want. Now, the export from Monarch could be from the table or from a summary - I'm going to assume that it's from a table for the moment. Assuming that you don't want the "bad" numbers anymore, then in the Table in Monarch, right-click on the old Invoice # column and select Hide. Now the export will only include the GoodNumbers.

                   

                  Best wishes,

                   

                  Olly