1 Reply Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Calculated Fields

    Jose _

      I have Monarch v8 and I am currently trying to use a calculated field to return values that contain certain criteria.  For example if a cell shows "Fee Accumulation" then how do I get the calculated field to return all possibilities using only "Fee" as a criteria.

       

      Help?!?!?!?!?

        • Calculated Fields
          Grant Perkins

          Hi Jose,

           

          Do you want the result of this to be a FILTER or simply an additional field which holds the content of the field being assessed ONLY if the assessed field begins with FEE?

           

          If it is a 'Begins with' situation have a look at the LEFT function in Monarch.

           

          LEFT(FIELD,4)="fee "   (Note the space)

           

          used as a filter will find only fields that stat with the word FEE.

           

          Taking this a stage further, to populate another field with the text from your original field ONLY if it begins with the word FEE (and not Feel for example) your formula should use the IF() function.

           

          So,

           

          IF(LEFT(FIELD,4)="fee ", FIELD, " ")

           

          This formula reads as "If the field strarts with the word FEE, populate the the calculated field with the contents of the other field, otherwise make it blank".

           

          There are variations on that theme of course.

           

          If the word FEE could occur anywhere in the text in the the original field you can do something similar in both cases but may wish to use the INSTR function to identify whether the FEE occurs WITHIN the field somewhere rather than at the beginning.

           

          INSTR(" fee ",FIELD)>0

           

          should do the trick (note the leading AND trailing spaces to avoid selecting feel[/i] or coffee[/i] for example). Applying that to the IF formula gives:

           

          IF(INSTR(" fee ",FIELD)>0,FIELD," ")

           

          If the word FEE can occur at the beginning or IN the text or only at the END of the text you may need to combine each version of the search - with and without spaces at beginning and end - for completeness.

           

          However if you can be sure that the string of characters FEE, wherever it exists in your original field, will always mean you will want to select that field then you can forget about the need for the spaces and simply use FEE as the identifier.

           

          LEFT(FIELD,3)="fee"

           

          for example. Note that I have adjusted the number of characters to be checked from 4 to 3.

           

          Check out the included HELP for the FUNCTIONS REFERENCE which provides excellent examples of how these and other Monarch functions can be used - I refer to it frequently.

           

          HTH

           

          Grant