Too many duplicate records

[stubanz]

I have a huge excel spreadsheet that I am trying to import into filemaker pro - it is a couple thousand rows high and about thirty rows across. It is basically a tracking of orders. The problem is that when someone puts in a new order, they put in all contact info and order info on the same line. Basically, company name, customer number, order number, contact name, contact address, etc... is all on one line, which FMP converts into a new record for every order. My thinking - to condense things - was that I would make a relational database with company name, number, contact info and a separate database with information on each separate order that can be recalled by common customer numbers. The only problem is that all info is in the same record. I have separated the company name/number/contact info into a separate database, but now I have (for example) 40 entries for each customer, which I want to delete. Any way I can get it so I only have one entry with customer name and number by making a script to delete the extraneous records? I'm pretty new to this, so any help would be greatly appreciated. Thanks,

- Stubanz

[Ted S]

Stubanz,

You can indeed relate multiple orders to a single contact but you may want to consider the possible negative implications of doing so. Contact records are fairly static but not completely static. What would happen if your contact goes to work for a competitor? Would your history of orders suddenly appear as if they were sold to the wrong company? I myself wouldn't want this.

For things like orders, I always lookup (copy) the information from the current contact record but store it in the order itself to ensure an accurate history.

So you MAY NOT want to eliminate all of this apparent duplication.

[MoonShadow]

I am in total agreement, Ted. In fact, even though redundant, I write almost all information directly to an Invoice, ie, address, product prices, etc. In this way, if a Contact address changes, the address at the time of the invoice is recorded as part of history.

One could have an address history file by date and relate to that; but it's important that an invoice become static data.

You can use a selfjoin to identify each unique entry then find unique, omit records and delete the rest. Search here on forums for Find Unique.

[BobWeaver]

I agree with Ted that you should keep the historical data in the sales table, but you can certainly remove the duplicate entries in your customers table. See the link below about removing duplicate records.

http://fmforums.com/forum/showtopic.php?tid/159484

Edited October 30, 2008 by Guest
updated URL

[stubanz]

Hmmm...true. That may not be too big of a problem because we are mainly selling to schools, and I'm not sure that too many would go from one school to another.

I do want, however, to make a report or display in some way which school has ordered from us, and when their maintenance agreement expires, and similar info that wouldn't change, at least until they order an extension or whatever. This is also info that would only take one line to report, this way we can get a summary of expiration dates without looking at the same information for (40) lines.

I have been tinkering in the past hour, and what it seems I want is basically the index, but with the accompanying info in other columns. Any suggestions?

[stubanz]

Bob, thanks for the link...I'll check it out.

[stubanz]

Hmmm....it appears that the attachment you posted in the other forum was created using FMP7, but I'm still using 5. Any way to convert this? Will the same technique be useful in FMP5? Again, thanks for any help.

- Stubanz

[BobWeaver]

If you look in the FM 5 user manual, the example given in the scripting chapter is essentially the same find and mark duplicates script as the one given in that link.