4 Replies Latest reply on Feb 23, 2017 6:13 AM by MrMunchkin

    Copy & Paste Data From Fields In One Table To Multiple Fields In Another

    MrMunchkin

      I have created an invoice producing database. For simplicity, it consists of the following:

       

      INVOICE TABLE:

      Date

      Customer

      Product

      Price

      *Customer & Product Combined Name

       

      I import data from other sources sent from filmmaker go into this invoice file. Everything is working great at this point.

       

      However, no price data is imported as prices can vary by customer and by product for that customer (such as special discount codes on products). At this point we enter the pricing data in manually. I have created a find/replace script which enters pricing for some of our most popular products. This works well but there are still other products and the same product with discount codes added to their name that I have to do manually. I invoice monthly and this can be a pain as there is always the potential to not enter these types of prices correctly. At the beginning of every month, I do a search of all the records for the previous month and enter prices.

       

      To solve this, I was wondering if I could create a master Price List Table where all the prices for all variations of products organised by customer and product name are set and then create a button on the Invoices Table which runs a script or calculation to copy all the prices on the master Price List Table to all the corresponding price fields on the Invoice Table. This is where I am having trouble as I don't know enough about scripting or calculations to make this happen.

       

      I have created a Price List Table as follows:

       

      PRICE LIST TABLE

      Customer

      Product

      Price

      *Customer & Product Combined Name

       

      I have created a relationship between the two tables using *Customer & Product Combined Name. The master Price List may consist of 100 records, however, every month the Invoice Table may consist of 2000 records. How can I create button with a script / calculation to copy and paste the price data from the master price list price fields into the corresponding price fields on the invoice table?

       

      Prices can change monthly so every month I will need to check our master price list prices and make the necessary changes. When I do this, I don't want the new prices to affect the prices already entered for previous months / years on the Invoices Table.

       

      Does anyone out there know how to do this?

       

      I did find an article but it seemed outdated and to be honest, I didnt fully understand it and couldn't get it to work.

       

      How to copy data to one field from multiple portal records | FileMaker

       

      Thanks

        • 1. Re: Copy & Paste Data From Fields In One Table To Multiple Fields In Another
          siplus

          Usually items in an invoice receive their current price via a lookup from a master price list, thus ensuring that

           

          - you get the current price at the time of creating the invoice line item record

          - previous invoices line items are not affected by updates done to the master price list.

          • 2. Re: Copy & Paste Data From Fields In One Table To Multiple Fields In Another
            MrMunchkin

            Hi Siplus,

             

            Thanks for your reply. I wondered about that. Before making our database I looked at a few stock solutions, they all had line items and when I tried to modify them to fit our solution, I just couldn't get things to import properly. I tried to make my own solution which is working very well, essentially it is just a big bucket that imports sales data (not including price) from multiple filmmaker go sources and then organises things through month sorts and customer sorts etc. As no pricing data exists in the filmmaker go imports I need to add that afterwards on the desktop. From the way I have designed it, does this mean that there is no solution to copy and paste multiple instances of pricing data from one related table into another?

            • 3. Re: Copy & Paste Data From Fields In One Table To Multiple Fields In Another
              siplus

              Whenever you import data in a table having already data in it, you end up with a found set limited to the imported data.

               

              All you need at this point is to do a relookup on ItemID, which will fetch the current price from the master price list.

               

              It's just one script line... make it 2 if you finish with a commit records/requests step. Add a freeze window, go to lineitems layout and a go to original layout at the end.

              • 4. Re: Copy & Paste Data From Fields In One Table To Multiple Fields In Another
                MrMunchkin

                Hi Siplus,

                 

                Thanks for the info and advice. I have never used the relookup feature and not sure how to do it or how it works. It sounds like it could be the thing though from your comments. I'll have a play around and see if I can get something working. Fingers crosses. Thanks for your help. If it works, it has really helped a lot.