1 2 Previous Next 20 Replies Latest reply on Jul 7, 2014 10:34 AM by philmodjunk

    Need to lookup multiple Products for same customer?

    PeterBrambs

      Title

      Need to lookup multiple Products for same customer?

      Post

           I have a "Sales Funnel" database where I list all the products a customer may be interested in (Product1 through Product4).

           To do this, I created a "Product" Table which lists the available products and a "Sales Funnel" Table which is supposed to display up to four different products of interest per client. The "Product" Table is related to the "Funnel" Table by "Product Name" (the match field). I have been able to create a lookup for one product per client (Product => Product1), but If I try to add a second Product (Product => Product2) it all collapses. Nothing works.

           How can I use the one Product Table to provide data for my Sales Funnel with 4 possible products per client using a lookup?

            

             

      Snip20140612_14.png

        • 1. Re: Need to lookup multiple Products for same customer?
          philmodjunk

               You appear to have a one to one relationship, On record in Inv Products linked to exactly one record in Inv Funnel where you need a one to many relationship.

               But if you had a product named "Black Widget" and then created 4 records in Inv Funnel where Product 1 Name was "Black Widget", you'd have 4 records in Inv Funnel linked to one record in Inv Products.

               But I doubt that this is what you really want. I would think that you would want these relationships. You talk in terms of "clients" but don't have a table of clients in your screen shot. Maybe that would be your Inv Company table or maybe you need to add such a table:

               Clients----<Inv Funnel>------Inv Products

               Clients::__pkClientID = Inv Funnel::_fkClientID
               Inv Products::__pkProductID = Inv_Funnel::_fkProductID

               With these relationships, you can put a 4 row portal to Inv Funnel on your Clients layout and use it to list 4 different products.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               Note that I have not used name fields for any of my match fields. While this can be tempting to do, name fields are both subject to possible change in the future and vulnerable to data entry errors. Should you ever need to change the name in the future--due to discovering an error or because the name was changed for other reasons, this breaks the link to related records--which is avoided by using auto-entered serial numbers that are never changed once assigned to a record on the "one" side of a relationship.

          • 2. Re: Need to lookup multiple Products for same customer?
            PeterBrambs

                 Phil,

                 Thanks for responding. Let me clarify. My "Investment Funnel" is a list of current clients and the products they are interested in. In each client record, I can choose up to Four Investment products they may be interested in. My "Investment Products" table lists all the products I have to provide my clients. As I click on each of the four investment choices, I'd like a drop down to come up that lists the products in the "Investment Products" table.

                 Once I select a product, I have other fields in the "Investment Products" table that I'd like to bring over as well (i.e. Product commission, max age requirement...)

                 I tried the Portal technique, but wasn't able to get it to Drop-down the list of investment product options. How do I relate the 4 investment choices on the "Funnel" with the "Investment Products" table?

                 Thanks in advance.

            • 3. Re: Need to lookup multiple Products for same customer?
              philmodjunk
                   

                        In each client record, I can choose up to Four Investment products they may be interested in.

                   But why limit it to just 4? What if your business changes and you need them to be able to list 5, or 6 or more?

                   You need the portal to a related table. Not only does this allow flexibility--you don't have to redesign your database if you need more or fewer products of interest to list, but it will work to bring over the data that you want to see.

                   Inv Funnel----<ProductsOfInterest>-----Inv Products

                   Inv Funnel::__pkClientID = ProductsOfInterest::_fkClientID
                   Inv Products::__pkProductID = ProductsOfInterest::_fkProductID

                   In your portal, you'd set up _fkProductID as a value list of ProductID's from Inv Product with Product Name as the second field value. Thus, you'd select a product by name in the value list, but the value list enters the ProductID. This will allow you to select as many products of interest in the portal as you design your layout to permit. This can be a four row portal to limit them to 4 choices, but you can also include a scroll bar and allow any number of choices.

                   

                        Once I select a product, I have other fields in the "Investment Products" table that I'd like to bring over as well (i.e. Product commission, max age requirement...)

                   You can add any fields from Inv Products to the portal row of Products of Interest that you need to show this data. Once you have selected the product from the value list set up for _fkProductID, they'll show data from the Inv Products record for that product.

                   Note 1: This is really just like an invoice system where a custom buys multiple products in a sales transaction. The only difference is that your customer has not yet purchased these items. You can look at the Invoices starter solution to see how a single invoice allows a customer to select multiple products in a portal and see data about each product in the portal. It also shows a way to print this information.

                   Note 2: I strongly recommend that you use ID numbers, not names to link your records in relaitonships. Names are subject to change and data entry errors that can require that you change the name entered into your database. But if this field is a match field in a relationship this breaks the link to other records in your database--a complication you can avoid by using ID numbers.

              • 4. Re: Need to lookup multiple Products for same customer?
                PeterBrambs

                     Phil, I'm getting closer but still having problems with the Portal. 

                     Per your suggestion, I created a ProductID serial number. I'm using it to relate the four products (Product 1 name, Product 2 name...) in my "Sales Funnel" table to my "Products" table.

                     In the layout  of  the "Sales Funnel" table, I set up the "Product 1 name" field to be a drop-down showing all the available Products in the "Product" table. When I select a product from the list, the first line of the Portal fills in properly. I'm not sure if I need to do this as I'd like to do this from within the portal.

                     *** This is where I am stuck. ***

                     I haven't figured out how to do a Drop-down for the next three products. Assuming my portal looks like the layout below, how can I get the Four product choices to drop-down and let me select the four products within the portal boundries?

                     Product1      Commission1  

                     Product2      Commission2

                     Product3      Commission3

                     Product4      Commission4

                     BTW, if I get this portal working, do I need for different Product fields in my Sales Funnel table?

                • 5. Re: Need to lookup multiple Products for same customer?
                  philmodjunk

                       The whole point in using a portal is that you only  have one product field per portal record instead of 4 different product fields in the same record.

                  • 6. Re: Need to lookup multiple Products for same customer?
                    PeterBrambs

                         I get that. i just haven't been able to get the 4 line portal to drop down and allow me to choose the four products.

                    • 7. Re: Need to lookup multiple Products for same customer?
                      philmodjunk

                           You select one product in each of the four portal rows. Is "Allow creation of records via this relationship" enabled for the portal table in the relationship set up for your portal?

                      • 8. Re: Need to lookup multiple Products for same customer?
                        PeterBrambs

                             Half working now:  I created a portal for "Product Name" and "Commission Rate". I can select four Products using a drop-down list from the table "Investment Products". "Commission Rate" should come from the "Investment Products" table. As you can see the "Commission Rate" doesn't populate. I was hoping that after I selected the "Product" that it would populate with that product's "Commission Rate". Not happening. What am I doing wrong?

                        • 9. Re: Need to lookup multiple Products for same customer?
                          philmodjunk

                               I'd need to see exactly why you have currently set up to in order to see what to check for. The obvious answer is that the value selected from your value list does not link that portal record to any record in your table of products. That can be due to the wrong value list setup or the wrong relationship setup.

                          • 10. Re: Need to lookup multiple Products for same customer?
                            PeterBrambs

                            Sales Funnel Database

                                  

                            Here is what I have so far.  Portal in the Sales Funnel layout is below.

                            Based on what I have been reading. The Parent table in this scenario is the Sales Funnel and the Investment Products table is the child table.

                            • 11. Re: Need to lookup multiple Products for same customer?
                              PeterBrambs

                              Using the Value List under “Prod ID”, I select a Product from the related Investment Products Table.

                              • 12. Re: Need to lookup multiple Products for same customer?
                                PeterBrambs

                                After selecting ProductID number 96, the data comes across ok for the first line. However, the second line populates with Prod ID 96 as well. I’m not sure why.

                                • 13. Re: Need to lookup multiple Products for same customer?
                                  PeterBrambs

                                  I then try to select a different product, Acme 4000, for Line item #2 and Line 1 changes from Acme 3000 to Acme 4000. I also notice that, unlike the first line selection which populated correctly, the product name and commission rate is blank.  

                                  I thought Line 2 would show a second product along with it’s related information based on the ProductID chosen.

                                  • 14. Re: Need to lookup multiple Products for same customer?
                                    PeterBrambs

                                    This is the value list that is used to pick a product in the Portal.

                                    1 2 Previous Next