Calc - unexpected deletion of existing values

[krempch]

I was experimenting (in TEST, thankfully) with a TEXT field that had a new requirement to change color of certain values to RED.

I changed the field type to CALCULATION and set up a CASE statement to do the color change:

CASE(LeftWords(myfieldname;1)="myfieldvalue"; TextColor ("myfieldvalue"; RGB (255; 0; 0)))

FileMaker warned me that this would cause it to re-evaluate all existing values.

WHAT I EXPECTED: All the target values to be changed to RED, and all other values to be evaluated and then left alone.

WHAT HAPPENED: The target values were all changed to RED. All other values were DELETED.

I only had the CASE set to act on the one value as shown above. I did not specify any other action (like bypass if 'not').

[color:red]MY QUESTION: Is 'delete existing values' if no specific handling given a normal behavior of FileMaker? If so, how do I specify a catch-all 'not' clause to protect those values?

I want to see if I am on to something I can use to diagnose an unrelated transient problem we're having. Several fields in our database routinely have data 'disappear' - I suspect some of the legacy calculations in it may be causing the data to disappear based on what I observed in my test.

(We are trying to unravel what 2 DBAs left us without benefit of documentation).

Thank you.

[Lee Smith]

When you want to test a calculation like that, just create a temp field to test it in, before you use it in the actual field. i.e. c_myfieldvalue.

You have to give it a color for those records that don't match your calculation.

Try:

Case(LeftWords(myfieldvalue;1)="myfieldvalue"; TextColor ("myfieldvalue"; RGB (255; 0; 0)); TextColor ( myfieldvalue ; RGB ( 0 ; 0 ; 0 ) ))

HTH

Lee

Edited August 29, 2008 by Guest

[krempch]

Thank you - that this is normal behavior is really scary stuff - Having worked with other database products, I'd consider behavior like this a BUG. :eeek:

[comment]

Calculation fields are for calculating - not for storing data. I am surprised Filemaker didn't erase all your data in that field - that **IS** a bug, IMHO. In any case, I don't see how you're going to modify any of it, if the need arises.

[Lee Smith]

Thank you - that this is normal behavior is really scary stuff - Having worked with other database products, I'd consider behavior like this a BUG.

Why, it did exactly what you told it. If found the matching data, and changed it to Red. It even tried to warn you that you would loose the data by changing the field type, but you went merrily along your way an changed it to a calculation.

Before calling something a bug, you really should learn the product.

Lee

Edited August 29, 2008 by Guest

[Fenton]

krempch, you only specified data if it matched your condition. You need to add a default result if it does not match.

CASE (

LeftWords(myfieldnam e;1)="myfieldvalue";

TextColor ("myfieldvalue"; RGB (255; 0; 0));

myfieldname

)

or, better I think, since these format changes tend to be "sticky,"

CASE (

LeftWords(myfieldnam e;1)="myfieldvalue";

TextColor ("myfieldvalue"; RGB (255; 0; 0));

TextFormatRemove (myfieldname)

)

Edited August 29, 2008 by Guest

[krempch]

Why, it did exactly what you told it. If found the matching data, and changed it to Red. It even tried to warn you that you would loose the data by changing the field type, but you went merrily along your way an changed it to a calculation.

Before calling something a bug, you really should learn the product.

Lee

Changes to data values it wasn't asked to evaluate that are not specified is a BUG. Changing the field type had nothing to do with what happened.

As far as changing the field type and calc fields being used to store data I will agree with the post from Comment. If this is what you were trying to say, fine, but that's not how you came across, smart-alek.

[krempch]

Thank you for providing a beginner like me the needed steps.

[Lee Smith]

As far as changing the field type and calc fields being used to store data I will agree with the post from Comment.

I don't happen to agree with comment here, but then I accept things the way FileMaker does them because I don't have his vast background.

If this is what you were trying to say, fine, but that's not how you came across, smart-alek.

Sorry if I came across that way to you. But, I lose my patients sometimes when new users call everything they don't understand, a BUG.

Lee