After you create the portal row are you causing a commit before trying to query the child table with SQL?
I don't think ExecuteSQL will return uncommitted records.
Why use ExecuteSQL for this purpose? Its way easier to put a summary calculation in the child or parent table
Instead of using SQL, why don't you add a calculation in the parent table, i.e., Sum ( related table::total price). Put the new calc under the portal directly beneath your total price field.
1 of 1 people found this helpful
Need a bit more information.
First, just so we can write a response can make any sense, what tables occurrences are your layout and portal based on?
Then, what is the ExecuteSQL statement you're using, and why? (ExecuteSQL doesn't take into account portals, or relationships for that matter, so the fact that the missing record is first in the portal is almost definitely not the deciding factor.)
With a little more info, we can help you troubleshoot.
The tables I am working with are:
Credit (Pk = Id)
CreditLine (Pk = Id, Fk = FK_CreditId)
Credit.Id --> CreditLine.FK_CreditId
Set field Credit.TotalCredit =
From CreditLine c
Where c.FK_CreditId = ?"; ""; ""; Credit::Id)
This is the most simple example of the bug happening, it does however happen in other aspects of my application as well using a similar setup. I think its an indexing issue, but I really am not sure. Its as if the record doesn't exist, but the FK is properly set and I can see it in Filemaker, but my SQL statement can not.
Also, I don't think it has to do with record commits. If I close Filemaker and reopen the application and go back to the Credit record and try to force it to recalculate the sum it still will not take the first portal record into account.
Thanks so much for your quick responses!
Ok so it isn't just the first portal row. I restarted Filemaker and it is the first portal you enter on a fresh start of Fikemaker into a portal.
Furthermore, restarting Filemaker and forcing it to recalculate DOES fix the issue.
I didn't see anything obvious in your description, and I haven't had this problem myself, so I threw together a quick test file (attached).
I can't reproduce the issue unless I leave a portal row uncommitted.
So the next step is, what's different between this test file and your file? Or, are you able to reproduce the issue in this test file? If so, what am I doing differently?
test_sql.fmp12.zip 66.9 K
Thank you so much for your help. I feel really silly, but I have to admit my mistake, it was due to a missing commit step after all. I had added the step earlier when trying to debug, but modified the wrong script!
I'm so sorry for wasting your time, but thank you. I guess I just needed to "talk it through" with someone to notice my own oversight!
I guess I just needed to "talk it through" with someone to notice my own oversight!
That's often all that's needed, and I'm grateful to all the patient (and no so patient) people who do it for me time after time. Glad I could pay it forward!