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

    Data validation on import


      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" ) )




      (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



        • 1. Re: Data validation on import

          Filter doesn't do what you think it does.


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



          " 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




          (not IsEmpty(Self))