4 Replies Latest reply on Nov 1, 2010 9:10 PM by TheodoreBieryla

    Relationship Issues Using 3 separate files? NEED HELP!!!



      Relationship Issues Using 3 separate files? NEED HELP!!!


      We have four databases in FM6. 

      Sales Reps



      Customer Claims

      Each dealer is assigned a Sales Rep. 

      When a dealer sells a contract to a customer the contract is entered into the "Customer" Database We need to link the Selling Dealer to that customer contract in order to keep track of that "dealers" sales and claims pertaining that individual  customer(s).

      As of now, when a contract comes in, I have the dealer phone number as a value list from the dealer database and when selected, it populates the "Selling Dealership" field in the customer database. 

      When a customer has a claim, as of now, all data is manually entered into the claims database and when typos occur the data analysis is always wrong. The claim in the "Customer Claims" DB needs to be linked to both the customer that  has the contract and linked back to the selling dealer of that customers contract as well.

      Ideally, when a claim comes in, we'd like to search the customers vin, pull that record up, click a button on the customer layout in the customer db and  take us to a new record request in the claims database and have it automatically populate the customer information fields from the customer database as well as the selling dealership information from the dealer database into a Customer Claim Record.     

      Any thoughts / suggestions on this matter is going to be GREATLY appreciated! 

      If someone can help out with this, I will post exactly what fields are in the claims database that needs to be populated and their corresponding databases. 

      Thank You

        • 1. Re: Relationship Issues Using 3 separate files? NEED HELP!!!

          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]

          New Record/Request
          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.

          • 2. Re: Relationship Issues Using 3 separate files? NEED HELP!!!

            I am using FM6, therefore its one table to one database.... is the procedure going to be the same? 

            • 3. Re: Relationship Issues Using 3 separate files? NEED HELP!!!

              Everything I posted was from the context of FileMaker 6. The details would differ a bit with more recent releases of FileMaker.

              • 4. Re: Relationship Issues Using 3 separate files? NEED HELP!!!

                Hi Taliena,

                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.