8 Replies Latest reply on Nov 11, 2013 2:38 PM by cortical

    Quote Items from different product tables

    adriana.lopez

      Hello,

       

      I have the following tables:

       

      Products Type A

      Products Type B

      Products Type C

      Products Type D

      In order to create quotes, client should be able to add any type of product the Product Type. It should looks something like this:

       

      Quote

      Quote Details:

      Company:

      ITEMS:

      Product Type

      Product

      Product Details

      Price

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      I have created a value list that contains all product types names (Type A, Type B, Type C, Type D) so when the client needs to add a product to the quote the “Product” field should display the products based on the product type entered under “Product Type” and prices and product details auto-populate based on the “Product” selected

      How can I accomplish this?

       

      Thanks for any advice

        • 1. Re: Quote Items from different product tables
          PSI

          Hi Adriana,

           

          My suggestion assumes a couple of things.

          1 - you have a really good reason to have separate product tables

          2 - you have a Quote and Quote Line Item tables

          3 - you have a product code or sku in the line items table that will be the lookup link

           

          Create relationships from the line item table to Products. One for each product table type using Product code to product code

           

          For the other fields that you want to lookup values for, product name, details and price

           

          For each field enter auto-enter > calculated value something like this

           

          Case (

          not IsEmpty ( ProductsA:Price ) ; Lookup ( ProductsA::Price ) ;

          not IsEmpty ( ProductsB:Price ) ; Lookup ( ProductsB::Price ) ;

          not IsEmpty ( ProductsC:Price ) ; Lookup ( ProductsC::Price ) ;

          not IsEmpty ( ProductsD:Price ) ; Lookup ( ProductsD::Price ) )

           

          Hope this helps,

          John Morina

           

           

          • 2. Re: Quote Items from different product tables
            erolst

            PSI wrote:

            My suggestion assumes a couple of things.

            1 - you have a really good reason to have separate product tables

             

            … which is the first thing I would take under scrutiny.

             

            Adriana –

             

            why do you have four Products tables, instead of one table with a category field/ID?

            • 3. Re: Quote Items from different product tables
              Stephen Huston

              Hi Adriana,

               

              I hope you didn't just bypass John's statement: "My suggestion assumes a couple of things. 1 - you have a really good reason to have separate product tables"

               

              I am not willing to asume you have that really good reason without knowing what it is, because, if you combined your product lists in a single table:

              • your problem would be reduced to having a field for the product type, so that
              • a filtered value list could display only the products for the type chosen;
              • the relationships and lookups would be simple, and
              • you would have only one product table to deal with throughout your solution.
              • 4. Re: Quote Items from different product tables
                adriana.lopez

                Hello,

                 

                Thanks for your response.

                 

                Yes, I do have a good reason to have products in separate table. The fields vary depending on the product type, which make things more complicated.

                • 5. Re: Quote Items from different product tables
                  cortical

                  <<Yes, I do have a good reason to have products in separate table. The fields vary depending on the product type, which make things more complicated.>>

                   

                  Well that does not mean you should not have one main product table.

                   

                  This is an example of data/meta-data or supertype-subtype ( as I see it anyway)

                   

                   

                  I would put all products in one main product table, together with common data ( product_id, product name, brand_id, cost profile fileds...)., as well as a  field subtype_code denoting the subtype ( sta, stb, stc, std)

                   


                   

                  Then create related tables for the meta data respective to each product subtype; ProductSTA, ProductSTB, ProductSTC, ProductSTD

                   

                  Things then become a whole lot easier.

                   

                   

                   


                  1 of 1 people found this helpful
                  • 6. Re: Quote Items from different product tables
                    adriana.lopez

                    Thanks Cortical!

                     

                    I guess I can do that. However, all product type tables are relating to one table, the Company table. In the Company layout I show a portal for each Product Type table (Type A, Type B, Type C, Type D). If I have them in one single table how would I be able to relate it and show that information in different portals depending on their type?

                    • 7. Re: Quote Items from different product tables
                      cwiltgen86

                      You could use four seperate relationships using a contant field in the Company table, or you could filter each of the four portals.  This would accomplish putting all the products in one table and being able to view them in different portals.

                      • 8. Re: Quote Items from different product tables
                        cortical

                        why are products relating to 'one table'  the company table? I may be misinterpreting the implications of that.

                         

                        I would expect it to be

                         

                        CMP-QUOT-QUOTLINE-PRODUCT-P_meta_a

                        CMP-QUOT-QUOTLINE-PRODUCT-P_meta_b

                        CMP-QUOT-QUOTLINE-PRODUCT-P_meta_c

                        CMP-QUOT-QUOTLINE-PRODUCT-P_meta_d

                         

                        now the above would actually be CMP-QUOT-QUOTLINE-PRODUCT and then 4 branching meta rels off the terminal Product

                         

                        IF you have different product types BUT all types have the same properties (fields) then all you need is a product_type_code (a,b,c,d...) in the Product table, and use portal fiters wher required, BUT as you state the fields vary accourding top product type

                         

                        Maybe you could give us an idea of the actual products, and what you regard as types

                         

                        If there is a requirement for Product Type meta data in say 4 sub-tables of product, then

                        where you need the meta data displayed as per the above tunneled REL path, with 4 terminal meta TOC, for any product type, then superimpose the required sets of fields for each meta type

                         

                        meta_a_size , meta_a_volume

                        meta_b_colour, meta_b_hardness

                        etc

                         

                        So a Quote Items portal for example would have the product name, cost, from the product table, the quantity and extended cost fields of the line item table, and 4 sets of fields superimposed on each other , one set for each product meta type, in the same portal row space.

                         

                        Alternately, you could create the 4 RELS from product to each meta-type subtable, product_id= product_id, and define a calculation in product to return the concatenated meta data, something like

                         

                        Let([

                        TYP = product_type

                        ;]

                        Case(

                        TYPE = "a" ; ProdTypeA::meta_a_size & ", " &  ProdTypeA::meta_a_volume

                        TYPE = "b" ;  ProdTypeB::meta_b_colour, & ", " & ProdTypeB::meta_b_hardness

                        //etc

                        ))

                        1 of 1 people found this helpful