6 Replies Latest reply on Jun 10, 2014 11:07 PM by jared944

    Table Relationships for Invoices/Invoice Line Items/Discounts

    jared944

      Title

      Table Relationships for Invoices/Invoice Line Items/Discounts

      Post

           Hey everyone,

           I am having difficulty figuring out a relationship for a database I am trying to establish and want to see if anyone has any ideas to jump start my creative process.

           I have five tables - Invoices, Invoice Line Items, Products, Promotions and Promotion Line Items. (Enclosed is the relationship graph).

           I currently have a working invoicing database ; now I want to try to add discounts based off of promotions.

           Each row in the Promotion table contains a 1) "Promotion ID" per line item, 2) "Group ID" which is the name/ID of the promotion, 3) a related keyfield (with the Products table) that identifies the product, and 4) the amount to discount.

           I want to be able to select the promotion (IE "Group ID" ) to apply from the Invoices Layout from a portal showing the Promotion Line Items table. I then want to set up a relationship that will identify if that promotion "Group ID" contains a product among the invoice line items. If it does, than I want it to apply the discount in the final invoice.

           I'm trying to be very careful to make it so if I change a promotion OR product amount in the future it wont affect prior Invoice Line Item amounts. I also want to look at each invoice/customer and see what promotions were used so I have the analytics to shape my future marketing.

           If anyone has any suggestions I would be very appreciative.

           Thanks!

           Jared

            

      Screen_Shot_2014-05-18_at_4.03.56_PM.png

        • 1. Re: Table Relationships for Invoices/Invoice Line Items/Discounts
          philmodjunk

               You seem to be missing a unit price field in your products table. Shouldn't adding a new record to Invoice|line items look up a unit price from products?

               There are many different ways you might set up discounts and promotional pricing. Can you explain in detail how you want these promotions to work?

          • 2. Re: Table Relationships for Invoices/Invoice Line Items/Discounts
            jared944

                 Good catch...Im just sandboxing the solution so some fields are missing. But yes, I would incorporate an amount in the products table.

                 I have certain Products :

                  

                 Table : Products

                 ID      Name                      Amount

                 1       Eyeglasses               $300

                 2       Contact Lenses        $100

                 3       Exam                        $90

                  

                 I want to create promotions on these products that would be grouped into Categories, such as :

                  

                 Table : Promotions

                 ID       Group                                  Product                          Discount

                 1        1    Associate Discount         1  Eyeglasses                30% off

                 2        1    Associate Discount         2  Contact Lenses         30% off

                 3        2    Friends and Family         2  Eyeglasses                40% off

                 4        2    Friends and Family         3  Exam                         50% off

                  

                 Then, from the Invoice Details layout I want to be able to add promotion Categories from a portal (From the above example, either 1) Associate Discount and/or 2) Friends and family.

                  

                 Table : Promotion Line Items

                 ID     Group

                 1      2   Friends and Family

                  

                 From the same invoice layout I will choose what products to add through a portal to Invoice Line Items. This row would include the Product Name, Amount, Discount and Total (If any discounts match from above).

                  

                 Table : Invoice Line Items

                 ID     Product                   Amount        Discount      Total

                 1      3  Exam                     $100              $50            $50

                 2      2  Contact Lenses     $100                0              $100

                  

                 I hope that clarifies things. Thanks!

                 Jared

            • 3. Re: Table Relationships for Invoices/Invoice Line Items/Discounts
              philmodjunk

                   So if a Discount Category is selected on a given Invoice, certain products will have a percent discount specific to that product-discount category combination applied to the price? And any number of items can be purchased at that discount rate?

                   If so, your initial relationships, for setting up your discount rates would look like this:

                   Products------<ProductDiscount

                   Products::__Item_ID = ProductDiscount::Item_ID

                   You could then use a portal to ProductDiscount to create each relevant discount with a DiscountCategory field set up with a value list of the different discount categories.

                   Then, to use a looked up value auto-enter setting to copy any such rate into a line item record, you'd need this relationship:

                   LineItems::Item_ID = ProductDiscount|LineItem::Item_ID AND
                   LineItems::DiscountCategory = ProductDiscoun|LineItemt::DiscountCategory

                   ProductDiscount and ProductDiscount|LineItems are two occurrences of the same data source table.

                   This allows you to copy the relevant discount rate into line items where a calculation can include it in the calculation to compute a line item cost. But note the fact that this requires each lineItem record to have the DiscountCategory specified. Since you aren't going to want to select a discount category over an over for each line item on a single invoice, you'll need to use some method to automatically copy this value from a field in your invoices table.

                   Option 1:

                   Make LineITems::DiscountCategory a field with global storage. Use a script performed by both OnRecordLoad and OnObjectSave (on the Invoices::DiscountCategory field) to perform a script that uses set field to copy this value to that global field.

                   Option 2:

                   Make LineItems::DiscountCategory an unstored calculation field that simply copies the value of Invoices::DiscountCategory

                   Option 3:

                   Use an auto enter setting on LineItems::DiscountCategory to copy over the value.

              • 4. Re: Table Relationships for Invoices/Invoice Line Items/Discounts
                jared944

                     The trouble I am getting into is the "method to automatically copy" the category to the line items. I can carry something forward if I only apply a single discount. What if there are multiple discounts applied per invoice? Than I would have to figure out which DiscountCategory I wanted to copy to the line item keyfield. What if there are multiple DiscountCategories that can apply to the same line item?

                     In my current solution, I select the discount from every line item. This is time consuming and would be difficult for reporting because I would possibly have many of the same discounts per invoice. I want to track which discounts were applied to which invoice, not necessarily the line items.

                      

                     Thanks!

                      

                     Jared

                • 5. Re: Table Relationships for Invoices/Invoice Line Items/Discounts
                  philmodjunk

                       The discount category could be a drop down list field in Line Items. It could even be a check box group. But the whole idea behind copying a value from Invoices was based on the assumption that you would only specify one discount option for an invoice and that it would potentially apply to the same invoice. This saves you the trouble of selecting this value over and over again, but limits you to a single discount option.

                       You aren't really limited to having it only one way here. You could select a discount category from a field in Invoices that applies to all line items and then, if you place the field in LineItems that copies this value into the portal row, you can edit the value for those line items where you choose to apply a different discount option.

                       

                            What if there are multiple DiscountCategories that can apply to the same line item?

                       Then a check box field sounds like your best bet here. This will complicate your discount calculations, however as you will need to combine all of the discount values for that product into a single discount. You'd need to set up an auto-enter calculation that uses the sum function to copy the total combined discount percentage into a field in the line items table. (The return separated values entered into a field via a check box format will match records by any one of the check box values, so if you select two discount check boxes, line items now matches to two related records in the ProductDiscount table.)

                        

                  • 6. Re: Table Relationships for Invoices/Invoice Line Items/Discounts
                    jared944

                         I see what you mean. I ended up using 3 different fields to select various discounts. I also had to set up three different calculations fields to figure the amount to apply after the discounts.