Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Testing for only letter characters in a field


Jim McKee

Recommended Posts

Within a validation script, I need to test whether a field (requiring exactly 4 characters) contains only uppercase or lowercase letters in the range A to Z. If any other character (number, space, punctuation mark, symbol, etc.) resides in the field, the test would return "true" and the user shown a dialog alerting them to fix their entry.

 

I thought it would be a matter of testing for the ASCII Code values of each of the 4 characters, but I can't figure out how to do this.

 

Anyone got a calc in their toolbox for this test?

 

BTW: is there an ASCII Code test capability hidden somewhere in FM6? A search on "ASCII" in the online help yields nothing helpful. Also nothing applicable in the FileMaker K-Base.

 

TIA for your help.

Link to comment
Share on other sites

Try this calculation in combination with selecting Maximum Characters of 4 on the validation tab ..

 

If( ( Middle( text , 1, 1 ) < "a" or Middle( text , 1, 1 ) > "z")
or ( Middle( text , 2, 1 ) < "a" or Middle( text , 2, 1 ) > "z" )
or (Middle( text , 3, 1 ) < "a" or Middle( text , 3, 1 ) > "z" )
or ( Middle( text , 4, 1 ) < "a" or Middle( text , 4, 1 ) > "z") , "0" , "1" )

Link to comment
Share on other sites

Works this way too

 

Test_For_Number = Calculation =

PatternCount( Left(First Name,1) <= 9,1) or 
PatternCount(Middle(First Name,2 , 1 ) <= 9 ,1 ) or 
PatternCount(Middle(First Name,3 , 1 ) <= 9 ,1 ) or 
PatternCount(Middle(First Name,4 , 1 ) <= 9 ,1 ) 

Since numbers , special characters and spaces and carriage returns are less than letters.

using

in this new field. To be valid the Test_For_Number field must = 0

Link to comment
Share on other sites

Re: Since numbers , special characters and spaces and carriage returns are less than letters.

 

Good point Chopper, that means this should work as well ...

 

If(  Middle( text , 1, 1 ) < "a"   or  Middle( text , 2, 1 ) < "a"
 or Middle( text , 3, 1 ) < "a"  or Middle( text , 4, 1 ) < "a" , 0 , 1 )

You might need to swap the resulting zero or one depending on whether you use the calculation in a seperate field or in the Validate by Calculation within the field definition.

 

Once again so many ways to get to the finish! Now it's time to take efficiency into account ...

Link to comment
Share on other sites

Chopper ...

 

Thanks.

 

This works gr8, except for the characters

 

