1 Reply Latest reply on Apr 17, 2015 9:34 AM by philmodjunk

    Testing field values against value list in calculated field

    AlbertVanReenen

      Title

      Testing field values against value list in calculated field

      Post

      I am setting up a calculated Postal Address field to format addresses based on the country. My calculation currently looks as follows:

      Case (
          not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ); "Countries AddressFormat Europe") ; Country ) ); List ( Address 1; Address 2; Address 3; (Postcode & " " & Upper ( Town )); Upper ( ${Province/State} ); Upper ( Country ) );
          not IsEmpty ( FilterValues ( ValueListItems ( "CB Subscriptions" ; "Countries AddressFormat Other" ) ; Country ) ); List ( Address 1; Address 2; Address 3; Upper ( Town ) & " " & Upper ( ${Province/State} ); Postcode; Upper ( Country ) );
         Country = "Australia"; List ( Address 1; Address 2; Address 3; Upper ( Town ) & " " & Upper ( ${Province/State} ); Postcode; Upper ( Country ) );
         Country = "Ireland"; List ( Address 1; Address 2; Address 3; Upper ( Town ) & " " & Postcode; Upper ( ${Province/State} ); Upper ( Country ) );
         Country = "Japan"; List ( Address 1; Address 2; Address 3; Upper ( Town ); Postcode & " " & Upper ( Country ) );
         Country = "New Zealand"; List ( Address 1; Address 2; Address 3; Upper ( Town ) & " " & Postcode; Upper ( Country ) );
         Country = "South Africa"; List ( Address 1; Address 2; Address 3; Upper ( Town ); Postcode; Upper ( Country ) );
         Country = "Spain"; List ( Address 1; Address 2; Address 3; Postcode & " " & Upper ( Town ) ; "(" & Upper ( ${Province/State} ) & " "; Upper ( Country ) );
         List ( Address 1; Address 2; Address 3; Upper ( Town ); Upper ( ${Province/State} ); Postcode )
         )

      This all works fine, apart from the first two cases: I am trying to establish if my Country field is a member of the value lists "Countries AddressFormat Europe" or "Countries AddressFormat Other", but these evaluations don't seem to be working. Is anybody able to help? I'm probably missing something...

        • 1. Re: Testing field values against value list in calculated field
          philmodjunk

          Nothing jumps out at me as incorrect syntax.

          I suggest testing this calculations in isolation where you can break them down into components to see which part of these expressions is failing. If you have FileMaker Advanced, you can use the data viewer, if Not, you can define a few calculation fields to test different parts of these expressions. Why it fails could be something as small as the space that I see after "countries" in your two value list names. If the value lists don't actually have that space in their names, the valuelistitems function will return an empty string.