Maybe create a new Glue Usage and calculation fields?
You could using "Hide if" only show the relevant calculation field (old or new) if you're on an old or new record.
Hope this helps!
This is one use case where an auto-enter calculation would serve better than a calculation field. If you put the same formula in the auto-enter, it will update whenever you create a new record or when QTY changes.
You can also use the "prevent modification" option to cause the first evaluation to "stick".
I tried the auto-enter calculation, but it didn't nothing when I created a new record.
Maybe I did not explain my situation clearly. I have two database that they are related.
One is called Glue_Usage and the other is called Journal.
Glue_Usage's database contains the following fields:
- Item name
While Journal's database contains the following fields:
- Item name
So, when I create a new record in Journal database and choose the item name and type the QTY and unit (pc/pr), it will calculate the total usage of glue1, glue2, glue3 base don the data from Glue_Usage database.
What I did is create a new filed called "auto_glue1" then double click the field name. After that I copy and paste the existing formula from total_Glue1 to auto_glue1 under Auto calculation tab and check the box of do not replace existing value (if any). However, it did nothing when I created a new record. Is there anything I did wrong?
Since one of my product's glue usage is changed. I am considering to create a new record with same item name with r1 after it and enter the new glue usage information.
But this is my last option if I don't have any other choice.
When you created the record, your fields (QTY, unit, etc) were empty, so the auto-enter calculation computed the formula using empty fields.
If you untick "Do not replace existing value..." then it would evaluate as soon as any fields change, like a Calculation field.
But I need to check "do not replace existing value" because I don't want to old records be changed.
In this case, is there anything I can do to make the auto calculation worked?
This is when a Scripted Set Field is preferred. or Auto-enter the calculation in a normal field and prohibit modification.
The old records will only change if one of the predicate fields (for example, QTY) changes on that record. That's probably needed, since you want the new values to apply if you should change the original record.
If, on the other hand, you need the old method to apply to the old records even if they're modified, then you need either a second field (as suggested by Chloe), or use a more sophisticated method, like storing the calculation you need in a separate table and using Evaluate to determine the correct value (selecting which calc should apply). The problem with the second field is that you start loading up your schema with extra junk - every time you want a new method, you have to add a field. The problem with the separate table is that it's harder to implement.