1 Reply Latest reply on Apr 7, 2017 8:39 AM by philmodjunk

    Data validation on import

    Stu412

      Hi there

       

      I'm trying to import Excel files into Filemaker and ensure that two conditions are met by using validation on a particular field:

       

      1)  The field is not allowed to be blank.  If it's blank, the Excel row is considered blank and therefore it's excluded from the import;

      2)  The field should not include the words/phrases "Role" and "If applicable".  These appear as headers on the Excel sheet on rows 5 and 6 but should be excluded.

       

      I have the following as a validation formula in the field concerned:

       

      Length ( Self ) = Length ( Filter ( Self ; "Role" ) )

       

      or

       

      (not IsEmpty(Self))

       

      The (not IsEmpty(Self) part of this works fine and when I exclude the other part of the formula, blank rows are successfully removed.  I'm struggling to have it remove any rows with 'Role' and/or 'if applicable' in this particular field.

       

      Any help gratefully appreciated

       

      Thanks

        • 1. Re: Data validation on import
          philmodjunk

          Filter doesn't do what you think it does.

           

          Filter ( "a big brown dog ate an Apple" ; "cat" )

           

          returns:

          " big brown dog e n Apple"

           

          To check to see if "Role" is present in the text in a field, use PatternCount:

           

          In your case:

           

          PatternCount ( self ; "role" ) = 0

           

          or

           

          (not IsEmpty(Self))