3 Replies Latest reply on Oct 7, 2010 2:58 AM by LaRetta_1

    Need help building a form to add to multiple tables.

    MarcTew

      Title

      Need help building a form to add to multiple tables.

      Post

      Hi, I am fairly new to Filemaker and I am already slightly out of my depth with it.

      I work in a Motor Dealership and I need a database whereby I can track vehicles on site, vehicles on order, and the customers those vehicles are then sold to. I was planning to have a table for vehicles, a table for customer details, and a table to attach order numbers and etc to. I have copied and pasted the fields at the bottom.

      The issue I have, is staff need to be able to open fm, and input data into two tables from one form. I was going to use a field called Customer Serial, to link tables. The customer serial is surname then DOB.

      Could someone advise me on how I setup the form to create identical records in two tables at once?

      Thankyou in advance. (Im on Filemaker 10)

      Customer Details

      Title                (Mr, Miss, Mrs, Ms, Other)
      First Name
      Surname

      Email Address

      Address Line 1
      Address Line 2
      City
      Postcode

      Tel Number Home            (Numbers 10-11 Digits)
      Tel Number Work            (Numbers 10-11 Digits)
      Tel Number Mobile        (Numbers 10-11 Digits)

      Preferred Contact Method     (Email, Home, Work, Mobile)

      Date Of Birth            (Date Box)

      Customer Serial            (Generated from Surname + DOB)



      Vehicle Details

      Registration Number        (Validated to maximum 7 digits)
      Manufacturer            (Citroen, Hyundai, Overwrite Possible)
      Model
      Trim Level
      Colour
      Fuel Type
      Engine Size

      Chassis Number
      Engine Number
      Stock Number
      Vehicle Key Number

      Date of Registration
      Mileage

      Stock Type            (New, Used, Pre-Reg, Demo)
      Vehicle Type            (Car, Van)

      Cleaned for Forecourt?        (Yes, No, N.A Sold Order)
      Workshop Status (New Vehicle)    (Unprepared, PDI'd)
      Workshop Status (Used Vehicle)    (Unprepared, Sales Safety Checked, UVPDI Completed)

      Internal Key Number        (3 Digits)


      Order Details

      Customer Serial            (Generated from Surname + DOB)

      Date of Customer Order
      Date of Admin Order
      Manufacturer Order Number

      Estimated Date of Arrival
      Vehicle Location        (Factory, Shipping, Dock, Transporter, Blackwater)

      Factory Options
      Accessories to be Fitted

        • 1. Re: Need help building a form to add to multiple tables.
          LaRetta_1

          "I was going to use a field called Customer Serial, to link tables. The customer serial is surname then DOB."

          Start off right by having each table contain a unique ID such as CustomerID, OrderID,  etc.  This should be an auto-enter, FM-generated serial number field starting with 1 (see auto-enter options on the field).  This is important because, if you create the customer and add related information (and you join by this field of surname and DOB) and you later find out the DOB was typed wrong and change it, you will break all your relationships.  Or maybe someone gets married and changes their last name, then what?  Serials which bind relationships should aways be meaningless serials which can never be changed.

          Can you ever sell a vehicle to more than one customer?  How do you handle Owner of Record and leinholders?  I ask because your 'Owner' might be a many side to one vehicle and this would be important consideration in your design.  Also, one customer can own more than one vehicle AND could buy more than one on an order, which is why a LineItems table is important for one Sales Order - to allow multiple purchaes.

          What FM version are you using?

          Here is perfect example of structure you need (products being your vehicle inventory.  Once we know your FM version and whether there can ever be more than one Owner for a vehicle, we can address data-entry itself.  Data-entry should never drive structure; data-entry is determined after structure is solidified.

          http://fmforums.com/forum/showpost.php?post/309136/

          • 2. Re: Need help building a form to add to multiple tables.
            MarcTew

            Hi LaRetta, thankyou for the speedy response.

            I see what you mean by the ID's, but how would I ensure that they match? As I may be entering a vehicle which we have not yet sold, and it is generated an ID of say 190? But then when I want to attach that to a cstomer when the car is sold, the auto generated customer ID is unlikely to be 190.

            I am currently using FM Pro 10.

            The issue of a customer owning more than one vehicle isnt really that much of a problem as the data is only to be held while the order is present, then it will be cleansed a set time after the car is handed over to the customer.

            Thankyou.

            • 3. Re: Need help building a form to add to multiple tables.
              LaRetta_1

              "as the data is only to be held while the order is present, then it will be cleansed a set time after the car is handed over to the customer."

              Oh really?  Why not keep the data so you can search - which is the purpose of a database?  Anyway, to answer your other question, your CustomerID would never match your VehicleID.  Your vehicle would have it's own set of IDs.  If you review the link I provided, it shows how your Customer would have their own ID and the vehicle would have it's own ID.  The tables are then linked as:

              Customers::CustomerID = Orders::CustomerID
              Orders::OrderID = LineItems::OrderID ... and so forth.  Look in the graph in the demo I provided (File > Manage > Databases and Relationships tab.

              Your Order would have it's own ID and also a field for the CustomerID.  When you insert the CustomerID in the order (customer selected via pop-up), it inserts the CustomerID in the Order's CustomerID field.  Then all fields from the Customer table (that you have placed directly on your order form) will appear.  When you select a vehicle in the LineItem (ProductID), it inserts the VehicleID in the LineItem table ProductID (VehicleID) field.

              What I have provided is standard, correct database design and there are many reasons it all is important to be structured this way.  Can you upgrade to vs. 11?