4 Replies Latest reply on Apr 26, 2010 9:51 AM by philmodjunk

    Copying related records from one portal to another

    crcampbell

      Title

      Copying related records from one portal to another

      Post

      I've been using Filemaker for over a year, but I'm still new to scripting and am having trouble getting related records from one portal on a layout to copy to another portal on the same layout. Here are the details:

       

      The main layout has several tabs for customer contact info, vendor info, quoted items and ordered items. On the items tab is a portal (portal A for this discussion) for entering multiple items in a related table (relation is the quote # in the main table to a quote # field in the quoted items table) with fields for part number, description, price each, quantity, and a calculation field for item subtotal. Also, I currently have a simple pop-up menu field with Yes/No to designate if an item is being ordered. What I am trying to do is to copy the quoted items in portal A to a similar portal (same fields minus the pop-up menu in a separate ordered items table) on the Ordered items tab through a scripted button or script trigger on the pop-up menu. Another factor is that this database is going to end up being published thru IWP so importing/exporting doesn't look to be available. Is there a way to set relationships or what scripting could I try to get the records copied over? I have not had much luck getting the loop command to run through each portal row for copy and paste purposes so any help there would be appreciated as well. I have been able to get the related records sorted, just not copied over to the ordered items table.

       

      Any help is greatly appreciated. Thanks! 

        • 1. Re: Copying related records from one portal to another
          philmodjunk
            

          The first change that I'd suggest is keeping your quoted and ordered items in the same table. (Let's call this table "Items".) You can use your Ordered(yes/no) field to separate ordered and quoted items. To show just the ordered items in a portal you could set up a compound relationship:

           

          Define a calculation field that returns text (I'm assuming that your Ordered field stores either "Yes" or "No" text). Define the calculation to return the literal string: "Yes".

           

          Create a relationship that uses this new field (call it OrderedKey):

          (I'm calling your main table "MainTable" since I don't see its name in your post.)

           

          MainTable::Quote#--=--Items::Quote# AND MainTable:: OrderedKey--=--Items:: Ordered

           

          Use the the Table Occurrence in this relationship for your ordered items portal and clicking the Ordered pop-up in the quotes portal should automatically make it appear in Ordered Items portal.

           

          That make sense?

           

          PS. I see you're having trouble looping through the rows of a portal. In the case of a layout with multiple portals, you have to tell filemaker which portal you want to loop through, but there's no obvious way to do it. Here's how: First use Go To Field to specify a field that exists in the portal you want to loop through. Then, Go To Portal Row [] can be used to move the focus to a given row in that portal. Of course, if you follow my advice above, you shouldn't need to do this for your current problem.

           

           

          • 2. Re: Copying related records from one portal to another
            crcampbell
              

            The relationship change looks like that did the trick. Thanks a lot!

             

            • 3. Re: Copying related records from one portal to another
              caz3000

              Hi, I was having a similar problem with multiple portals in a layout.  Filemaker defaulted to the first portal and I wanted to paste something in the second portal.  I tried your suggestion:

               

              First use Go To Field to specify a field that exists in the portal you want to loop through. Then, Go To Portal Row [] can be used to move the focus to a given row in that portal. Of course, if you follow my advice above, you shouldn't need to do this for your current problem.

               

              but it didn't work.  After a bit more trial and error I figured out that if I named the portal I wanted to paste into as an object in the inspector, I could use 'Go to Object' command in the script to go to that portal.  Then when I told it to Go To Portal Row [last], it went to the last row in the named portal (rather than the first one in the layout).

               

              I'm just posting this in case anyone else has encountered the same problem.

              • 4. Re: Copying related records from one portal to another
                philmodjunk

                Yeah, I learned that trick myself after this thread came and went.