8 Replies Latest reply on May 22, 2015 10:15 AM by disabled_JustinClose

    Delete portal row not working - get(FoundCount) reports 1 too many records

      Title

      Delete portal row not working - get(FoundCount) reports 1 too many records

      Post

      Hey all,

      I'm using FMPA 14 and I'm having an issue with a 'delete portal row' script step.  The step itself appears to work correctly, and the record is disappearing - I have a second window open showing me the records I am working with while I'm developing this.  After deleting that portal row/record the same script goes off to a utility layout, finds the remaining related records, and tries to reserialize them.  However, it is failing because I check for the proper count of records before doing this 'replace field contents' step.

      The relationship is a simple Parent-Child table:  TableA::RecID = TableB::ID_fk

      Here are the script steps involved; the reserialize part is actually another script, but I copied the steps out of it and combined it into one so I could export the steps.  (I tried to make it look nice, exporting to PDF is a bit of chore.  And this forum editor doesn't appear to respect different formats I apply to sections, e.g. 'preformated'.)  Also, if there are missing if/end-ifs, don't worry about it too much, it's just my pasting job - the script otherwise works fine, and this is just a portion of it:

      ----------------------------------
      Delete Portal Row [ No dialog ]
      Set Variable [ $LastError; Value:Get(LastError) ]
      
      If [ $LastError = 0 ]
          Go to Object [ Object Name: "3_1" ]
          If [ ScratchBooking::Traveler_Count_uc = 1 ]
              Go to Object [ Object Name: "2_1" ]
              Set Field [ ScratchBooking::Option_g; "Single" ]
          Else
              Set Field [ ScratchBooking::Option_g; "" ]
          End If
      
          Perform Script [ “Reserialize”; Parameter: List ( 123 ; Scratch::Count_uc ]
      
              #--------------------     (2nd script starts here...)
      
              Set Variable [ $SetID; Value: GetValue( Get(ScriptParameter) ; 1 ) ]
              Set Variable [ $SetCount; Value: GetValue( Get(ScriptParameter) ; 2 ) ]
              If [ IsEmpty ( $SetID ) ]
              ...
              Else
                  New Window [ Style: Document; ... ]
                  Go to Layout [ “Scratch_Child UTIL” (Scratch_Child) ]
                  Enter Find Mode [ ]
                  Set Field [ Scratch_Child::ID_fk; $SetID ]
                  Set Error Capture [ On ]
                  Perform Find [ ]
                  If [ Get(FoundCount) = $SetCount ]     <-----  Problem shows up here!
                      Replace Field Contents [ Scratch::Position; Replace with serial numbers: ... ]
                      Set Variable [ $LastError; Value:Get(LastError) ]
                      If [ $LastError ≠ 0 ]
                          Set Variable [ $ExitResult; Value:"Error: 1" ]
                      End If
                  End If
              End If
      End If

      --------------------------------------------------

      I put a pointer on the line where the problem crops up.  The $SetCount being passed in is correct (e.g. 1 record).  The issue is at the 'Get(FoundCount)' step:  it reports 2 records found, when there is only 1 record...there's only 1 record in the entire table!  Is this a problem were the local cache of the DB still has the record present?  My spare window I mentioned, showing the Child table, shows one record in the entire table; this other window updated immediately when the portal row record was deleted.

       

      Thanks,

      Justin

        • 1. Re: Delete portal row not working - get(FoundCount) reports 1 too many records
          philmodjunk

          Can you describe your layout design where you have this set up? There's a log going on here that makes no sense to me.

          don't see why the script isn't just:

          Delete Portal Row
          Commit Records
          IF [ Not IsEmpty ( PortalTable::fkField ) ]
               Go To Related Records
               Replace Field Contents
               Go to Layout [original layout]
          End If

          • 2. Re: Delete portal row not working - get(FoundCount) reports 1 too many records

            I made a mock-up file showing the problem.

            https://www.dropbox.com/s/q19s5l4q55u9nu9/Portal_Row_Record_Delete_issue.fmp12.zip?dl=0

            I tried the GTRR you mentioned and it turns out to return only 2 records (which is correct).  I don't recall why at the moment, but there was a good reason that I actually stuck with the Find routine instead, though.

            I think that the 'commit record' step you mentioned would probably fix the portal issue that I am seeing, but I haven't tested that yet.

            Either way, there's the question of whether or not this is a bug in FileMaker.  If you step through the script and stop after it does the find, you will see that it returns 3 records, even if there are only 2 records showing in a separate window showing that layout.  The status bar for that window says "3/2" for the found set.  Here's a screen capture.  Again, I bet that the commit records step would fix it.  But what is going on behind the scenes?  Why is this record showing up?  I don't recall having to do a commit step if I go to a record in script while on a layout and delete it there.

            I also have tested this file in 13 and it behaves the same way.  So it appears that this isn't new.

             

            --   Justin

            • 3. Re: Delete portal row not working - get(FoundCount) reports 1 too many records

              Oh yeah, now I recall why I didn't want to use GTRR:  it makes the script less modular.  GTRR would require that I be starting on a particular layout and a particular record.  Using the Find version, this script can run from anywhere called by anything that passes in the ID of the parent record that needs it's children reserialized.

              To solve the issue for now, I have just put in a 'commit record' after deleting the portal row - as you suggested, Phil.

              --  Justin

              • 4. Re: Delete portal row not working - get(FoundCount) reports 1 too many records
                philmodjunk

                That wasn't really the point of my question. My observation was that there a bunch of other steps in your script that have no obvious reason for being there. But glad you got this working. wink

                • 5. Re: Delete portal row not working - get(FoundCount) reports 1 too many records

                  What steps would those be?  They all make sense to me.  :)

                  For the reserialization, every step has a purpose and is required (or at least purposeful, e.g. the error checking).  Here's the bit that I think we are discussing:

                          Set Variable [ $SetID; Value: GetValue( Get(ScriptParameter) ; 1 ) ]
                          Set Variable [ $SetCount; Value: GetValue( Get(ScriptParameter) ; 2 ) ]
                          If [ IsEmpty ( $SetID ) ]
                          ...
                          Else
                              New Window [ Style: Document; ... ]
                              Go to Layout [ “Scratch_Child UTIL” (Scratch_Child) ]
                              Enter Find Mode [ ]
                              Set Field [ Scratch_Child::ID_fk; $SetID ]
                              Set Error Capture [ On ]
                              Perform Find [ ]
                              If [ Get(FoundCount) = $SetCount ]     <-----  Problem shows up here!
                                  Replace Field Contents [ Scratch::Position; Replace with serial numbers: ... ]
                                  Set Variable [ $LastError; Value:Get(LastError) ]
                                  If [ $LastError ≠ 0 ]
                                      Set Variable [ $ExitResult; Value:"Error: 1" ]
                                  End If
                              End If
                          End If

                   

                  The 'Go To Object' steps after the initial row deletion are about UI cleanup - hiding some stuff, essentially.  It was perhaps confusing to have included it here, as it isn't germain to the problem at hand.  And if you replace the Find routine with the GTRR that pretty much makes it the same as what you proposed.  The 'commit' was missing from mine, and then I have some error checking thrown in, but otherwise...

                  In my second example, the demo file I made up, I did include some extra nicities for being able to choose which method to use; made it easier to run the script using one or the other method.  Is that what you are referring to?

                   

                  --  Justin

                   

                  • 6. Re: Delete portal row not working - get(FoundCount) reports 1 too many records
                    philmodjunk

                    The go to object steps were the main ones. Don't see what they were doing even now in terms of "UI clean up" as those steps only put the focus on a layout object but comparing the found count to a variable also seemed a bit odd as well.

                    • 7. Re: Delete portal row not working - get(FoundCount) reports 1 too many records

                      The go to objects cause a slide-panel to change to a blank panel, hiding a portion of the UI.

                      The variable and get(foundcount) check is just some added protection or error checking.  Just want to ensure that something weird hasn't happened and that all records are returned - that it has the proper count.

                      --  Justin

                      • 8. Re: Delete portal row not working - get(FoundCount) reports 1 too many records

                        I believe that I understand what was going on now, and that it wasn't a bug in FileMaker.

                        The behavior is part of FileMaker's Transactional underpinnings.  I was doing some research for another piece of this project and came upon a full explanation from Todd Geist (starting here).

                        The summary is that since I was using related records (i.e. portals) and relationship settings to achieve these functions, the related records (INCLUDING deletions) were NOT being saved to the server until a 'commit' occurred (either explicit or implicit).  So that is why I had the wrong count and why your recommended 'commit records' script step solved it.

                        Hopefully this will aid others who run into this unexpectedly.

                        --  Justin