2 Replies Latest reply: May 15, 2014 10:09 AM by elginreigner _ RSS

    Need assistance with a calculated field

    JonWagoner _

      I am a new Monarch user and would appreciate any help you might be able to provide. I am attempting to create a calculated field for the first time. The equivalent excel formula would be as follows (assume you are dropping this formula in cell B3):

       

      =IF(D3=””,B2,C3)

       

      Column D is a document type and column C is a document number. The type of data is a detailed accounts receivable aging, but it is too detailed. When the type is blank that record is not actually the next invoice, but rather a payment that has been applied to the invoice above it. The document number is a payment reference rather than the appropriate invoice it was applied to. The formula above replaces the document number for the payments with the appropriate invoice number, but if the line actually is an invoice it leaves the number as is. It works great in excel, but I cannot find a way in Monarch to reference the previous record.

       

      Data Kruncher provided a great answer to a similar question on 10/21. I set that scenario up as a learning experience for myself but it won't quite get me there. My sample data is as follows (Inv# is the field I need to calculate):

       

      Cust#     Inv#     Doc#     Type

      010019     10     10     SLS

      010019     10     CR10     

      010019     10     CR22     

      010034     15     15     SLS

      010034     35     35     SLS

      010034     35     CR33     

      010050     50     50     INV

      /code

       

      The fact that the "Type" is blank is the driver that tells me the Doc# needs to be replaced. As you can imagine in excel the copy the cell above approach because the first blank record is dealt with before the next is addressed.

       

      Thanks to advance to anyone who might be able to help.

        • Need assistance with a calculated field
          Olly Bond

          Hello John, and welcome,

           

          Looking at your data, I'd be tempted to use an append template to pull in the TYPE, as well as the DETAIL, and then compare the two fields to distinguish payments from invoices.

           

          But if you're working from a table of data instead of from a report, that might be a bit fiddly. The problem is that Monarch won't let you make fomulae that refer to other rows of data - so:

           

          B3=IF(D3=””,B2,C3)

           

          is impossible, as B2 is data from another row.

           

          Two tricks - if it's a report, use an append template. If you're working in a table, use Recno()1 or Rowno()1 and export this, then look it up joining on Recno() or Rowno().

           

          Best wishes,

           

          Olly

            • Need assistance with a calculated field
              elginreigner _

              As Olly stated, if this is a table it's a no go. However, as he also stated if this is a report, there are many options. Olly's will work, so would trapping and using the option 'copy value from previous record if it is blank'. Either will working a report.

               

              If it's a table and you still want to accomplish this, you can import the table into Monarch, export it as a text (any type of report file) and read that through another pass in Monarch.