one customer many databases in fmpro 6.x

[curious99]

Hi from Canada!

We have a Ford car and truck franchise combined with a body shop operation as well as a rent-a-car operation. We often sell to the same person more than once and we service the person's car more than once.

How do I create a reference, in FM pro 6 ( my server is on linux and to my knowledge, FM 7x does not offer linux yet), that will allow me to change the adress of the customer (when he moves to another adress) and affect all the previous transaction as well as to quickly find his address when servicing his car in our departments.

Right now, we write up the adresses everytime and do not update the old transactions which creates problems as we do mailings and we get a lot of returned mail plus many other issues arising from this design.

Got any ideas to get me going in the right direction on this topic? ;)

[RalphL]

You need to add a customers table (File). Use lookups to add names, addresses & phone number to invoices, work orders, etc.

Keep the customer file up to date and use it for mailings.

[Ender]

Oh, Canada...

Well, you haven't said much about your structure, so it's hard to say where you're having trouble. But there are structures that can avoid this type of problem.

In general, an Invoice (transaction?) file would be a child of a Customer file. The address is maintained in the Customer file, and lookups pull the address into new Invoice records. If the address changes, the change is recorded in the Customer file, and the old invoices are left unchanged. This is important, because you may need to go back to see where something was actually sent.

Mailing lists would be generated from within the Customer file, so old addresses would not be a problem.

As far as Work Orders go, I'd have those either in a separate file related to Customer and Invoice, or kept within the Invoice file itself and distinguished with a Type field.

I'd imagine there would also be a Car file, where you'd have a record for each car ever held in inventory. This would make it easier to pull info into the Invoice and the Work Order later.

[curious99]

Thanks for your answers.

I follow what you are saying and it makes perfect sense.

Ender, when you write "and lookups pull the address", how would the lookup be set up? For example, how would one of my employees know if that customer has done business with us in the past? Is there a way in filemaker to set up what I see in other softwares that you start writing a name in file "rent-a-car" for example and it brings the closest names that match and as you write up more letters, it reduces the possible matches?

[Ender]

A lookup is defined in the field definition, under the Auto Enter options. You must first have a relationship set up between the two files, then you can use the relationship to grab the contents of a field in the parent file to populate the field in the child's new record.

Your questions about how to make this invoice creation work in the IU are valid. There are different approaches developers take to creating the required parent records when working in the child file.

My suggestion, is to have your users navigate to the Customer file whenever they need to add a transaction. The process would start by searching for the Customer. If no records are found, then the database prompts them to create a new Customer. When they are on the desired Customer record, you would have a portal to view the past Invoices / Work Orders, and a button to add a new one. The button jumps to the Invoice file and creates a new Invoice for that Customer.

Your thought of using "type ahead" functionality to link a Customer to a new Invoice has a couple problems. First, type aheads do not work very smoothly in FileMaker without a plug-in (natively, you have to exit the field for the relationship to build.) But more importantly, this type ahead would only show Customers that already exist in the Customer file, so dealing with new Customers would be confusing. Users might be tempted to simply type the name of the new customer in the Invoice, not realizing that this does not actualy create a related Customer record. To work correctly, users would have to exit the invoice, add the Customer, return to the Invoice and reselect the Customer. There are other scriptable work-arounds here, but I still recommend starting at the parent record and working your way down.

Have you ever been asked for your name and phone number when paying cash for some doo-dad somewhere (do you even have doo-dads up there in Canada?) These questions are asked because the clerks are filling out that Customer record before creating the Invoice. Ever thought it was kind of weird? Well, it probably is for simple doo-dad purchases. I'd look for another method in these cases, something that won't require the clerk to put in junk-data on half the purchases.

But in your case, I think it makes sense to start at the Customer file, since your customers won't have a problem giving out their names and phone numbers, and it's important for future contact with them.