7 Replies Latest reply on Nov 23, 2010 4:22 PM by philmodjunk

    Calculations within Case

    RandallGrove

      Title

      Calculations within Case

      Post

      I am a newbe

      I am building a form that Calculates the price and cost of a number of items (custom manufacturing).  I am using  multi-able value lists that use Case to obtain a value to use in another calculation. These values are calculated in other tables and work just fine in their native tables. However when I try to use their calculated value in the form and Case mentioned above, I get nothing.

      Example:
      Case
      ValueCount ( FilterValues ( SubType1 ; "Acrylic Clear NG (1/8)")) ; "Background Substrate::perSqincha * SsArea1 ;" ;
      ValueCount ( FilterValues ( SubType1 ; "Acrylic Clear NG (1/4)")) ; "Background Substrate::perSqinchb * SsArea1 ;" ;
      )

      Help please, while I still have hair.

        • 1. Re: Calculations within Case
          philmodjunk

          "When I try to use their calculated value ..., I get nothing"

          If you just place the above calculation field on your layout, do you see an empty field or do you see text? If it's empty, is your calculation defined to return text or number?

          • 2. Re: Calculations within Case
            RandallGrove

            The calculation field shows only the value for SsAera1, In real terms the values should return a result of

            Example:
            Case
            ValueCount ( FilterValues ( SubType1 ; "Acrylic Clear NG (1/8)")) ; "Background Substrate::perSqincha * SsArea1 ;" ;
            )

            // were perSqincha=.0579,  and  SsAera1=30  result 1.737

            // all I get is 30.00 as a result

            • 3. Re: Calculations within Case
              philmodjunk

              The quotes and the semi-colon are what I'm checking first:

              "Background Substrate::perSqincha * SsArea1 ;"

              If you just want to compute a number and return that result, those quotes and that semi-colon shouldn't be there. If this value is passed to another calculation that uses the Evaluate function, then the quotes make sense as you'd want this case function to just return text for it to evaluate, but then the ; makes no sense here.

              • 4. Re: Calculations within Case
                RandallGrove

                Still can not get it to work.


                I am beginning to think I am on the wrong track.


                Let me expand what I am doing.  I am pulling data from a number of tables that are data input records that have a breakout and costing of materials used in our products. I am using the results in these tables to calculate the price of custom mfg. products.  On the form that the calculates that price, I have multiable value list using Case as well as straight math functions. I originally inserted the values for Case manually to establish that the formula worked. So far so good. Now I want to directly pull those values form data input forms into the Case calculation to replace the previously inserted values.

                Sorry for any misstatement of the situation


                //This works

                The main calculation field looks like this with out insertion of external field values:

                Case(
                ValueCount ( FilterValues ( SubType1 ; "N/A")) ; "0";
                ValueCount ( FilterValues ( SubType1 ; "Acrylic Gravo-Tac (1/8)")) ; ".0579" ;
                ValueCount ( FilterValues ( SubType1 ; "Acrylic Clear NG (1/8)")) ; ".0284" ;
                )
                *
                SsArea1
                +
                Case(
                ValueCount ( FilterValues ( SsPaint ; "Yes")) ; ".0069";
                ValueCount ( FilterValues ( SsPaint ; "No")) ; " 0 ";
                ValueCount ( FilterValues ( SsPaint ; "N/A")) ; " 0 ";

                )

                *
                SsArea1

                +
                Case(
                ValueCount ( FilterValues ( SsInlaid1 ; "Yes")) ; ".50";
                ValueCount ( FilterValues ( SsInlaid1 ; "No")) ; " 0 ";
                ValueCount ( FilterValues ( SsInlaid1 ; "N/A")) ; " 0 ";
                )
                +
                Case(
                ValueCount ( FilterValues ( SsCustEdge1 ; "0")) ; "0";
                ValueCount ( FilterValues ( SsCustEdge1 ; "1")) ; ".5";
                ValueCount ( FilterValues ( SsCustEdge1 ; "2")) ; "1.";
                ValueCount ( FilterValues ( SsCustEdge1 ; "3")) ; "1.5";
                ValueCount ( FilterValues ( SsCustEdge1 ; "4")) ; "2.";
                )
                +
                Case(
                ValueCount ( FilterValues ( SsMount1 ; "0")) ; "0";
                ValueCount ( FilterValues ( SsMount1 ; "Screws")) ; "3.5";
                ValueCount ( FilterValues ( SsMount1 ; "Tape")) ; "1.5";
                ValueCount ( FilterValues ( SsMount1 ; "Adhesive")) ; "1.5";
                )
                +
                Case(
                ValueCount ( FilterValues ( SsRadius1 ; "0")) ; "0";
                ValueCount ( FilterValues ( SsRadius1 ; "1")) ; ".25";
                ValueCount ( FilterValues ( SsRadius1 ; "2")) ; "5.";
                ValueCount ( FilterValues ( SsRadius1 ; "3")) ; ".75";
                ValueCount ( FilterValues ( SsRadius1 ; "4")) ; "1.";
                )
                +
                SsNumberCharactors1 * .51
                +
                BrailleCharacter1 * .51
                +
                VinylGraphic area 1 * .01
                +
                Case(
                ValueCount ( FilterValues ( Border 1 ; "Yes")) ; "2.00";
                ValueCount ( FilterValues ( Border 1 ; "No")) ; " 0 ";
                ValueCount ( FilterValues ( Border 1 ; "N/A")) ; " 0 ";
                )
                +
                If ( SsArea1 >= 1 ; 6.25 ; 0 )

                • 5. Re: Calculations within Case
                  philmodjunk

                  I see what looks like a disconnect between what you posted in your first case function and this last expression.

                  The possible values returned by:
                  Case
                  ValueCount ( FilterValues ( SubType1 ; "Acrylic Clear NG (1/8)")) ; "Background Substrate::perSqincha * SsArea1 ;" ;
                  ValueCount ( FilterValues ( SubType1 ; "Acrylic Clear NG (1/4)")) ; "Background Substrate::perSqinchb * SsArea1 ;" ;
                  )

                  is: "Background Substrate::perSqincha * SsArea1 ;" , "Background Substrate::perSqinchb * SsArea1 ;" or "" (nothing at all)

                  Depending on what values are present in the text field SubType1.

                  You won't get any numeric result from this due to the quotes that enclose the 2nd and 4th parameters of your Case function.

                  I believe you should have:

                  Case
                  ValueCount ( FilterValues ( SubType1 ; "Acrylic Clear NG (1/8)")) ; Background Substrate::perSqincha * SsArea1 ;
                  ValueCount ( FilterValues ( SubType1 ; "Acrylic Clear NG (1/4)")) ; Background Substrate::perSqinchb * SsArea1 ;
                  )

                  Note the quotes and semi-colon that have been removed.

                  • 6. Re: Calculations within Case
                    RandallGrove

                    Thanks PhilModJunk

                    I am still thick in the head and can not get a predictable result. I have tried without quotes and semi-colons, updated the table relationships with the approate fields, use an external (to Case) function and still no positive results. I think I am expecting the wrong outcome. thank you for your time and effort.

                    • 7. Re: Calculations within Case
                      philmodjunk

                      Let's break this down one step at a time. With the quotes removed and "Acrylic Clear NG (1/8)" entered as a value in SubType 1, what does:

                      Case
                      (
                      ValueCount ( FilterValues ( SubType1 ; "Acrylic Clear NG (1/8)")) ; Background Substrate::perSqincha * SsArea1 ;
                      ValueCount ( FilterValues ( SubType1 ; "Acrylic Clear NG (1/4)")) ; Background Substrate::perSqinchb * SsArea1 ;
                      )

                      Return? (Define this as a separate calculation field for testing purposes if you haven't already.)

                      Given that your are referencing BackGround Substrate, a related table occurrence, you also need to consider context.

                      From the context of what table occurrence is this calculation evaluating (That's the "evaluate from the context of" drop down at the top of your specify calculation dialog) ?

                      How is that Table Occurrence ( box in the relationship graph of same name ) related to Background Substrate?