5 Replies Latest reply on Jul 9, 2017 8:28 PM by dale_allyn

    Problem Script: Generating Unwanted New Records from Loop


      FileMaker 16 Server


      I have not been able to replicate this issue on local FMPA instance, but it comes up on the served version.


      This is a "Report Submission" form for a laboratory which takes-in submissions from clients. Each submission has submission line items (one submission, one or many line items). On the submission layout is a portal for inputting the submission line items. The portal is where the problem occurs.


      On Submit (submit, not just commit to save) some basic checks for empty fields are done, then a loop to do some things, with a counter. The counter is used rather than "exit after last" because records can be created via the portal, so there is always one more blank record opportunity in the portal.


      I'm testing against "0" for the variable countdown, but wonder if my syntax can be improved, because at some (seemingly) random times, 0 (zero), is not being respected by the loop steps. Will using "<1" be more stable? What else am I missing? The starting variable is set via a summary field, but locally has no issues.


      Here's the loop section of the script:


      Set Variable [ $itemCount; Value:Sub_Line_Items::s_ItemsCount ]

      Go to Portal Row

      [ First ]


      #Clear unnecessary fields by item type.

      If [ Sub_Line_Items::ItemType "Pearl-like: Strung" ] Clear [ Sub_Line_Items::prl_strung_detail ]

      [ Select ]

      Clear [ Sub_Line_Items::prl_strung_rows ] [ Select ]

      End If
      [ Sub_Line_Items::resubmission = 1 and IsEmpty ( Sub_Line_Items::resubPrevReportID ) ]

      Show Custom Dialog [ Title: "Previous Report ID Missing"; Message: "You have marked an item as a Re-submissioin, but the Previous Report ID is missing. "; Default Button: “OK”, Commit: “No”; Button 2: “Add Later”, Commit: “No” ]

      If [ Get (LastMessageChoice) = 1 ]
      Go to Field [ Sub_Line_Items::resubPrevReportID ] Exit Script [ ]

      End If End If

      If [ IsEmpty ( Sub_Line_Items::reportNumber_ID ) or IsEmpty ( Sub_Line_Items::barcode ) ] Perform Script [ “Commit Submission Item” ]

      End If

      Set Variable [ $itemCount; Value:$itemCount - 1 ]

      Exit Loop If [ $itemCount = 0 ]

      Go to Portal Row

      [ Next ]

      End Loop


      Stepping through is locally it performs as needed, but in production on the server we're getting runaway record creation.

      Thank you for any input.


        • 1. Re: Problem Script: Generating Unwanted New Records from Loop

          To add before asked


          The subscript has been reduced to the following:


          Set Field [ Sub_Line_Items::location; "CSR" ]

          Commit Records/Requests


          It's a simple sub-script, but I stripped it from the original with a couple other minor items. The simplification has had no effect.


          • 2. Re: Problem Script: Generating Unwanted New Records from Loop

            Are you trying to use Perrform Script on Server to do this?


            If so, that would be very problematic for this script.

            • 3. Re: Problem Script: Generating Unwanted New Records from Loop

              Hi, Phil,


              Thanks for your reply.


              No, this is not a PSoS situation.

              • 4. Re: Problem Script: Generating Unwanted New Records from Loop

                Then I do not see why you cannot use FileMaker Advanced and the script debugger plus data viewer to determine why/how your script is failing.


                I can comment that I try to avoid scripts that loop through portals in order to modify or analyze sets of related records. This approach is very "brittle", modest changes to the layout can break such a script and there are alternative approaches that avoid being as brittle as this approach is. But that doesn't tell you or me why the above script would work as a stand alone solution and not as a server hosted solution.


                Any global fields involved here? (They behave differently in a hosted solution as compared to a stand alone copy.)


                Other observations:

                Looping through the rows of a portal is "brittle" due to the fact that:


                Go to portal row can't specify the portal in which you want to "Go to" a row. It is safest to give the portal an object name and precede Go To Portal row with Go to object to be sure that you are looping through the rows of the correct portal. Even if you have only one portal on your layout, that might not be the case in the future. Even with Go to Object, each change in portal row changes focus and this can cause issues either now or due to a future layout change since focus changes are what trip most script triggers.


                Clear is also "brittle". Set Field [Table::Field ; "" ] can sometimes avoid some issues caused by the change in focus that is a side effect of using clear.


                Note that you are clearing fields, not deleting records from the portal table. That may be what you want, but if you were assuming that clear was deleting a record from the portal's table, please be advised that clearing the fields leaves you with a record in the table with empty fields, it does not delete the record.

                • 5. Re: Problem Script: Generating Unwanted New Records from Loop

                  Thank you philmodjunk for your thoughtful reply (as always).


                  I have stepped through the script many times with FMPA and Data viewer and have not been able to replicate the issue; not locally or on a dev installation on the server. Likewise, I've downloaded the database from the server and attempted debugging. It always runs fine for me, but not for the one user who has triggered it three times.


                  No globals.


                  Yes, go to object prior to go to portal row is a good practice and I overlooked that here. Only one portal on the layout, but a good practice.


                  I agree about "clear" vs "Set field". I almost always use "Set field [Table::Field ; ""]", but for some reason chose "Clear" this time (it's an older script). I've even commented to others in these fora that I've found "Set Field" to be more reliable. Thanks for the reminder.


                  The field clear is mostly a cleanup and not really required. It's there in case a user changes ItemType to avoid unrelated data in a record. It wouldn't be shown or acted upon anyway for other Item Types. However, I can clean it out with a script trigger on the ItemType selection instead. Script triggers can replace the other elements of the loop as well. I was just trying to give the user a single dialog to address instead of by line-item (similar to a web app), but I think it's best I remove the loop and handle the elements via script triggers, etc.


                  Thanks for the input. There's a lot going on in this solution and this has been an annoying distraction. Posting here has allowed me to step back and reevaluate the need for the loop.


                  This version is a pared-back version of the loop, as the reason the loop was initially used is that Barcode Creator is used for generating barcodes for each line item. The full loop inserts the barcode (as without it some barcode images were not being set) and since we're looping already, the cleanup and dialog was added. I had removed the Barcode Creator subscripts as it was looking like something there was causing the runaway record creations (not Barcode Creator, but how I had implemented it possibly). After paring it back to this very simple loop I was surprised that it came up again.


                  This loop (together with the Barcode Creator steps/subscripts) was mostly a UX thing. I can address the elements from a different angle and avoid a loop.


                  Thank you for your feedback and input.