10 Replies Latest reply on Dec 22, 2015 1:00 PM by Extensitech

    Filemaker 14 First Portal Row Bug

    loomy18

      Hello Filemaker Friends!

       

      I am running into an issue in Filemaker 14 and I can't trace it down to anything within my solutions.  The first portal row on the first instance of creating a record via a portal in Filemaker (after opening Filemaker each time) is not being picked up by ExecuteSQL.  I am trying to sum up a portal to a total price.  The portal row does have the proper foreign key stored and is being assigned an ID, but for some reason when I am summing up the portal using the foreign key the first rows value is not included in the sum.  If I restart Filemaker, the record exists so it is saving, but it still can picked up by my ExecuteSQL select statement.

       

      This is a major issue and I feel like it is an issue with the client itself.  Does anyone have a workaround?

       

      Thanks,

      Brian

        • 1. Re: Filemaker 14 First Portal Row Bug
          coherentkris

          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

          • 2. Re: Filemaker 14 First Portal Row Bug
            alquimby

            Brian,

             

                 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.

             

            Al Quimby

            • 3. Re: Filemaker 14 First Portal Row Bug
              Extensitech

              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.

               

              Chris Cain

              Extensitech

              1 of 1 people found this helpful
              • 4. Re: Filemaker 14 First Portal Row Bug
                loomy18

                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 =

                ExecuteSQL("Select sum(c.LineCredit)
                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.

                • 5. Re: Filemaker 14 First Portal Row Bug
                  loomy18

                  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!

                  • 6. Re: Filemaker 14 First Portal Row Bug
                    loomy18

                    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.

                    • 7. Re: Filemaker 14 First Portal Row Bug
                      loomy18

                      Furthermore, restarting Filemaker and forcing it to recalculate DOES fix the issue.

                      • 8. Re: Filemaker 14 First Portal Row Bug
                        Extensitech

                        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?

                         

                        Chris Cain

                        Extensitech

                        1 of 1 people found this helpful
                        • 9. Re: Filemaker 14 First Portal Row Bug
                          loomy18

                          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!

                          • 10. Re: Filemaker 14 First Portal Row Bug
                            Extensitech

                            loomy18 wrote:

                             

                            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!

                             

                            Chris Cain

                            Extensitech