4 Replies Latest reply on Jun 4, 2010 9:10 AM by ultraman

    calculation with if and value list items

    ultraman

      Title

      calculation with if and value list items

      Post

      Hi. I'm obviously new to all that but i learn everyday.

       

      I'd like to know if i can do this and if yes, what would be the good syntax.

       

      In the same table, I have a value list called products_types and let's say there's 2 values in it: type A and type B. (drop-down)

       

      And I have, for example, 4 fields with price values: price 1, price 2, price 3, price 4.

       

      And finally a calculation field called Total.

       

      What I want is that: 

       

      If I choose type A in products_types, then total = price 1 + price 2

       

      If I choose type B in products_types, then total = price 2 + price 3

       

      And so on...

       

      Is that possible?

       

      Thanks

       

        • 1. Re: calculation with if and value list items
          ryan

          Use the Case function in your Total field calculation:

           

          Case (

          products_types = "type A" ;  price_1 + price_2 ;

           

          products_types = "type B" ;  price_2 + price_3 ;

           

          "" )

           

           

          • 2. Re: calculation with if and value list items
            ultraman

            Thanks for your answer.  When I put this final code, I always have this error message: The specified field cannot be found. And then, product_types is highlighted.

             

            Case (

            product_types = "Apparel" ;  TRANSFERS::cost per transfer + TSHIRTSTOCK::tshirt_cost ;
            product_types = "Picks" ;  RAW_PICKS::raw_picks_cost  + PACKAGE_BAGS::bags_cost  + CARDS::cards_cost ;
            "" )

             

            Do I have to specify that is a valuelist or something else?


            • 3. Re: calculation with if and value list items
              ryan

              what is the name of the Field you are selecting either type A or type B in from the products_type value list?

              that is the field you want to use.

               

              whatever the name of that field is, use that in the calculation where you currently have "products_type"

              • 4. Re: calculation with if and value list items
                ultraman

                i got it! I was using fixed values in my list.

                 

                I changed that so my valuelist get the values from the name field of TYPE table. I then make a relationship between this table and my products table, change the list name to field name in the code and that works now!

                 

                here's the code in case it could help someone:

                 

                Case (

                TYPES::name = "Apparel" ;  TRANSFERS::cost per transfer + TSHIRTSTOCK::tshirt_cost ;
                TYPES::name = "Picks" ;  RAW_PICKS::raw_picks_cost  + PACKAGE_BAGS::bags_cost  + CARDS::cards_cost ;
                "" )

                 

                Thanks