Calculated field Editable ?

[Pinky]

Is it possible to have both ?

To simplify things, lets say I have C = A + B. If I enter A and B, C is calculated. However, sometimes, I have a C value, but no A and B, and I want to fill C myself. Since it is a calculated value, it won't allow me to edit it (even if I check allow entry).

Anyway I could o this ?

[trevorg]

You can't do it directly as you describe, but you can do something that gives that effect.

Create one new field. Call it C_Override or something

Then change your "C" field calculation to:

Case(

IsEmpty(C_Override), A+B, C_Override)

This means, If the field C_Override is empty, then C is whatever A+B equals, but if something is in the C_Override field, that's what I want the "C" field to display regardless of what A+B is.

You can overlay the C field directly on top of the C_Override field. Set the C field to not allow entry, and the C_Override to allow entry. If you click on the "One" field, you will be taken directly into C_Override where you can enter your value.

Hope that makes sense!

[Pinky]

Makes perfect sense ! I had made something like that, but didn't think or overlaying the fields for display ! You've solved it for me

Thanks a lot for the suggestion.

[jasonwood]

Set the C field to not allow entry, and the C_Override to allow entry. If you click on the "One" field, you will be taken directly into C_Override where you can enter your value.

Just a word of warning here... if you use this field in searches, it will only search the override field (if you enter the data manually & you conduct the search on the same layout that is).

The best workaround would probably to use a separate find layout which does not contain the override field.

[LaRetta]

Hi Pinky!

Another option is to have field C be a regular field but set it to auto-enter calculation. Then base the calculation on A + B. This will insert value A + B in field C, but will allow you to change field C if you wish. Oh, so many options with FM Don't you just love it?

LaRetta

[jasonwood]

Good point LaRetta. Do you (anyone) know what the limitations of an auto-enter calculation field are?

I assume it always updates if you change one of the involved fields on the layout, but what if you change them from another database or a script, without actually visiting that record?

[Pinky]

Hi LaRetta,

I actually tried that, but it couldn't apply. Here's what's happening :

I have a catalog (catalog.fp5), and each item has a code (CODE) regular price (PRICE), and a discounted price (DISC_PRICE).

In my invoice, I have the following columns : DISCOUNT (yes or no), CODE, OUTPUT_PRICE, QUANTITY, TOTAL_PRICE.

To add an item, I input the CODE, and the other values are looked-up.

OUTPUT_PRICE has to know what DISCOUNT has been set to in order to decide between PRICE and DISC_PRICE. Since none of this info (+ what item I selected) is available when I create the invoice, auto-enter calculation didn't work (it probably needs all those fields to be known when it is being created).

Additionaly, since it would no longer be a calculation, togleing ( <- spelling ?) DISCOUNT to yes and no would have no effect, since the value was set on creation, and no longer being updated.

In response to the find issue, it shouldn't matter, since I probably won't be using that field to search for a specific invoice (other fields such as CUSTOMER and DESCRIPTION are the ones I'll probably use).

Thanks for the inputs though...always good to know what's available !

[Mike D.]

what the limitations of an auto-enter calculation field are?

An auto-entry field calculation is done only upon the creation of the record. As a quick test, I created a file with 4 fields -- f1, f2, c1 and ae1. The c1 field is a calculation of f1 + f2. The ae1 field is an auto-entry of f1 + f2. When the record is created, ae1 only gets the value from the field (f1 or f2) that is first filled in, not f1 + f2 as you might expect . If you change one of the values at a later time, the auto-entry field does not update.

HTH, Mike

[BobWeaver]

An auto-entry field calculation is done only upon the creation of the record.

That's not quite true. An auto-entry field calculates whenever both of these conditions are true:

1. It is completely empty, and

2. Any of the fields on which its calculation formula is based, is changed.

So, if you have an auto-entry field, FullName, with the formula:

FirstName & " " & LastName

it will calculate as soon as you enter "Bob" into the FirstName field, giving you the result "Bob." Then, when you enter a last name into the LastName field, nothing will happen, because FullName already has a value and won't recalculate. So, FullName will remain as just "Bob."

If you use this formula instead:

Case(IsEmpty(FirstName,"",

IsEmpty(LastName),"",

FirstName & " " & LastName)

Now, the FullName field will remain empty until both the first and last name fields have been entered, and will then calculate correctly.

Later, if you clear the field, it will be ready to recalculate again as soon as one of its referenced fields is changed.