Multiple Files

[Tomo1]

Hi,

This is a difficult one to explain. We have a seperate customer database for each customer. We can't put them all in one DB as there would be 100 thousand records and would take forvever to sort. How could we change from having 50 DBs to just 1 or 10 etc???

Thanks,

James.

[dshafer]

Well, I'm no FMPro guru (yet) but my immediate reactions are:

1. If you truly have a database of 100,000 records and are convinced FileMaker Pro isn't up to the task, try a different database program! I don't _know_ if FileMaker Pro would break down under that load (though I suspect it might), but keeping a separate database table or each customer is madness and will not only ultimately break, it will never allow you to treat your customers as a single group or entity.

2. Barring that, the only viable solution I could come up with is to divide your customers into alphabetical categories. So you'd have one DB, e.g., for customers whose last names start with A-C, another for D-E, another for F-I, etc. That would let you keep the overall group sorted, and you'd know where to look for a given customer. But, at the end of the day, it sounds like you may have an application for which FileMaker just isn't the right tool. Great as it is (and I love it), it isn't the right solution for every need.

[Steven H. Blackwell]

Well the sort speed will depend on a number of factors including hardware and software considerations, presence of related fields, etc. But, in the abstract, 100L records is not a large number. I have a file with 5 million FMP records.

Old Advance Man

[Tomo1]

I agree, 100,000 records isn't very much. There are no relationships, just a flat file for each customer. I have a cover DB with buttons linked to each DB. One DB had around 50,000 records and when sorted on a slow Mac it took forvever. The trouble is, we need to cut the number of DB from around 50 to ?.

Thanks,

James.

[Anatoli]

Pardon me, but that is strangest design I've ever heard of. Separate file for each customer? What is the benefit of this? For that you do not need FileMaker.

[Kurt Knippel]

Jim,

It is pretty plain to see that you problem is not number of records but database design. If you feel that sorting 100,000 is too slow, that shows that you should NOT be sorting 100,000 records.

I cannot believe that you really need to sort that many records anyhow.

Sometimes one cannot see the forest for the trees, perhaps you need someone else to look at the solution (just one of the files if they are all the same) and re-evaluate how things were done. They may be able to see some ways to optimize that you just cannot, being as close as you are.

I always like to do this with my solutions and they all turn out much better for it.

[Tomo1]

Thank you all for your input,

but I think were all getting our wires crossed. (Well me anyway). There are about 50 customers, each with there own DB and each with a unique customer number ie 22, 44 etc. Each DB has about 2000 records. When the DB is opened it sorts the job numbers inside the customer from 1-?. Each DB has a different number of records.

[BruceJ]

Ditto the above, it's your design...

In essence you'll need two files:

1. Customers

2. Jobs

Relate the Customer ID to a Job ID.

Sounds like you may be trying to sort inside of portals. This is a forced sort everytime you go to a layout. You may want to re-think why you'd need 50,00 records viewed in order. Maybe your work functions don't demand this and you may be able to use a "find" type of arrangment instead. This may "find" say only a particular sales person's jobs, or maybe jobs between or after a certain date...

You can make a "find" type of system without using "find command". Just make a field(s) and use these as the related field to the protal. you enter the info into the filed and magincally, the related records will appear in the protal. (well, it's a little more complicated).

If you wanted to email me a copy of the empty databases, I'd play around with it and send it back.