Clac Field wont recalc

[ljm]

I have a strange problem. I have a series of fields that are calculated results via a case statement for a field called trans_descrip. Well it turns out there was some odd data in trans_descrip that was throwing off the case evaluation, so I cleaned up trans_descrip using a series of evaluations and replacements.

Now, all for all the trans_descrips I replaced the calculated fields will not re-evaluate.

If I go into each of those trans_descrip fields and add a space or delete and re-add the last character the calc fields re-evaluate. Very puzzling to me.

Interestingly, when I created a new set of calc fields, and pasted in the exact case statements from the originals, they evaluate correctly.

The original calcs are not stored - they get evaluated each time there is a change.

I can fix this by swapping out the original fields with the new temp fields, but would rather hear if there is a reasonable explanation.

This really has me stumped and my client is a little leary of this process now that I seem to have killed a lot of reports.

Any thoughts would be appreciated

Edited April 8, 2006 by Guest

[LaRetta]

Are you using 8.0v1 or 8.0v2? I wonder if it is field-creation order ...

You may have also lost your index (another 8.0v1 problem with auto-enter calculations). Have you tried opening field definition and Storage tab and changing to Index NONE and back out of Define Fields; then go back in and turn indexing on again.

[ljm]

I am using 8.02.

The indexing suggestion is a good one. It looks to be as though the field value is being evaluated as Blank. So resetting the index might help.

Thanks for the suggestion.

[ljm]

Unfortunately, the reindexing didn't fix the problem.

I can reproduce this behavior by replacing the contents of the field to be evaluated with Trim(field) once the contents are trimmed they no longer get evaluated. Strange.

[LaRetta]

That makes no sense if it works with new fields however. We simply don't have enough information. Are any of the fields from related tables? It would help if you can post your fields (indicate table name, field name name and data type) and specifically list the Case() calc. Better still ... post a file showing the problem.

[Ender]

Depending on what your calc is checking for, it may be necessary to uncheck the option, "Do not evaluate if all referenced fields are empty." If that doesn't help, perhaps you can post a calc for us to check.

[ljm]

Ok, here is a small chunck of the file, with two fields (Trans_JT_desc_eval)with the same case statement in both. They both evaluate what is in Trans_Description.

Try replacing the contents of Trans_description with trim(Trans_description)and the field will not be evaluated.

Tnen try re-replacing the contents of Trans_description with Trans_description&" " and the field will evaluate.

Thank you for your help with this, I am sure it is something elemental that I just don't know.

Small_Trans2.zip

[Ender]

Well, this calc is a circular definition:

Trans_JT_Desc_ Evaluation (calculation, text result) =

Case(

Trans_Date>Date(5;31;2005) and PatternCount(Trans_Description;"honorarium")=1; "No - honorarium";

Trans_Date>Date(5;31;2005) and PatternCount(Trans_Description;"grant")=1; "No - grant";

Trans_Date>Date(5;31;2005) and LeftWords(Trans_Description;3)="Bailis Fund 2005"; "No - Bailis";

Trans_Date >Date(5;31;2005) and Trans_JT_Desc_ Evaluation=""; "Current"

)

I'm suprised that it allows this (must be a "feature",) but that explains why the result toggles between "Current" and a blank anytime the field is changed. The other calc is dependant on this one, so that toggles the opposite result.

[LaRetta]

Your second test field is result of number. And you have spaces at the end of your Trans_Description field. If you remove them, Trans_JT_Desc_Evaluation will update. Put your cursor in the Trans_Description field and run Replace Contents via calc with:

TrimAll(Trans_Description ; 0 ; 0 )

I'm unsure, Mike, but maybe the calculation is acceptable because the RESULT of the evaluation (for that calc) doesn't happen until it hits the first true ... and if it fails all prior tests, then it would (maybe) BE still empty (because it is indexed?). All speculation, mind you.

Note: I really hate seeing dates and text hard-coded into a calculation. If you would like to explore other ways of addressing your situation, let us know.

UPDATE: Oh, if you really want to see it break BIGTIME - and do what you are suggesting, Mike, change both calcs to unstored! It goes into wonk-city! How cool! So the circular will work if both calcs are indexed ([color:blue]so they work as a toggle) but if changed to UNSTORED, it'll wonk (goes into repeated attempts to redraw and produces ? and if you try to put it back to indexed FM won't let you!? The default result needs to be changed and the self-reference removed. What do you want if records are less than (and equal to) 5/31/2005? Would they also be considered "Current" or do you want those fields blank?

I kinda like the toggle concept using circular reference with calcs indexed. :yep:

Edited April 8, 2006 by Guest
Added Update