2 Replies Latest reply on Jan 3, 2012 1:57 PM by MichaelNeck

    Multiple enteries to one field?

    MichaelNeck

      Title

      Multiple enteries to one field?

      Post

       my company provides up to 6 different services to each customer.  The service price is different for each customer.  What is best way to list each service of a specific customer and the associated fee?  I'm thinking it would look something like this, but not sure how to set it up so I can extract it for invoicing:

      Company:                               Service(s):                          Fee:

      ABC Industries                          typeI                                 $xx.xx/hr

                                                     typeII                               $xx.xx/hr

                                                     typeIII                              $xx.xx/visit

        • 1. Re: Multiple enteries to one field?
          philmodjunk

          The standard invoicing set up is this:

          Customers---<Invoices----<lineItems>----ProductsOrServices

          Customers::__pk_CustomerID = Invoices::_fk_CustomerID
          Invoices::__pk_InvoiceID = LineItems::_fk_InvoiceID
          ProductsOrServices::__pk_ProductID = LineItems:_fk_ProductID

          with pricing info stored in ProductsOrServices. In your case, this table would be named Services.

          If every service to every customer is unique, this set up still works--just give each service a unique ID. A conditional format in the LIneItems portal on your invoices layout can be set up to limit services to only those approprate for the current customer.

          If the same service is provided to different customers and billed at different rates. (Even if this is only a theoretical possibility at this point...), Then you should add a separate pricing table.

          LineItems>----ServiceRates

          LineItems::_fk_ComapnyID = ServiceRates::_fk_CompanyID AND
          LineItems::_fk_ServiceID = ServiceRates::__pk_ServiceID

          Then the unit price field in line items will look up a specified rate from Service Rates.

          • 2. Re: Multiple enteries to one field?
            MichaelNeck

             Thank you.  I have much to learn on resolving many to many relationships.