Copying Fields Between Related Tables?

[rblloyd]

Is there a simple way to tell file maker to copy selected fields in one table to selected fields of a second table? How should the relationship be set up between the two tables? I want the fields to be identical for both tables for each record created! Thanks in advance for your help!

[Søren Dyhr]

I want the fields to be identical for both tables for each record created!

It's not what usually pass as a normalized structure, each atomic data should reside in one single location. If data is displayed in various places is it due to relational tunneling, and certainly not copying!

So you wish to have the data identical in both tables is pure and simple a fault in you otherwise expirienced reasoning, I'm afraid!

You might note that I keep this reply in a general manner, since you reveal next to nothing about you problem at hand - hence the pretty teoretical reply ...the cardinal newbe sin here is to play a bluffers game letting the repliers guess unnecessary - I can only say you're approaching your problem wrongly!

The only exception I can think of here is, lookups that only are needed when say a price might be fluctuating from time to time you issue an invoice to the same customer. Lookups works as snapshots of the price at the moment the invoice was issued.

But with a bit of luck in my guesswork, could your question be along the lines of reasoning in this thread:

http://www.fmforums.com/forum/showtopic.php?tid/185710/post/245633/hl//

--sd

[rblloyd]

Well I thought that a simple yes or no answer would have sufficed, but maybe that response isn't feasible.. It might be that I just wasn't able to read through your broken English... Thanks for the reply but I was looking for a reply that was more educational and less user experience bias.

[Lee Smith]

How about.

It is a bad idea to have redundancy in your data. There is usually a better way to accomplish what you are thinking you need to do, without using the copy and paste it. Of course, we do not know what it is you are wanting to do, or what the data is that you are wanting to have multiple copies of either. Perhaps if you explained more about this, we can make a suggestion that will actually make sense to you.

Keep in mind, that a relationship can provide you with ways of seeing the data in other TOs and Files, without actually moving it. After all, that is one of the benefits of a relational database, i.e. to eliminate all of the redundant data.

BTW, using the copy and paste method for moving data is way down on my list of a means to move data.

HTH

Lee

[rblloyd]

Hey thanks for the help. Here is my problem... I currently am running an invoice management system that allows invoices to be logged and reported. In my particular business its not uncommon to log 20 invoices per week to the same customer. Now during the momement of purchase a copy of the invoice is printed for the customer, but he will be sent weekly statements that sum up the weeks worth of purchases so he can pay them all at once. My thoughts were to copy the date, cust name, and product price and description to a second table so that I can pull the information to put into my statement. I would like to fit all of the purchases on one page line at a time! Could you sugest a better way to do this? The only reason that I wanted to copy the data to a different table is because if I use a relationship to pull the product disc and price information, it may pull the newest price (as the prices and descriptions change constantly) Thanks in advance for your help!

[mr_vodka]

Soren's posts can come off as cryptic at times, but his points are valid. What is the purpose here of having the same data in two tables? Proper relational structure exists to avoid duplicate data as such.

Take a look at these past threads...

Duplicate Record Thread 1

Duplicate Record Thread 2

Please report back with the answer of what you are trying to achieve.

[mr_vodka]

The only reason that I wanted to copy the data to a different table is because if I use a relationship to pull the product disc and price information, it may pull the newest price (as the prices and descriptions change constantly) Thanks in advance for your help!

If you use a proper lineitems table with lookup fields, then you will keep the pricing at that point in time. IOW, the lineitems table should have a price, qty, productID, description, etc in it that uses lookups to the products table. Then when a invoice is created, it will lookup the current price at THAT time. Once you change the data in the products table and then a new invoice is created, it will pull the new pricing and data at the NEW time. So it will always have a history of the price at the particular time the invoice was being created. Otherwise if the prices were just references and changed each time you viewed at an invoice, there wouldnt be much use would there?

You should be able to find lots of examples on these forums with a proper invoice/lineitem/product structure.

[comment]

A simple way to produce a weekly (or any other range) statement:

1. Find the relevant invoices by customer and by date range;

2. Go to Related Record from InvoiceLineItems, matching entire found set, using a statement layout.

The statement layout should be a list, showing records from InvoiceLineItems, with sub-summaries by InvoiceID. If you need to do this for multiple customers, sub-summarize by customer first, then by invoice, with a page break between customers.

No duplication of data is required.