Actually, you have one Database with 4 files, each with one table of related data. In each file, you should define an ID field that auto-enters a serial number. Since you have existing records already in use, you'll need to user replace field contents to update this field for existing records using this tool's serial numbers option.
You'll also need matching number fields defined in order to set up relationships between your files(tables). And you will need to enter the correct ID numbers into these fields to link your existing records to the correct record in the other file.
Dealers---<Sales Reps----<Customers----< CustomerClaims ( ---< means one to many )
Dealers::DealerID = Sales Reps::DealerID
Sales Reps::SalesRepID = Customers::CustomerID
Customer::CustomerID = CustomerClaims::CustomerID
You would open define relationships in each file and define whichever of the above relationships you need for that specific file.
With the above customer to CustomerClaims relationship defined in the customer file, you can place a portal to CustomerClaims that will list all the claims records for that specific customer contract record. You can start a new claims records simply by entering data in the bottom blank row of this portal.
You could also use a pair of scripts to do this without having a portal.
Set Field [gCustomerID ; CustomerID] //gCustomerID is a global field
Perform Script [//select the following script in CustomerClaims]
Set Field [CustomerID ; Customer::gCustomerID]
For this second script to work, you must have a relationship defined in CustomerClaims that links it to Customers.
You don't want to use names or phone numbers to link the files in relationships because this data may change and if you edit the field to record the new name or number, it breaks the link to the related records in the other file.
I am using FM6, therefore its one table to one database.... is the procedure going to be the same?
Everything I posted was from the context of FileMaker 6. The details would differ a bit with more recent releases of FileMaker.
Glad to see your using a Filemaker System. Filemaker will do EVERYTHING needed.
Your “SalesRep” file should be more of a “Users” file, which will allow you to create a login system , every user will have a Unique Employee Number, this works in 2 parts , it allow you to track actions, ie ( creating/modifying records etc.) and used for reporting. The sales reps employee number is assigned to the dealer. This number is used to track and pay commissions etc, in the users record you may want to have a territories field as well, lets call New Jersey (NJ1). As a rep may be responsible for more then 1 territory
** Keep in mind , sales reps leave or get fired so you need a easy way to reassign those dealers a new rep without losing the sales of contracts from the old rep.
Dealers phone numbers are good for lookups (temporarily) but assign the Dealer a Unique account number, I would suggest base it off the FIPS Code, for example the State code for NJ is 34, Hudson County is 017, and use the same technique for serialization of your authorization number for a serial number, for example the first dealer in from NJ in Hudson county their DIN is 34017001, next one 34017002….
You track sales not only by dealer, but state ( assuming you will be in multiple states ) and counties as well, this is later used because some counties have different TAX rates.
**Dealers phone numbers change, or they go out of business and a new dealer may get the same number the old dealer had, so don’t use the phone as a primary key
Dealers will need some files associated with it as well to track: phone call, mailings, etc. plus a dealer may have different primary contacts, the owner may not necessarily be the primary contact it may be the F&I manager so correspondence is addressed accordingly.
The Customer file should contain only data related to the customer, No Contract Information. That’s related
So You need a Contract & a ContractLineItem file, You will also need a ISP_Item file to store the default contract information ( months / mileage / optional coverage’s / Price etc ) this is used to calculate to total cost of coverage for that contract. In the LineItemFile.
Customers can have more then one contract, on more then one vehicle, from different dealers, they can extend / renew coverage’s not counted into the dealers sales or charged against their claims ratio etc. This is why Customers JUST holds customers data.
Your Contract File is your primary data file holding the “Keys” - Customer #, DIN, RepID, VIN etc. but is NOT directly accessed. You don’t need the Customers /Dealers address, phone etc stored here its related data
VIN’s tracks the car but can’t be unique ( math equation will tell you if the vin is valid ) since people sell there cars and you may get another warranty on it with a different customer and/or dealer. So that is only part of a PK the Contract # is the second part. And is use to check a claim ( if any ) that may exist from a different customer and applies the authorized claim ( if any ) to the correct customer/dealers account.
Claims needs a ClaimsLineItem file as well
Remember to account for Repair Centers ( since your generating your Auth# to them and not the customer directly), A Dealer may have a in-house repair center and cover the cost of a authorized repair for its customer, so that Dealer is now also a Vendor. So the same holds true for accepting payment (Contract orInvoiceFile ) you need a PO File, Line Item to pay against
The above is just a snippet of what you’re going to need.
I know I designed a Filemaker system exactly for this industry and it worked perfectly in a multi-user / multi-location environment right up until Nov 23rd, 07 ( My replacement didn’t know what he was doing )
Save yourself some time and purchase the latest FM and don’t design a new system in FM6, your structure will be completely different, separating Data and UI, with FM6 your starting backwards.
A system for your industry can get pretty complex depending on what your needs are but Filemaker can do EVERYTHING.
Here are 2 links to a partial FM Relationship Graph, making everything work smoothly together can get challenging.