14 Replies Latest reply on Jun 27, 2014 7:11 AM by philmodjunk

    calculated value formula

    DannyAccola

      Title

      calculated value formula

      Post

           my formula

           If ( ClientAdress::Billing Currency = "CHF" ; Products::Wholesale 2 CHF ; Products::Wholesale 2 EUR )

           does not work the way i intended it to work ... the field only shows the value of ::Products::Wholesale 2 EUR .... what am i doing wrong ..??

        • 1. Re: calculated value formula
          philmodjunk

               Billing Currency is a field in ClientAddress the other two fields are in Products

               What relationship links the two tables?

               In what Context does this calculation evaluate? (If this is in a script step, what layout, based on what table is current at the moment this step executes? If this is in a calculation field, what was selected from the context drop down at the top of the specify calculation dialog?

          • 2. Re: calculated value formula
            DannyAccola

                 the field ::WholesalePrice does calculate this value based on this formula ... 

                  

            • 3. Re: calculated value formula
              DannyAccola
              /files/e9ecdaae20/Screen_Shot_2014-04-11_at_4.32.30_PM.png 1252x1000
              • 4. Re: calculated value formula
                philmodjunk

                     This means that your calculation "tunnels" through the following table occurrences to access a value in the Billing Currency field:

                     OrderLineItems>------Order>------ClientAddress

                     If there are any problems in those relationships, such as a match field with the wrong value or no record in order that links to both the correct OrderLineItems and ClientAddress records, Billing Currency will evaluate as empty and you'll get the value of Products::Wholesale 2 EUR--which is consistent with what you report here.

                     But it could also mean that Billing Currency has been defined to be a field of type number instead of text and that would also produce this result.

                • 5. Re: calculated value formula
                  DannyAccola

                       every ClientAddress contains a Currency, and every current Products::Wholesale 2 EUR and Products::Wholesale 2 CHF has a value ... older Products::Wholesale 2 EUR are empty ... but they are not used for this years calculations.

                  • 6. Re: calculated value formula
                    philmodjunk

                         Sorry, but that does not seem applicable to the comments I posted last April.

                    • 7. Re: calculated value formula
                      DannyAccola

                           Sorry, I was traveling and left my computer at home .. Now I am back and stuck with this riddle

                      • 8. Re: calculated value formula
                        DannyAccola

                             ::WholesalePrice contains this CalculatedValue

                             If ( ClientAdress::Billing Currency = "CHF" ; Products::Wholesale 2 CHF ; Products::Wholesale 2 EUR )

                             but the Layout only delivers Wholesale 2 EUR
                             ClientAdress::Billing Currency is correct
                             all Products::Wholesale 2 CHF and Products::Wholesale 2 EUR
                             have informations ... 

                             what's wrong

                        • 9. Re: calculated value formula
                          philmodjunk

                               It would seem that "Billing Currency" never equals "CHF". Could it be that Billing Currency is defined in Manage | database as a number field?

                               If it is text, could it be that the data in that field is never exactly CHF, perhaps an additional character such as a space is keeping it from being equal to the quoted string "CHF".

                          • 10. Re: calculated value formula
                            DannyAccola

                                 yes ... i have checked both; ::BillingCurrency (the field and the database) both seems to be fine

                            • 11. Re: calculated value formula
                              philmodjunk

                                   Nevertheless Billing currency never equals the exact text "CHF" or you would not get this result. Case is not the issue. Upper an lower case letter combinations such as "chf" or "Chf" will evaluate the same, but if there is any other text in this field such as a space or return character, then you'll get the result that you describe here. Another possible cause of this issue can happen when the field is formatted with one or more check boxes or radio buttons--the format can hide other data that might be in the field and can permit selecting multiple values in the same field--all of which will keep this field from being equal to the specified text string.

                                   If that's not the issue here, I'd put the clientAdress::Billing Currency field in the body of the layout that you've shown here temporarily so that you can check the actual value. You may want to double click the text in this field to see what nonvisible text, such as a space or return might also be present in the field.

                              • 12. Re: calculated value formula
                                DannyAccola

                                     to select the currency - originally - i defined a value list for the ClientAdress::Billing Currency field ... to test options - i switched to a regular field and back to the value list .. the problem consists .. ):
                                      

                                • 13. Re: calculated value formula
                                  DannyAccola

                                       but maybe (?) the problem starts here ... when i add a new order the currency field already shows the 'wrong' price

                                  • 14. Re: calculated value formula
                                    philmodjunk

                                         Changing back and forth between an edit box and a value list to "fix" this issue is NOT what I was recommending. I was suggesting that you format the field as an edit box and click into the field to examine the actual VALUE of the field and not what can be a misleading apparent value if you are using check box/radio button formatting or have an added invisible character in your field. And that added character is still possible even with the value list definition that you show. You need to check the values in the FIELD not the value list in order to be sure.

                                         when i add a new order the currency field already shows the 'wrong' price

                                         Assuming this relationship:

                                         Client-----<Orders (one client record to many orders records)

                                         You should not see ANY value in this field when you first add a new record. Then, when you select the client for this order, you should see the data from the selected Client record. If after linking the order record to the client, you see the wrong text in this field, then the following may be possible:

                                         a) the wrong data is selected in this field in the client record

                                         b) the order record is linked to the wrong client

                                         c) There are two or more client records all with the same value in the field used to link a client record to an order record. If that happens, your relationship will access the value of the "First" related client and this may not be the client you expected.

                                         And please note that a calculation field evaluates from a specified "context' selected in a drop down at the top of the specify calculation dialog. This "context" can produce confusing results when the calculation field is displayed on a layout context different from that specified in the calculation as it will always evaluate from the defined context in the calculation--not the layout's context.