8 Replies Latest reply on Mar 2, 2013 5:12 PM by petermontague1

    Logic of Sales Tracking with repeat Customers

    micheleclark

      Hi,

       

      I need logic help.

       

      I am making a form for a sales person to record daily sales.

       

      There will be entries for customers and invoice/purchase orders. These will include all the contact information for the customers plus an itemized list and quantity details in the order

       

      I plan on showing these options through portals onto one simple form. However, let's say another sale is being made to a customer already in the system.... is there a way that he doesn't have to fill out allllllll their contact information again, but can draw from the form already made and have it load up?

       

      How is this possible?

       

      I appreciate it!

        • 1. Re: Logic of Sales Tracking with repeat Customers
          DavidJondreau

          There's two ways to do this. Is it important that historical data is preserved? For example if a customer's address changes, do you need to see the old address on old invoices and the new address on new invoices? Or is it fine if the invoice always shows the current address?

           

          If it's the latter, then you need a relationship between an Invoice table and a Customer table based on Customer ID. The invoice should have address fields from the Customer table. The salesperson enters the Customer ID, and the address will display.

          • 2. Re: Logic of Sales Tracking with repeat Customers
            micheleclark

            It is fine if the invoice always shows the current address!

             

            So far I just built this–– Does the logic look accurate?

             

            Screen shot 2013-02-15 at 4.28.06 PM.png

             

             

             

            EDIT:

             

             

            So with the changes you suggested it looks like this now––

             

            Screen shot 2013-02-15 at 4.50.02 PM.png

            • 3. Re: Logic of Sales Tracking with repeat Customers
              DavidJondreau

              It looks ok, but I think you have unnecessary join tables. Unless you assign an invoice to more than one customer, you don't need rel_customers_invoices. And probably not rel_invoices_items or rel_sales_customers. Putting a customer_idfk field in the invoices table directly is all you need. You can skip the join.

              1 of 1 people found this helpful
              • 4. Re: Logic of Sales Tracking with repeat Customers
                micheleclark

                Wait: my sales person has to enter the customer id?

                 

                I had assumed that the customer ID was a serial number I wouldn't want my sales person messing with or really seeing.

                Is it something they would assign? How would they remember the ID?

                 

                Should I create a value list portal?

                • 5. Re: Logic of Sales Tracking with repeat Customers
                  ChristopherReesman

                  Each customer has their own id number but you should include a redord number as well that way each customer can have more than one contact.

                   

                  You than create a table occurance of the customer table to display the list of all customers for a salesmen and he can just select the customer from that list.

                   

                  E4.jpg

                   

                  E5.jpg

                  When the contact on the left is selected the information is displaied to the right.

                   

                  E6.jpg

                   

                  Hope this helps

                  Reesman

                  • 6. Re: Logic of Sales Tracking with repeat Customers
                    petermontague1

                    I have an issue with using customer_id exclusively as the key field. How am I supposed to remember the customer_id for Bob Jones? Is it normal practice to have the customer's name as a key field to another TO of the customers table to allow us choose from a list of available customer_ids?

                    • 7. Re: Logic of Sales Tracking with repeat Customers

                      Hi Peter,

                       

                      You create a value list of your unique customer IDs (auto-enter serial or UUID) and specify use values from field and all values.  In the next screen, select CustomerID in the left pane and your customer's full name (usually a concatenated calculation) in the right pane.

                       

                      Below in same dialog, specify 'all' and 'only show values from second field.

                       

                      Now attach this value list as POP-UP with CustomerIDs to your Invoices::CustomerID or your Quotes::CustomerID (the foreign keys where you will need to select your customers).  It will display the customer name for selection and also after you you have selected, but it will insert the CustomerID into the field.

                       

                      ADDED:  If you have a lot of customer IDs, a portal for selection which can be filtered works well (usually provided in a modal or dialog window).  And you can also filter the relationship using a global so if you type a 'c' only customers with names beginning less than or equal to c will display etc.

                      • 8. Re: Logic of Sales Tracking with repeat Customers
                        petermontague1

                        Thanks. I'll give that a go.