5 Replies Latest reply on Dec 12, 2013 10:12 AM by Stephen Huston

    Copying data from one table to the other

    maurice2307

      Goodmorning fellow Filemakers,

       

      I would like to ask your assistance with the following.

      There are 2 tables: offers and orders. I make plenty of offers and on average 1 out of 10 becomes an order.

      When an order arrives, I need to manually type in the data in the new order record which are already in the existing offers record - not very efficient.

       

      I’d like to be able to when in the offers layout: click a button which activates a script (or a series of scripts) which will:

       

      a) create a new record in the orders table;

      b) copy the information from the fields of the offer;

      c) insert that copied data into the designated fields of the newly created orders record;

      d) take me to the orders layout so that I can complete the orders record, print, etc.

       

      Needless to say there are many if’s and but’s and calculations to perform, but if you could just show me the principle of how this functions, than I can probably sort out the rest myself.

       

      Thanks in advance.

      Kind regards,

      Maurice

        • 1. Re: Copying data from one table to the other
          Mike_Mitchell

          Maurice -

           

          There are a few ways you can accomplish this task:

           

          1) You can use a Lookup or Auto-enter calculation to copy the data from Offers to Orders via a key field. You can put an OfferID field in your Orders table that has a relationship back to the Offers table. Then, you can just enter the OfferID on a new Orders record and have FileMaker copy all the data over. (A value list having all the OfferID values and some other value so you can recognize which is which will help you identify what OfferID to put in.)

           

          2) You can set up an automated import from Offers to Orders. Do a Find to locate the specific Offer record, then import it to Orders. You can set up the necessary field mapping in the Import dialog in the Import Records script step as needed to get the right data into the right fields.

           

          3) You can create a couple of global fields in a convenient layout (either Offers or Orders; likely Offers, based on your description) and then set up relationships between those global fields and the appropriate key fields in Offers and Orders. This allows you to do an operation known as "processing in place", which I first saw in a presentation by Ray Cologon. Basically, you set the global field that points to Offers equal to the key field of the record you want to copy from. You allow creation of related records on the relationship that points to Orders, and then use a series of Set Field script steps to create the new record over there. Example:

           

               Set Field [ OrdersGlobal::CustomerID ; OffersGlobal::CustomerID ]

           

          This technique takes advantage of an undocumented (but very handy) FileMaker behavior: When you have a global field on the "parent" side of a relationship that meets these conditions:

           

          • The relationship allows record creation
          • The relationship points to a key field that automatically creates a value (like a serial ID or a UUID)
          • The global field is currently empty

           

          and you use Set Field to set a different field on the "child" side, then the global field is automatically set to the newly-created ID value. (Try it; it's cool.)   

           

          In any case, once you've successfully created the new record, your script can use the Go to Layout script step to navigate to the correct layout.

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Copying data from one table to the other
            maurice2307

            Thank you Mike. Gonna try this ;-))

            • 3. Re: Copying data from one table to the other
              Stephen Huston

              Assuming that the offers and orders tables carry much of the same info, or that most offers fields are included in the order table,  merge those tables so all fields are in a single table, just not all used when the offer is started.

               

              Then there are a couple of  simple methods to use some records as Orders while the Offers all remain:

              1. use a radio button to mark the record type, defaulting to offer on creation, changing it to order when changed. Filter any portals showing orders to show only the order type.
              2. Use a button or script to convert the offer to an order by having a second foreign-key field, same as for the client-to-offers table, but used for the client-to-orders relationship. Set that value to the same client ID at the time the record is changed to an order, and you have a separate relationship with just the order records out of that table.
              1 of 1 people found this helpful
              • 4. Re: Copying data from one table to the other
                maurice2307

                Thank you Stephen.

                 

                Very early in development of my solution I was thinking about creating just one table with some boolean making the difference between offers and orders.

                I guess in hindsight that would have been the better solution. Of course you are correct in assuming that most of the records in both tables hold the same info. I could merge the two tables and my practical issue would be gone.

                 

                However, I was curious about the mechanism of how to exactly 'take' a record from one table and insert the data of this record into another table, together with a switch to the appropriate layout where I could complete the record. A kind of complicated 'copy and paste' action if you like. Mike already made some usefull suggestions which I am going to try out. But I would be grateful for your suggestions too.

                 

                Thank you again.

                • 5. Re: Copying data from one table to the other
                  Stephen Huston

                  Personally, if keeping separate tables, I would probably go for the lookup option which Mike mentioned. You need only set the primary key of the Offer record to a script variable, go to the layout for the Order records, create a new order, and set the foreign-key of the new order to the variable value.

                   

                  The fields in the Order table need to be set to auto-enter based on a lookup setting between the Orders and Offers, so a relation has to be set between them. When the new Order record is created and its foreign key set to link it to the Offer, the data will fill itself in.