3 Replies Latest reply on May 3, 2013 11:22 AM by philmodjunk

    Conditional Calculation

    JohnDevlon

      Title

      Conditional Calculation

      Post

           With this post, I would like to ask a question.

           A have a problem with a small project i'm building.

           I have a database with 3 tables: "Basket", "Product items" and "Default baskets".

           The table "Product items", has 3 fields: "ProductID", "ProductName" and "ProductPrice".
           The table "Default baskets", has 4 fields: "DefaultBasketID", "SetName", "BasketPrice" and "ProductID".
           The table "Basket", has 3 fields: "ID", "DefaultBasketID", "ProductID".

           The table "Product items" contains products like a banana, a appel, a waterbottle, ...

           The table "Default baskets" contains several default shopping baskets containing product Items.
           Example: a basket that contains 3 appels and 1 banana.

           The table "Basket" contains a choosen default basket and items picked.

           When creating a new basket, you pick a default basket and add products.

           So far so good.

           Now I need a calculated field that calculates the basket price. The total price is the basket price + all items not in the default list.
           When a default basket has 2 bananas and the real basket has 4 bananas: the total price is the default price of the basket and 2 additional bananas.

           How do I create a calcalution like that?

           Thanx

        • 1. Re: Conditional Calculation
          philmodjunk

               I would treat each "default basket" as a record in Products with a price for that basket. Then all pricing will come from the products table whether the item be added to the invoice as an indivdual item or as part of a "basket".

          • 2. Re: Conditional Calculation
            JohnDevlon

                 This approach doesn't allow you to calculate the items based on the default basket items. Using the suggested approach will make you rely on the correct input of the user. I need a fail proof systeem to compare the default basket content with the current basket content...

            • 3. Re: Conditional Calculation
              philmodjunk

                   You can use a relationship to a related table that lists the product ID's, unit prices and quantities of each item that makes up that basket. A portal on the products layout can be used to create the list products that make up the basket.

                   Invoices----<lineItems>-----Products----<BasketProducts>-----Products|inBasket

                   Products and Products|InBasket would be Tutorial: What are Table Occurrences? that refer to the same data source table.