KEY_ERROR and #VALUE from F9

[geneticjim]

Hi everyone,

I was wondering if anyone else has run into this issue? Several of us end-users are getting this and I am not sure why. I can open my slice that has been saved and it will be fine but the moment I click F9, then I get either *KEY_ERR or #VALUE. However, if I click in the cell and then click Enter, my value is there.

I have found that if I simply click F9 and then Save, the save will not resolve. What I have to do is click in a cell, click Enter to get a value, and then click Save. Then, once the save is done all is okay.

This is happening with several different spreadsheets. Yes, I am connected to the TM1 server instance. My calc is on Manual calc and calc on Save - not Automatic.

In my screenshot, I show pointing to an excel tab called Control for a couple values, but even if those aren't there, I still have the issue so it doesn't appear to be related. This is from the same spreadsheet that I have another thread on regarding multi-threaded calc, but this happens in various spreadsheets.

I was wondering if anyone else has experienced this and how you got around it? Thanks a lot!

VALUE and KEY_ERR issue.jpg (127.87 KiB) Viewed 7040 times

[Alan Kirk]

I was wondering if anyone else has run into this issue? Several of us end-users are getting this and I am not sure why. I can open my slice that has been saved and it will be fine but the moment I click F9, then I get either *KEY_ERR or #VALUE. However, if I click in the cell and then click Enter, my value is there.

In 99.9 to infinity percent of cases like these, the reason is that you have a DBRW formula which is pointing to a cell which has another DBRW formula. The second formula is used to get one of the element names for the first one.

DBRWs are processed in batches so the first formula can't know what the result of the second formula will be until it's finished processing... and by then it's too late. The first formula has no idea which element to use, and therefore throws a Key Error. When you manually calculate the cell, however, the second formula isn't being calculated at the same time. Consequently the first formula knows the element name that it needs to be looking at.

The solution is to make the second formula a DBR rather than a DBRW. DBRs are calculated beforfe DBRWs, and one at a time.

[Wim Gielis]

Most likely it is what Alan wrote above, but mind you,
I have seen this behaviour without DBRW's pointing to other DBRW's.
But then again, that was when using Active forms and I guess Active forms just don't like me.
Others seem to not have the kind of issues I have with Active forms.

So let's hope it is solved if you follow Alan's advice of using DBR.

[geneticjim]

Thanks a lot, guys! You know what, on my spreadsheet, I changed the DBRW to DBR and it, indeed, took care of the flakiness! Very happy about that. I had to think through what cells were pointing to what, etc. and then changed only some of them - not all.

There are some that are getting this odd behavior simply with a slice. Nothing special, nothing altered, just a simple slice. I don't know all the details so will gather those when I am able and come back to update. I've gotten tied up with some other things. I did want to let you know that using DBR resolved my issues though and thank you!!! I'll post more details on the other as soon as I can.

Thanks!

[Wim Gielis]

There are some that are getting this odd behavior simply with a slice. Nothing special, nothing altered, just a simple slice.

I would be interested in seeing that, so yes, please post again when time is available.