6 Replies Latest reply: May 15, 2014 10:12 AM by kbelli _ RSS

    Problem with V11 Lookup Calculated Field

    Kathi _

      I have just converted to Monarch Pro V11.0.3.11 from Pro 10.5 and I am having problems with a lookup calculated field in a model I've used in several versions.  The field isn't returning any values at all.  There are 42 lookup values from a field that came from the detail template.  If the value is found, the output value is to be an "X".  This "X" is later used in another calculation.


      However, when I look at the field results, it is all null.  I created a new lookup value from scratch (since I didn't want to mess with my original).  I entered one of the values and it worked perfectly.  I entered a second and again it was perfect.  However, the third value caused the original results of all null values.  I've tried different third values and sometimes I can get one to work and sometimes not.  If I get one to work and then try to add the 4th value, the problems begin again.


      Additional info - no filters and no sorts are currently active on this data.


      I really have no clue how to address this issue.  This particular calculation is a key component of my model and it must work reliably.  Has anyone else had this issue?




        • Problem with V11 Lookup Calculated Field
          kbelli _

          Kathi, when your lookup field returns all nulls, have you tried closing the model and reopening it?  It seems like V11 sometimes "forgets" how to calculate an entire field, and it just populates the entire thing with null values.  It can be very frustrating - particularly when a null value is supposed to mean something specific.  In that situation the best advice that I've heard so far is to close your model and reopen it.

            • Problem with V11 Lookup Calculated Field
              Kathi _

              Yes, I have closed and opened it several times.  No success.  I can revert back to 10.5 and the model and field work flawlessly.

                • Problem with V11 Lookup Calculated Field
                  Olly Bond

                  Hello Kathi,


                  Have you tried importing the field from your 10.5 model into a new model in v11?


                  Best wishes,



                    • Problem with V11 Lookup Calculated Field
                      Kathi _

                      Yes.  I tried that before I started recreating it manually.  Then the manually created field wouldn't work (at least consistently) after the 2nd item in the lookup value.

                        • Problem with V11 Lookup Calculated Field
                          kbelli _

                          Hi Kathi,


                          I'm no expert on any of this, so I apologize if I'm just wasting your time. 


                          I just had a calculated field null out on me again, and I thought of your thread.  In the model that I was using I had an external lookup, and I tried going to Table: Data: Refresh: Refresh External Data.  I don't know if it was a coincidence or a direct effect, but Monarch repopulated my calculated field after that. 


                          It's unfortunate because I don't know how to trigger this problem, so it's difficult to play around with.  Do you have any external lookups in your model?  Could you (or did you) try refreshing the data for them when you encounter one of your internal lookups nulling out?  I know you said there are no filters or sorts on your data, but maybe you could also try creating one, applying it, and then doing : Refresh : Refresh Sort/Filter to see if that repopulates it.


                          And really, I have no idea if we're even encountering the same problem.  I can't recall if I've seen an internal lookup depopulate itself, but I know I've had numerous formula-based calculated fields do that.  I'm just kind of guessing that it's the same thing causing both.



                            • Problem with V11 Lookup Calculated Field
                              kbelli _

                              I was able to recreate what was most recently causing my calculated field to depopulate, so I was able to test a couple of things.  There may be other things that also cause calculated fields to forget themselves, but this seems to trigger it for me:


                              [INDENT]Step 1:  Create a calculated internal lookup (I think any calculated field will do).


                              Step 2:  Create a summary using at least two keys, one of which is your lookup.  Load your summary.


                              Step 3:  Edit the summary, deleting the lookup key.  Accept the change, and let your summary recalculate.


                              Step 4:  Edit the summary, adding back the lookup key.  Accept the change, and let the summary recalculate.[/INDENT][/INDENT]


                              At this point, at least in two different models that I've tested it in, the lookup is completely null in the summary.  And if I switch over to the Table view, it's completely null there as well.


                              I've found that refreshing the Filter/Sort doesn't fix it, but refreshing external data has worked so far for me.  If this continues to be a problem, I think I'm going to keep a dummy database on our network and just add a link to it for each of my models so I can refresh the data in these situations.


                              Kathi, is this kind of what you're encountering, and are you able to force your lookup to refresh in this method?