8 Replies Latest reply on Jan 27, 2009 7:21 AM by AJ

    Duplicating a record with multiple portals

    AJ

      Title

      Duplicating a record with multiple portals

      Post

      Say I have an order that I'm building for a customer in a table called ORDER. In the ORDER table, I have an auto-enter field "Order Number", fields from my CUSTOMER table (name, address, customer ID, etc), and 2 portals.

       

      The ORDER table has a relationship to OrderLineItemProducts and with OrderLineItemServices, which are related to the PRODUCTS table and SERVICES table (respectively). The Products and the Services table each hold information for different products and services that we offer.

       

      Say that once I build an order that has several records in each of those two portals (as well as the cust. info, etc.), I need to DUPLICATE THE ENTIRE ORDER, with a new order number.

           *EXAMPLE*

      I have a returning customer and the customer wants to place the same order again. I need to create a new reciept with a new order number with all of the same Products and Services that he ordered last time.  Duplicating the record only copies the Customer Information to a new order. How do I copy the related records from the portals on the old order into the portals on the new order, without having to input them all over?

      I know that I have to run some sort of script, but I am at a loss of what commands to enter.

       

      Thanks!

        • 1. Re: Duplicating a record with multiple portals
          TSGal

          AJ:

           

          Thank you for your post.

           

          I'm sure several other users will offer their version of how to do this, so let me start.

           

          Yes, you will need to run a script.  I'll explain the script steps as we go along...

           

          I'm assuming the Order Number field is the "key" field that relates your ORDER table to the PRODUCTS and SERVICES tables.  If that is a case, when you enter a new order, there is a new value generated for Order Number.  Therefore, you need to store the old Order Number value to a variable prior to "duplicating" the order.  The script step to do this is:

           

          Set Variable [ $oldordernum; ORDER:: Order Number ]

           

          Now, in your original order, you may have records in OrderLineItemProducts and OrderLineItemServices, but you may have records in only one of these tables.  The following two script steps store the number of records in both of these tables to variables.  I'll explain the reasoning later.

           

          Set Variable [ $lineprod; Count ( OrderLineItemProducts:: ProductID )

           

          Set Variable [ $lineserv; Count ( OrderLineItemServices::ServiceID ) ]

           

          (I'm using ProductID and ServiceID as possible field names from those respective tables.  The point is to select a field from those related tables that contain data for ever record.  Usually, some kind of ID field will always have that kind of information.  Use the field that applies to you.)

           

          Now, we can duplicate the record.  The script step is:

           

          Duplicate Record/Request

           

          As you said, the Customer information is available, but you will notice a new Order Number.  We need to store that information for later use...

           

          Set Variable [ $newordernum; ORDER:: Order Number ]

           

          Now, let's find the old PRODUCTS and SERVICES records, if they exist.  First, the two variables we created earlier now come into use.  If the Count() was zero, then there is no reason to find the records.  If there are records, then we switch to the layout that holds the OrderLineItemProducts records, enter Find mode, put the old Order Number into the appropriate field (OrderID?) and find.  The script steps are:

           

          If [ $lineprod > "0" ]

             Go to Layout [ <layout that holds all OrderLineItemProducts records> ]

             Enter Find Mode []

             Set Field [ OrderLineItemProducts:: OrderID; $oldordernum ]

             Perform Find []

           

          -----

           

          At this point, we have found the old records.  Now, we want to import those records into the same table and assign the new Order Number to those newly imported records.  Continuing on with the script....

           

             Import Records [ Restore; No dialog ] 

           

          (Note: You will need to specify all the parts to this import completely.  That is, select the current file, select the OrderLineItemProducts table, and match up the appropriate fields.  You don't need to include the OrderID, since the OrderID is going to be replaced with the new OrderID.)  Continuing on...

           

             Replace Field Contents [ No dialog ; OrderLineItemProducts:: OrderID; $newordernum ]

          End If

           

          -----

           

          The same thing needs to occur with the SERVICES records (if they exist).  This would be nearly identical to above:

           

          If [ $lineserv > "0" ]

             Go to Layout [ <layout that holds all OrderLineItemServices records> ]

             Enter Find Mode []

             Set Field [ OrderLineItemServices:: OrderID; $oldordernum ]

             Perform Find []

             Import Records [ Restore; No dialog ] 

             Replace Field Contents [ No dialog ; OrderLineItemServices:: OrderID; $newordernum ]

          End If

           

          Remember, the Import Records script step needs you to select the current file, select the OrderLineItemServices table and match up the appropriate fields.

           

          That should do it.

           

          There are other ways to do this and reduce the number of script steps (Set Error Capture), but this may be one of the easier methods to understand for someone who hasn't used script steps previously.

           

          Let me know if you need clarification for any of the above steps.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: Duplicating a record with multiple portals
            AJ
              

            Thank you for your quick reply.

             

            I am currently working on mapping out my script, and I am running into a snag at the step that requires me to specify the "Import" of the found records.  Here is the exact step:

             

            If [ $lineserv > "0" ]

               Go to Layout [ <layout that holds all OrderLineItemServices records> ]

               Enter Find Mode []

               Set Field [ OrderLineItemServices::OrderID; $oldordernum ]

               Perform Find []

               Import Records [ Restore; No dialog ] 

               Replace Field Contents [ No dialog ; OrderLineItemServices::OrderID; $newordernum ]

            End If 

             

            Could you elaborate a little more on this step? I am just having a hard time understanding "Specifying Data Source" and "Specify Import Order"

             

            Thank you!

            • 3. Re: Duplicating a record with multiple portals
              TSGal

              AJ:

               

              The "Import Records" script step has a few options.

               

              "Perform without dialog" -  When you pull down the File menu and select Import Records, it brings up a dialog box.  You don't want this to occur during a script, so this option suppresses that dialog box.

               

              "Specify data source".  There are a few options here that I forgot to mention.  Most users import from a text file or another FileMaker Pro file, so the default is set to "File".  When you put a check mark in this option, a dialog box appears prompting you for the file you want to import.  In the upper right corner, click on "Add File...", and select the current file.  This will be added to the list.  Click OK.

               

              Finally, you need to set up the import order, so put a check mark in "Specify import order..."   A new dialog box appears entitled "Import Field Mapping".  Along the right side are the fields you want to import into.  On the left side are fields.  At the top of the left side, there is a "Source:" pop-up menu that displays the different tables.  Be sure to select the appropriate table "OrderLineItemServices" or "OrderLineItemProducts".  Once you match up the fields, click OK and you should be set.

               

              Please don't hesitate contacting me if you need further clarification.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Duplicating a record with multiple portals
                AJ
                  

                Alrighty... forgive me if I am overlooking something obvious...

                 

                In the "Import Field Mapping" the "source" should be, say, "OrderLineItemProducts", but what should the Target be?

                My choices are:

                • Customer Info
                • Orders
                • OrderLineItemProducts
                • OrderLineItemServices
                • Products
                • Services
                • New Table ("OrderLineItemProducts 2")

                 

                • 5. Re: Duplicating a record with multiple portals
                  TSGal

                  AJ:

                   

                  The point is to duplicate the records from "OrderLineItemProducts" and "OrderLineItemServices" with the original ID.  That is why we import from the same table.  Once the old records are imported, we have a new set of records with the old ID and we replace with the new ID.  Therefore, if the source and target should be the same.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Duplicating a record with multiple portals
                    AJ
                      

                    Filemaker prevents me from having the Source and Target fields being the same table. When I match the appropriate fields and click "OK", a dialog box appears, saying, "A table cannot be imported into itself."

                     

                    Please tell me if i may be doing something wrong...

                     

                    Thanks for your persistence on the topic :)

                    • 7. Re: Duplicating a record with multiple portals
                      TSGal

                      AJ:

                       

                      Oops.  No.  You are doing nothing wrong.  My fault.  I could have sworn I did this previously.

                       

                      I'll have to modify the script again....  Sorry about that.

                       

                      If [ $lineserv > "0" ]

                         Go to Layout [ <layout that holds all OrderLineItemServices records> ]

                         Enter Find Mode []

                         Set Field [ OrderLineItemsServices:: OrderID; $oldodernum ]

                         Perform Find []

                       

                         Export Records [ No dialog ; "temp.tab" ]

                       

                         Import Records [ Restore; No dialog ]

                         Replace Field Contents [ No dialog ; OrderLineItemServices:: OrderID; $newordernum ]

                      End If

                       

                      --------

                       

                      I added the step to export the records to a tab-delimited text file, "temp.tab".  You will need to modify the "Import Records" script step so that you are now importing from "temp.tab" as the source.  Keep the "OrderLineItemServices" table as the target.

                       

                      I purposefully named this "temp.tab" because it is a temporary holding file.  Therefore, you can use the same file name for the "OrderLineItemProducts" table, too.

                       

                      Again, I apologize for the confusion.

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: Duplicating a record with multiple portals
                        AJ
                          

                        Alright :) It's working, with a minor adjustment...

                         

                        The first step of the script has me "saving" the old order number in the operation:

                         

                        <Set Variable [oldordernumber; Value:Order::ordernumber.pk]

                         

                        but for some reason FileMaker wasn't allowing me to retrieve the value "$oldordernumber" to perform my find in the steps to follow:

                         

                           Enter Find Mode []

                           Set Field [ OrderLineItemsServices:: OrderID; $oldodernum ]

                           Perform Find []

                         

                        Everything was working EXCEPT for this step. So, I figured, what the heck, why not just copy and paste? (oh if every problem in life could be solved by this...)

                         

                        So, Here is my script: (And it works!)

                         

                        Go to Field [Orders::OrderID.pk

                        Copy [Select; OrderID.pk]

                        Set Variable [$lineprod; Value:Count ( ProductLineItem::ProductID.fk)]

                        Set Variable [$lineserv; Value:Count ( ServicesLineItem::ServicesID.fk)]

                        Duplicate Record/Request

                        Set Variable [$newordernumber; Value:Order::OrderID.pk ]

                        If [ $lineprod > "0" ]

                           Go to Layout [ "ProductLineItem" ]

                           Enter Find Mode []

                           Paste [Select; ProductLineItem::OrderID.fk

                           Perform Find []

                           Export Records [ No dialog ; "temp.tab" ]

                           Import Records [ Restore; No dialog ]

                           Replace Field Contents [ No dialog ; OrderLineItemProducts:: OrderID; $newordernumber ]

                        End If

                        If [ $lineserv> "0" ]

                           Go to Layout [ "ServicesLineItem" ]

                           Enter Find Mode []

                           Paste [Select; ServicesLineItem::OrderID.fk

                           Perform Find []

                           Export Records [ No dialog ; "temp.tab" ]

                           Import Records [ Restore; No dialog ]

                           Replace Field Contents [ No dialog ; OrderLineItemServices:: OrderID; $newordernumber ]

                        End If

                        Go to Layout [ "Orders" ]

                         

                         

                        Thank you for all of your help-I hope that this info can help anyone else needing to do the same thing :)

                         

                        Once again, THANKS!!