2 Replies Latest reply on May 18, 2009 12:35 PM by philmodjunk

    Setting up a quote system for trucking

    ohioguy48

      Title

      Setting up a quote system for trucking

      Post

      Hello all,

       

      ...I'm trying to set up a quote system within Filemaker 10.  I've downloaded a trial version of the software to test its features and the ease of use.  Here is my problem.

       

      I'm trying to set up a quote system which would allow me to pick from a "table" the values set forth and dropped into a row on the form. Here is an example.

       

       

      (Accesesorials)  -  Private Escorts

       

      Ohio

       

       

      Line Item                  Description           Quanity                 Cost         Extented cost.    Total

      Line Item                  Description           Quanity                 Cost         Extented cost.    Total

      (as many as 10 rows or more from above).

       

       

      Indiana

       

      Line Item                  Description           Quanity                 Cost         Extented cost.    Total

      Line Item                  Description           Quanity                 Cost         Extented cost.    Total

      (as many as 10 rows or more from above).

       

       

      What would be the best way to create this type of database?  My thinking is to create a "table" for each state due to the different charges/rates that each state bills. (or would it be better to have one table) listing (all the states) and there related chargers for each item.

       

      example table:

       

      Description               Cost

       

      Oh-Permits               $40

      Oh-Pole Car             $100

       

      OR

       

      Oh-Permits               $40

      Oh-Pole Car             $100

      IN-Permits                $50$

      IN-Pole Car              $90

       

      When you click on a line item, you would enter the value for each item.  When you get to the the "description" field, a box would pop up listing either one "table" for each state,  and then enter the item from the state/list,  OR one "table"  with all the states included in the search.  I've played with filemaker somewhat to which it allows you to enter part of a phrase, and the  list is shortened based upon your search. After entering a value (on one line) I'd want filemaker to insert another blank line for input. etc.

       

      I think you get  the picture.

       

      In the near future there would be more catagories (accessorials) added to the database, upon having other values listed for those as well.

       

      Your help would be greatly appreciated.  Im somewhat familiar with an older version of FM, Version 5.  Please advise.

       

         thanks,

       

              Jeff

        • 1. Re: Setting up a quote system for trucking
          deltatango
            

          I would make only one table for all the states. Then, make a table for the charges. Add a charge type field in the charges table.

           

          Then you could use a global field to change the type of charges you are looking at. 

           

          Link the state to it's charges AND the global field in the state table to the charge type in the charges field.

           

           

          STATE TABLE                    CHARGES TABLE

          STATE ID   --------------------  STATE ID       

          GLOBAL_TYPE ----------------- CHARGE TYPE

          • 2. Re: Setting up a quote system for trucking
            philmodjunk
              

            Your second approac  will probably work better for you. The relationships you'd need to get the one table, one state approach to work for you could get really, really complicated.

             

            There are a number of threads in this forum that deal with quotes and invoicing. You might want to clicked the "advanced" search link above and search on the keywords "invoice" and "quotes" for useful discussions on this topic.

             

            Here's a very general outline of how to do what you want:

             

            You'll want at least three tables: Invoices, LineItems and PriceList.

             

            In Invoices, you'd collect the information for a single invoice or quote such as date, location, total cost, customer name, etc.

            In LineItems, you'd collect the individual items that add up to a single invoice or quote. You would relate LineItems to Invoices by a serial number field in the Invoices table. The LineItems table will also look up pricing information from the price list.

            In your Pricelist table, you store the name and cost of each item/service you might sell to a customer. You want this information to be in a separate table where it can be copied to the LineItems table so that you can update prices without changing the prices in any existing Quotes or Invoices.

             

            In the case of your example, I'd probably add a field to the PriceList table for identifying the State. In any case, you can build the type of Quote you describe by building a layout that uses your LineItems table as its reference table, but still includes fields, such as a Quote/Invoice number, date, customer name and such, from the Invoices table.