4 Replies Latest reply on Sep 10, 2016 8:15 PM by RobFredericks

    Problem with script to add portal row records

    RobFredericks

      I have a database that tracks information on our portfolio of rental properties.  The database is hosted on a filemaker 15 server and I work off of filemaker 15 advanced. 


      The Problem:  I have a main development table named "Development" and a related welfare exemption table named "Tbl_WelfareExemption".  I have a  tabbed layout based on the Development table with one tab that contains portal rows to the related welfare exemption table.  I have a button that performs a script to automatically add new welfare records to the portal rows for each development that requires a welfare exemption.  My script works fine when I first run it, but when I subsequently run the script, it seems to overwrite or delete the portal row records that I previously added through the script rather than just adding new portal records.

       

      Here is a link to a short movie which details the issue:

       

      Issue with script to add Portal Records - RobFredericks's library

       

      Also, here is the script that I made to perform this addition to the portal records:

       

       

      Screen Shot 2016-09-10 at 1.28.00 PM.png

       

      Any help anyone could provide would be greatly appreciated!

       

      Rob

        • 1. Re: Problem with script to add portal row records
          philmodjunk

          Your script raises many questions:

           

          Why do you perform a find in step 9? What criteria do you use?

           

          Why do you need a loop?

           

          Do you have the "create" option specified for your portal's relationship?

           

          Does your portal show "Tbl_WelfareExemptionTracking" when you check "show related records from" in Portal Setup?

           

          I generally recommend against having a script interact with a portal as a way to add related records like this--simple layout changes in the future can screw up your script's ability to function. There are other ways to add the portal record such as MagicKey or changing layouts to a layout based on your portal's table, creating the new record there, and then returning that are not quite as vulnerable.

           

          But all in all, my money is on the possibility that you have not enabled "created" in your relationship as that would cause your script to overwrite existing records.

          • 2. Re: Problem with script to add portal row records
            RobFredericks

            Hi Phil,

             

            Thank you for replying to my issue.  The answers to your questions are as follows.  If there is a better way to handle this, I would love some direction.   Did you see my movie to see what I am trying to accomplish? http://www.screencast.com/t/zebf5kbbT   I am even willing to give login rights to my database and pay for some help on it.

             

            Why do you perform a find in step 9? What criteria do you use?

             

            I performa a find in step 9, as I only want to add the portal records to the developments that are required to have a welfare exemption. 

            The criteria I use is: Development::RequiredWelfareExemption: ["Yes"]

             

             

            Why do you need a loop?


            I need a loop as I want to run through each of the records found from step 9 of the script to add the new portal row records.

             

            Do you have the "create" option specified for your portal's relationship?

            Yes - see screenshot:

             

            Screen Shot 2016-09-10 at 4.23.31 PM.png

             

            Does your portal show "Tbl_WelfareExemptionTracking" when you check "show related records from" in Portal Setup?

             

            Yes - see screenshot

             

            Screen Shot 2016-09-10 at 4.24.18 PM.png

             

             

             

             

             

             

             

             

            • 3. Re: Problem with script to add portal row records
              philmodjunk

              The video is enlightening and illustrates why I prefer not to use portal interaction (go to portal row) as a way to add related records. Go to portal row is never changing the focus to the last portal row of the correct portal. It is interacting with the other portal on your layout. (That's why the tab control changes to show the other portal.) Thus, it keeps overwriting data in an existing portal row instead of adding a new one in the bottom "add data" row of the portal.

               

              To fix that part of your script, you need to:

              Enter layout mode

              Select the portal, not the tab the portal is on and give it an object name

              Then update your script to change the go to object name step to go to this new object name.

               

              Clear--another place where you have a problem, and go to portal row are what I characterize as "inherently brittle" scripts steps. They easily break if very ordinary layout changes are made to the layout and there are alternatives that do not have this vulnerability. Your clear steps generate an error and do not clear anything because the specified field is not actually present on the layout (at least not via the tableOccurrence::FieldName specified in your script). You can use set field to clear your fields by setting them to empty quotes "".

               

              Go to Portal row can't specify which portal to interact with and thus we use Go to Object to put the focus on the correct portal but it's the portal that should be given the specified object name, not your tab panel. I still consider go to portal row a potential source of trouble as it is fairly easy to accidentally change an object name and not know that you've done it or nor realize that you've just caused a script to fail by doing so.

               

              I recommend that you consider using the MagicKey method to add your related records. This is a technique that you can easily find via a web search.

               

              Also, if your data is being entered into global fields, you don't need to copy them to variables. You can just refer to the global fields instead of the variables.

              • 4. Re: Problem with script to add portal row records
                RobFredericks

                Thank you Phil!  I changed the script per your suggestions and it works fine.  Good learning moment for me.  I will look into the MagicKey method as well.

                 

                Rob