4 Replies Latest reply on Jun 12, 2013 11:59 AM by ColinJim

    Evaluating the result of a FilterValue

    ColinJim

      Title

      Evaluating the result of a FilterValue

      Post

           Hi all:

           I have a calculated field, c_photo_file_name that I want to evaluate based on the contents of a field (photo_tag) with multiple values.  

           My evaluation is:

           1) if the photo_tag field contains the word "Approach" I want c_photo_file to be Direction & "Approach";

           2) alternatively if the photo_tag field contains the word "Elevation" I want c_photo_file to be Direction & "Elevation";

           3) and if photo_tag does not contain either of those two words I want it to return Photo_ID. (Direction is another field defined within the table)

           I have been trying to use (but failing):

           Case (FilterValues( Photo_Tag; "Elevation")="Elevation; Direction & "Elevation"; FilterValues( Photo_Tag; "Approach")="Approach; Direction & "Approach"; photo_ID).

           Testing the FilterValues( Photo_Tag; "Elevation")="Elevation on its own returns "Elevation" for any records that contain that value in the Photo_Tag field but the calculation falls apart when I embed it into the Case().

           Any thoughts?

           Much thanks in advance!

            

        • 1. Re: Evaluating the result of a FilterValue
          philmodjunk

               Are the different "tags" in Photo_Tag separated by returns? That's what filtervalues expects and if the data is delimitted with other character such as commas or semi-colons, it won't work for you.

               Your example expression using case is missing some quotation marks, but perhaps that's a typo.

               Case (FilterValues( Photo_Tag; "Elevation")="Elevation"; Direction & "Elevation";
                          FilterValues( Photo_Tag; "Approach")="Approach"; Direction & "Approach";
                          photo_ID).

               Also, are you using a text field with an auto-entered calculation or a field of type calculation with text as the return type? (If using an auto-entered calculation, the field won't update when values in Photo_tag are modified unless you clear the "do not replace existing values" check box.)

          • 2. Re: Evaluating the result of a FilterValue
            ColinJim

                 The "tags" were entered into the field through the use of a checkbox set and are on different lines when looked at in table view so I assume that means they are separated by returns.

                 You are right that those were just typos in my post but were not in my actual calculation formula.

                 I am using a calculation field as c_photo_file_name where is the result is set to return as text.  Photo_tag is a text field.

                 Again, thanks for any further insight.

                 Colin

            • 3. Re: Evaluating the result of a FilterValue
              philmodjunk

                   Oh yes, I just remembered the annoying detail that is breaking your calculation. I generally avoid it with this syntax:

                   Case ( Not IsEmpty ( FilterValues( Photo_Tag; "Elevation") ) ; Direction & "Elevation" ;

                   instead of:

                   Case ( FilterValues ( Photo_Tag; "Elevation") = "Elevation¶" ; Direction & "Elevation" ;

                   All the "values" functions append a return on the end of the result--which is very annoying to allow for in your calculations.

              • 4. Re: Evaluating the result of a FilterValue
                ColinJim

                     Your the man Phil.  Thanks!