MrMunchkin

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

Discussion created by MrMunchkin on Feb 23, 2017
Latest reply on Feb 23, 2017 by 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

Outcomes