I have a Table (Table A) and layout with a portal to Table B. Table B has a Number field defined with an auto-enter calculation.
The calculation is complicated (and probably not important) but basically it refers to another table (Table C), grabs a date from Table C, then calculates the # of days between a date entered in a field in Table B.
When I do this:
click into the Date field in Table B (in the Table A layout's Table B portal, in an empty row)
type a Date in Table B portal row
tab to the next field
The auto-enter calculation works as expected
When I do this in a script:
Go To Portal Row [Select: On; Last]
Set Field [Table B::Date; $date]
Go to field [Table B:: another field]
The auto-enter calculation fails: specifically it seems to fail as if the TableC::Date value is missing when the calculation is evaluated.
I determined a workaround which was to turn off 'Do not replace existing value of field (if any)'
But I'm left with the question - why does it behave differently? Conceptually, what is happening differently between the "manually type date in new portal row" vs. "script creation of new record in portal row" that would explain this difference?
Maybe adding a CommitRecords( ) after setting the field?
Or forcing the field to update by adding some bogus calculation/operation that refers to that field?
I think I figured it out - when creating a new record through a portal, it appears that Autoenter calculations (that depend on the Foreign Key) are evaluated twice - once before the key field has been set, and once again afterwards.
In this demo, Field B and C have the same calculation, which depends on a value
from Table C.
Field B has "replace existing value" turned OFF
Field C has "replace existing value" turned ON
What appears to be happening is that the foreign Key that links Table
B and C is not being populated until after the new record is created,
which means field C is actually evaluated twice
(once before and once after the foreign key is changed).
Is there a sliding panel, tab panel, or popover involved in this anywhere? Or any sort of conditional calculation for hiding / showing any of the objects?
Greetings from South Carolina - I had the same issue recently, and it involved a complex, sequential auto-enter LET statement in the related table. I had to commit records, re-trigger the related auto-enter, and then refresh the portal. I still yet have not concluded why this was necessary, but I think it had to do with the complexity of the LET statement vs. when the data trasaction(s) occurred.
My preference and expectation : when a new record is being created via a portal:
the key / foreign key fields which are part of the relationship in the portal would be given values first.
then, any auto-enter fields would be evaluated
Instead, what seems to be happening:
the new record is created with missing key fields.
Autoenter fields are evaulated
Key fields are set
Autoenter fields are re-evaulated (if they depend on the key fields)
The problem illustrated in the sample database I uploaded is that if "Do not replace existing value of field (if any) " is set, then Autoenter fields would not be updated again in step #4.
Assuming my explanation is correct:
has this behavior changed in recent versions of FileMaker?
Related records in portals when editing can be quite tricky and the current record open state is another dimension to consider. The scope is exactly coming from the row TO of the relationship and foreign key and primary key when uncommitted might not resolve dependent related records for the lookup or auto-calc. All to be fetched should be only one TO away to succeed and latency / refresh seams to be lazy evaluation. Cacheing might be in need to make It work reliably if possible….
Recall that a recent change in the LOOP script step is the FLUSH option. It's options are Always, Minimal, and Defer.
Although this isn't a loop, this sounds as though creating data manually through a portal you get the Always option behaviour but the scripted creation gets the Minimal or Defer behaviour.
It might be that the speed of human interaction (being so slow) gives the computer enough time to bring all of the relationships into play, whereas the scripted interaction doesn't give it that time.
This is not an answer to your question but the point that you are scripting, why not gather all the data you need and create the record with everything it needs? I know it's more effort but you then have complete control.
It might be that, or it might be the order in which fields are set in the script vs. human scenario. TBD.
My preference is for calculations to be in one place - either entirely in a script, or entirely in the field definitions. This makes maintenance easier. In this table calculations were already in the fields, so I wanted to keep it that way.
Another solution that should work: the AutoEnter Calculation could detect the "missing key field" case and return "". Then, when the key field is updated later, the AutoEnter should evaluate properly, even if Do not replace existing value of field (if any) is enabled.
