5 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    Calc Field formula to lookup

    Gary Kaye

      Is there a way to change a calculated field from a formula to a lookup.

      It seems that if I remove the old & add a new calc field, I have to reinvent all my summaries.

        • Calc Field formula to lookup
          Grant Perkins

          Hi Gary,

           

          I'm not aware of a 'convert' feature but there may be some way to to process things in a particular order to effect the change with the least effort.

           

          Two starting questions;

           

          What do you mean exactly by "reinvent"? Start completely afresh or at some intermediate point in the summary definition?

           

          Which version of Monarch are you using and Standard or Pro? (AND if Pro are you using an internal or external lookup?

           

           

          Grant

           

          [size="1"][ October 05, 2007, 07:39 PM: Message edited by: Grant Perkins ][/size]

          • Calc Field formula to lookup
            Nigel Winton

            HI Gary

            I managed to get over this problem by setting up a new calculated field for the Lookup and then referencing this one in the original formula calculated field. That is the formula in the original calculated field will be the name of the new field.

            That way you do not have to change anything in the summaries as you have not removed the original field.

             

            Regards

             

            Nigel

            • Calc Field formula to lookup
              Gary Kaye

              Grant,

              I'm using Monarch Pro V9.

              I would like to change the calculated field from a formula to an internal lookup.

              I believe, that if I create a new calc field, I would have to add this new field to all my summaries & remove the old field. This could be a little difficult, with filters, etc.

              Nigel suggested referencing a new field from an old one. That would probably work, but it's not clean.

              Any thoughts?

               

              Gary

              • Calc Field formula to lookup
                joey

                Monarch has intelligent behavior built in that detects when you rename a field, and updates dependancies (like summaries).  This is usually for the best, but in your case it's going to add a lot of extra work.

                 

                 

                In your case, suppose you have a calculated field "Field1".  You want to replace it with a lookup.  I would add a lookup "Field2".

                 

                The easiest approach from this point is to make Field1 equal Field2.  But this leaves an extra field.  My suggestion is dirty and largely unsupported, but it gets the job done.

                 

                Open the model file in a word editor.  Find the piece of the XML that has the name for each field.  Swap the names of your two fields in the text editor.  Open it up in Monarch to make sure you didn't break anything.  This is the only way that I know of to bypass the auto update that occurs when you rename a field.

                • Calc Field formula to lookup
                  Grant Perkins

                  Originally posted by Gary Kaye:

                  Nigel suggested referencing a new field from an old one. That would probably work, but it's not clean.

                  Any thoughts?

                   

                  Gary /b[/quote]Gary,

                   

                  I think Nigel's suggestion is the simplest, quickest, easiest and most flexible approach. All you need to do is create your internal lookup table and then comment out the exiting formula (for a historic record) in your existing field and enter the name of the new lookup field as the formula. Job done, nothing else needs to change as far as I can tell. Even filters should be unchanged unless I have missed a nuance somewhere.

                   

                  This is, conceptually, quite similar to the idea of Linked Objects introduced with V9. However I don't think linked objects can cater for linked substitutions between different types of calculated fields. I assume for reasons of field behaviour which would not be an unreasonable constraint.

                   

                  The benefit of the approach is that any future changes are also easy to apply - just create the 'new' input from what ever source and set that (probably hidden) field to be the source for you summary key field. One edit in the table, no changes to the summaries. I am thinking that such an approach could be very useful with linked object versions of summaries.

                   

                  Joey's approach is one I also find interesting but I suspect that there may be a bit more to understand (and therefore potential for error creeping in) where different styles of calculated fields are involved. It would be fun to check but I don't have the time to experiment at the moment. I think it might also fall foul of your 'clean' requirement!

                   

                  HTH.

                   

                   

                  Grant