1 2 Previous Next 22 Replies Latest reply on Jun 23, 2016 1:32 AM by mrpete

    Cannot copy portal records - frustrated!

    mrpete

      I have a seemingly simple need, however the solution continues to elude me and I'm hoping the community can help!

       

      Simply put, I have a database containing Sales Orders and another containing Shipping Orders. I am using FM Pro 11.

       

      I need to COPY portal data from a Products table that is displaying via a portal in a Sales Orders layout to a Shipping Order table without touching the original data.  The product line item data record contains:

       

      -Quantity of Product Ordered (3 fields are used, but 2 are blank depending upon whether the line item is ordered via BAG, SACK or BULK)

      -Description of Each Product

      -Weight of Each Product

      -Tons Ordered of Each Product

       

      A single product record contains these fields (and a few others):

      Sacks, Bulk, Bags, Size, Lbs, Tons

       

      I need to copy these fields to a Shipment Order table without touching the original Sales Order data because:

       

      a) multiple shipments can be made for each order and must be tracked as to their status (open or closed)

      b) each line item can be made up of multiple lots that need to be tracked (so each line item needs to link to potentially multiple lot numbers in another table)  I created a concatenated field to do this using order number, line number and lot number that "should" work...

       

      I have tried several scripts that I created plus some that I located online but none seem to loop and copy properly.  I can't use a portal to the same Products table because changes in the portal change the original order data.

       

      Essentially, I want to be able to enter in the order number into a portal to the Shipping Orders table and have the product line data from the related Products table copy over to it by way of a button/script.  The items are related by both order number and line number.

       

      Since it may take multiple shipments to fulfill an order, by entering the order number in the portal, all original order data is brought over and a running total is kept in the Shipping Order database as to what has already shipped and whether or not the order is complete.  I can either duplicate the shipping order and modify it since it will be unique but related to the original, or go back to the original order and create a new shipment.

       

      I have the running totals working, but cannot get more than one line item to come over.  I'm pretty sure the problem is either in the relationship of the 2 tables or the copy script, but I have been unable to get it to work.  I have an order field and a line number field, and created a concatenated field to link over to the shipment table.

       

      Confused yet?!

       

      I can provide more specifics on the table layouts if need be; my copy script is attached.

       

      Thanks in advance for any insights...

       

      -Pete

        • 1. Re: Cannot copy portal records - frustrated!
          siplus

          some problems:

           

          1) using copy / paste is a bad idea, always. To begin with, you destroy the user's clipboard. But on a lengthy script your user might also go into another app while your script is running and do a copy of something - if you're out of luck ... well... you can immagine what will happen - the clipboard is a system-wide global shared by all your active apps.

           

          2) if you have more than one portal on the same layout, you have to give a name to each portal and use go to object (portalName) and THEN use go to portal row.

           

           

          Ideally some ExecuteSql would help, but you're seemingly stuck to FM 11. Or not ? Is some upgrading possible ?

          1 of 1 people found this helpful
          • 2. Re: Cannot copy portal records - frustrated!
            karina

            Hi,

             

            Where you use copy, use set variable and where you use paste, use set field and fill it with the variable.

            1 of 1 people found this helpful
            • 3. Re: Cannot copy portal records - frustrated!
              BruceRobertson

              This is a moderately advanced scripting problem, and you will need to learn to use variables and and set field operations, rather than copy/paste. It is definitely feasible but you may want to consider paying somebody to do it for you.

              I don't see any value or necessity to use ExecuteSQL.

              • 4. Re: Cannot copy portal records - frustrated!
                Markus Schneider

                It's now that part of the learning curve that becomes flat...

                 

                As Siplus mentioned: Use ExecuteSQL to get Your wanted data into a list ($/$$ variable), go to the target layout/table and loop through that list, fill the fields in the target table. Use GetValue and other list-functions

                 

                Copy will sooner or later lead to problems, either when a copy-command fails for whatever reason or when users loose their copied data

                 

                We had to rebuild 2 big foreign solutions lately due to clipboard problems with VMWare/Citrix...

                • 5. Re: Cannot copy portal records - frustrated!
                  mrpete

                  I appreciate the responses...I am "stuck" with FM11 at the moment, but I've been trying the set variable / set paste functionality and am having limited success.

                   

                  I'll keep plugging at it - I know I am close...

                   

                  -Pete

                  • 6. Re: Cannot copy portal records - frustrated!
                    mrpete

                    OK, I've named the portals on the layout and go to them and the next portal row, set the variables and set the fields in a loop and commit while counting the records to copy ($linecount)...to me it looks like it should work, but I'm not having any success.

                     

                    When I execute the script, I get 9 pop-up windows telling me "This actions cannot be performed because this field is not modifiable."  However, I see no reason why it is throwing this error...the 9 occurrences correspond to the [Set Field] commands, but why?

                     

                    What am I missing?

                    modified copy script.jpg

                    • 7. Re: Cannot copy portal records - frustrated!
                      bigtom

                      You usually get this error if it is a calculated filed.

                      1 of 1 people found this helpful
                      • 8. Re: Cannot copy portal records - frustrated!
                        User26869

                        Hi Pete

                         

                        It's a context problem. Make sure the PackSlipsBOLs table occurence is available on your layout. Is your Portal PSBOLs based on it?

                         

                        Cheers

                         

                        Chloe

                        • 9. Re: Cannot copy portal records - frustrated!
                          mrpete

                          For some reason, my changes to the fields to make them non-calculated didn't take; I changed them back (thanks bigtom) and the errors disappeared.

                           

                          The portal to the tables are available on the layout - a source portal named "Products" that is the source of the data I need, and the destination portal "PSBOLs".  The PackSlipsBOLs table is the main table that the portal table occurence is based on.

                           

                          The script runs, but does nothing - no data is copied to the destination.

                          • 10. Re: Cannot copy portal records - frustrated!
                            User26869

                            Thanks bigtom fro actually giving an helpful answer - I mixed up error messages...

                             

                            Pete, you can set a "Show custom dialog"  script step that show the variable as a dialog box after you set the them, to verify they are not empty.

                            Also, do you have empty records to fill in your portal? I think i there isn't and the relationship doesn't allow the creation of records in the child table, it won't create new ones.

                             

                            Hope this helps a bit!

                             

                            Chloe

                            1 of 1 people found this helpful
                            • 11. Re: Cannot copy portal records - frustrated!
                              bigtom

                              Do you have the record creation via relationship checkbox set in the graph?

                               

                              I think you may want to be using go to portal row by calculation to get data and go to portal row Last to set data.

                              1 of 1 people found this helpful
                              • 12. Re: Cannot copy portal records - frustrated!
                                mrpete

                                OK, the custom dialog box is a neat trick; it IS showing me the expected data for each row of source data in my portal.

                                 

                                However, the data is NOT getting copied to the destination portal row.  I set a custom dialog for that as well, and it IS showing up, but is not putting it there.  I do have the record creation relationship set, so that isn't the problem.

                                 

                                The script goes to each row in the destination portal, but nothing is copied there.

                                 

                                I am so close I can taste it...arrgghh

                                • 13. Re: Cannot copy portal records - frustrated!
                                  siplus

                                  have you thought about going to related records (the ones shown in the first portal), then opening a new window on a (empty) layout belonging to the second portal's table and just doing an import records ?

                                  • 14. Re: Cannot copy portal records - frustrated!
                                    mrpete

                                    I'm shying away from exporting/importing because the end users need it as simple as possible, and the script method assigned to a button is the easiest way to accomplish what I'm trying to do.

                                     

                                    This set of data is going to require an entire other tracking mechanism for lot numbers since each ordered line item can be made up of multiple lots across multiple shipments. However, since that data won't change once it's entered via a portal, it shouldn't be too difficult to implement.

                                     

                                    I need to have a separate copy of the ordered items to track partial shipments and orders that are shipped to multiple locations.  It's a fairly complicated order fulfillment system, which is why it is so frustrating that i am having such difficulty simply copying records.  Unfortunately, this Filemaker solution has been touched by multiple programmers over the past 15 years, and is based off an original Basic program.  The design wasn't done correctly from Day 1, and I simply cannot redo the entire system - I just need to make it work with what I have to work with.

                                     

                                    I don't know why it's so hard to simply copy records across 2 tables!

                                    1 2 Previous Next