: ; ? @ (which in the ASCII table fall between the "9" and the "A",

 

and the characters

 

[ ] ^ _ ` (which in the ASCII table fall between the "Z" and the "a").

 

And then there are all those other pesky characters above the "z" in the ASCII table. :eek:

 

I'm working on a better solution. Film at 11! cool.gif

 

Jim

Link to comment
Share on other sites

Chopper ...

 

I think I came up with a comprehensive validation scheme, but it's just a bit complex.

 

I usually have a Utility file in all of my solutions. This file always contains only one record. I use it for things like counter fields to assign (and increment) codes and for container fields for graphics used throughout the system.

 

Here's my validation solution:

 

In the Utility file, create a 'Constant' field (calculation = 1), and a text field (not a global). Call the text field 'match field'. In this field put each letter of the alphabet (uppercase only is fine) on its own line with a carriage return. like this:

 

A

B

C

.

.

Z

 

In the database where the field being validated resides (let's call it Main.fp5), create 4 calculation fields -- one for each of the 4 characters in the field I'm validating.

 

c_position1 (Calculation, Text) = Left(field, 1)

c_position2 (Calculation, Text) = Middle(field, 2, 1)

c_position3 (Calculation, Text) = Middle(field, 3, 1)

c_position4 (Calculation, Text) = Right(field, 1)

 

Then, create 4 relationships in the Main file. Each relationship is from one of the above calculation fields to the 'match field' in the Utility file.

 

position1test: c_position1 = ::match field Utility.fp5

position2test: c_position2 = ::match field Utility.fp5

position3test: c_position3 = ::macth field Utility.fp5

position4test: c_position4 = ::match field Utility.fp5

 

Finally, create a calculation field which will perform the validation:

 

c_test_result (Calculation, Number) =

 

Case(

 

IsValid(position1test::Constant) and

IsValid(position2test::Constant) and

IsValid(position3test::Constant) and

IsValid(position4test::Constant), 0,

 

1)

 

So if one or more of the four characters in the field I'm validating is not found in the contents of the 'match field' in Utility.fp5, c_test_result returns a "1" (true), and the user receives an alert that their entry is invalid.

 

A key requirement of this method is that the Utility file always has only 1 record. Otherwise, the relationships will fail and you will always get a "1" result.

 

One final thing: it doesn't matter if the user enters lowercase letters. FileMaker will return a "true" when matching from a lowercase letter in the field being validated to the 'match field', which contains only the Uppercase letters. If you want only Uppercase letters as the final field entry, a Set Field (field, Upper(field)) script step will take care of that.

 

I dunno ... this might be like going from Miami to New York via San Francisco, but it does completely prevent the user from entering ANY character outside the range of "A" thru "Z".

 

If anyone wants the example files, email me.

 

Thanks.

 

Jim McKee

aka: dataminer1

Link to comment
Share on other sites

Hi Jim,

I believe you can do this rather more simply with a validation formula along the lines of:

 

not TextToNum("A" & TextField) and not WordCount("A" & Substitute(Substitute(TextField, ".", "|"), "-", "|") & "A") > 1

...which exploits some of the inbuilt 'sensory capabilities' within FMP, rather than trying to 'reinvent the wheel'... wink.gif

Link to comment
Share on other sites

That works OK for straight ASCII.

 

How about Central European characters which are 8 bit as in Mac CE or Windows 1250 character set?

Link to comment
Share on other sites

Hi Anatoli,

I confess I've not looked into ways to implement that technique for the Central European character sets you mention, though in priniciple I can see no reason why it shouldn't be workable along similar lines.

 

Did you have a specific problem in mind, or was your question of a more general nature? smile.gif

Link to comment
Share on other sites

Ray ...

 

Thanks a gazillion for your short and sweet calculation. It works perfectly, and it's exactly what I was looking for, rather than my convoluted solution.

 

Can you explain a little bit how your calculation actually works? For the life of me, I can't figure out why you used the specific substitutions you did.

 

Thanks again.

 

Jim McKee

aka: dataminer1

Link to comment
Share on other sites

Originally posted by CobaltSky:

[qb]Hi Anatoli,

I confess I've not looked into ways to implement that technique for Central European character sets you mention, though in priniciple I can see no reason whjy it shouldn't be workable along similar lines.

 

Did you have a specific problem in mind, or was your question of a more general nature? smile.gif [/qb]

Ray, it was just small teaser because you always surprise me with your elegant solutions, which are working beautifully. I don't have right now such problem, but I was thinking -- what the heck, I might need one in future so let Ray work for extra minute and have his elegant solution ready for the future smile.gif

That's all smile.gif

Link to comment
Share on other sites

  • 2 weeks later...
Originally posted by Anatoli:

[qb]Ray, it was just small teaser...[/qb]*/snip

Oh, OK Anatoli. smile.gif I'll let you know if something pops into my head. Meanwhile, thanks for the compliment!

 

Originally posted by JimMcKee:

[qb]Can you explain a little bit how your calculation actually works?...[/qb]*/snip

Hmmn... the explanations are always longer than the calculations themselves smile.gif

 

The first part of the calc relies on the fact that TextToNum( ) returns only the numeric component of a text string, to deal with numbers.

 

The second part of the calc utilizes the built in capability of FileMaker to detect words based on non alphanumeric characters being interpreted as word separators. However in some circumstances, particular characters such as hyphens and periods are not treated as word separators, so the trick is to substitute any occurrences of those characters with something that will be treated as a word-separator (such as a pipe character in the above example).

 

The includion of an "A" character before/after the evaluation strings ensures that:

 

1. FileMaker will not interpret the words 'TRUE" or "YES" (or any other strings starting with "T" or "Y") as numeric, since it generally applies boolean logic to these text values and evauates them as "1"

 

2. A non-alphanumeric character occurring at the start or end of the string will be detected as a word separator and will push the word count over 1 just as it would if it occurred in the middle of the string (otherwise it would be ignored by the WordCount( ) function).

 

It's a bit of an exercise in smoke and mirrors, but it sure beats separately testing each letter in the field against every letter of the alphabet individually. wink.gif

Link to comment
Share on other sites

Ray ...

 

Thanks much for the explanation. Without a doubt my weakest skill area in FileMaker is Text functions. So, when I see things like nested Substitute functions, my left-brain shuts down and all I can do is stare at the calculation and admire how elegant it is ... :rolleyes:

 

Jim McKee

aka: dataminer1

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use