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