This is hard to follow. Can you take another stab at it using actual examples and some screen shots? For starters, make sure the calculation results match the field type, i.e.text to text or number to number. Are these auto enter calcs on text fields, or calculation fields.
You can copy and paste your expression from the specify calculation dialog to here. Then tell us how it is failing. For example, if your auto-enter calculation refers to a field from another table, it won't update automatically when the value in that field from the other table is modified.
Hi thanks for the responses,
Yes it is pulling from another table so I have - If (field name = field name in different table ; populate this 1, or this if not)
So this will only work in an actual Calculation field not an auto-enter text field using the calculation option?
Do you know how I can connect a field from one table to another, have the info populate in said field from the other table but still have the ability to overwrite the info?
Basically I work on a film and we need the editors file # for the VFX shot to populate from the editors table into our table in a specific field. But if we do not have the correct info from the editor we want to overwrite the info in our table without having to go back to editor to fix it in his because ultimately it is the info in our table that goes back to the studio not the editors. I'm new to filemaker, although I have learned rather quickly to create scripts and such this one is alluding me. Thanks!!
The other way that auto-enter calculations fail to work as expected is that when you add them or change their definition, the value in existing records do not update. You can fix this with a replace fields action or the trick described here: Updating values in auto-enter calc fields without using Replace Field Contents
That might be enough to get what you need, the only added thing would be a script to update this field run from an OnObjectSave trigger on the original field should you ever need to go back and edit that value--say to correct an error.
Another way to make this field work as something that can be manually overridden is to use two fields with this calculation. If the data field in the current table is empty, refer to the field from the related table, otherwise show the contents of this added data field. That way, you can override the value by editing the value in this other field.
Thank you. I tried all of that and nothing was working for me but I did finally figure it out.
So when the TYPE of the field was a Calculation Field the If statement for pulling data from another to populate in the current field/table looks like this:
If (Field Name of current table = TableName::FieldName ; TableName::Field Name ; 0)
So basically it states IF this field data in the current table = info in the foreign table in this specific field then the results to populate in the current table field will be from the foreign table field or else zero.
It worked fine in the calculation type field but when I changed it to text/auto-enter/calculation field it did not populate the info. So what I had to do was add the current table in order for it to work.
So If (TableName::Field Name of current table = TableName::FieldName ; TableName::Field Name ; 0).
I can't believe I racked my brain for the last two days and all I had to do was add the current table name to the calculation. SMH. Well thank you Steve and Phil for your help.
In an auto-enter calculation, you can use Self to refer to the field in which you are defining the auto-enter calculation.