Trying to automate a search

[wolfe]

I am trying to automate a message which will pop up if you enter a social security number in a text field. The text field is more or less an area for comments and may contain anything from one or two words to a hundred or more. I want to make the automation where it will not allow the user to move on until the social security number is removed.

I tried using a pattern count like this........

PatternCount (CustomerInfo::Textfield ; /*###-##-####*/ (1 ))

but that only works when a ss number has all 1's. I tried using the @ and # symbols but FM doesn't recognize the symbol as a "wild card".

HEEELP!

[Deep Thought II]

try Validation...

[transpower]

Try PatternCount ( Customerinfo::Textfield ; "-" ) = 2. What's the likelihood of a comment containing two hyphens? If it's minimal, then this would work.

[wolfe]

Hmmmm, looks good! I'll give it a try and see what happens. I'll add my results to this string, good or bad for others who may have a similar problem.

Thanks again!

[wolfe]

Was that quick enough! Your suggestion works perfectly. Thanks a million!

PS...checks in the mail!

[wolfe]

Another problem........what if the user leaves out the dashes "-" in the social security number? As we all know, soon as you idiot proof a database, someone comes out with a better idiot! Is there a way to perform a pattern count using wild cards? When I try to use a pattern count using "#" Filemaker doesn't use the "#" as a wild card. It works okay with a "Find" script, like (Perform find "###-##-####" or "#########"). So is the "Find" the only way I can do this?

[-Queue-]

Wild cards don't work in PatternCount. Only hard-coded characters do.

You could filter the numbers and test if there are nine numbers also. Case( PatternCount( field; "-" ) = 2 or Length(Filter( field; "0123456789" )) = 9.

[wolfe]

First off......what the devil is a "hard-coded character"?

Seondly........THANK YOU VERY MUCH! The Pattern count catches the social security numbers entered with dashes and the Length/Filter combination takes care of the rest! This may not have seemed like a huge problem to some, but I think it's the little things that make the difference. Thanks again Transpower and Queue!!

[-Queue-]

Any explicitly referenced character is hard-coded; wild cards are not. Within the context of a find, "ABC" is hard-coded, while ### and * are not. PatternCount is not a grep function, so # and * will not work with it, unless you are searching for those specific characters. In fact, no FileMaker functions accept wild cards. Only a find request can use them.

[wolfe]

That's interesting to know, no wonder I couldn't get my scripts to work.

By the way, can you tell me of any way of running a script on a field exit without using one of these $100+ plug-ins?

[-Queue-]

Look here for a free one. I tinkered with it and found it to be quite cool.

[transpower]

Neat--I just downloaded EventScript and will be playing with it. I've been wanting to launch a script from a field calculation.

[wolfe]

Tnx Queue!

[wolfe]

I hate to drag this dead mouse back in the house but this simply isn't going to work. The text field I'm needing to filter is used pretty much as a comments type field and holds all kinds of information from phone numbers to employee numbers. Certain combinations of employee numbers are looking like social security numbers even if they aren't connected and seperated by several lines of text.

Also.... the two dash method is being killed by these people who refuse to use something other than a dash to enter a phone number with an area code!!!

So,

I've got a text field which holds just about anything EXCEPT a social security number and I need the ability to prevent the user from leaving the entry if he puts a SSN in it. There must be a way to do this. I told my boss there was!

Please HELP!!!!

[LaRetta]

Well Wolfe, I have an affinity for dead mice. I'm no text magician but this is how I might approach it ...

The following number calculation will fail validation if it contains a dash, two characters, dash; or you could use it to search for 0. This would establish the pattern, as phone numbers have dash, three chararacters, dash.

How to be sure the characters between the dashes are number is a problem; but maybe we can work with this position idea a bit more? GetAsNumber() should be possible to work into it and if we could use > 9 and < 100 (or JT's Filter/Length idea) to identify only 2-digit number between the dashes. Regardless, I enjoyed working on it!

not (Position(text; "-";1; 1) + 3 = Position(text; "-"; 1; 2))

LaRetta

[transpower]

With Developer 7 you could construct a recursive function for validation. The text would be read in and the farthest RIGHT word (using the RightWords function) would be checked for two hyphens in a word of length 11. If not, the function would be recursively called with one less word, until no words are left. If a Social Security number is found, the validation would fail.

[LaRetta]

Come on, Transpower! I dislike someone saying that something can be done - and getting me excited about possibilities without even providing an attempt of what it might look like. If you're going to talk the talk, at least also attempt the walk.

My calc is funky and a bit out of my league but at least I provided Wolfe with an additional idea that works. Give us an example so we can help Wolfe here; and it will help others (including me) learn as well.

LaRetta

[transpower]

LaRetta: I had to play in a tennis match this afternoon, so I didn't have the time to give the function (tennis is at least as important as FileMaker). But I'm back and here it is now:

TestforSocialSecurityNumber ( text ) =

Let ( [ wordNum = WordCount ( text ) ;

rightWord = RightWords ( text ; 1 ) ] ;

Case ( wordNum = 0 ; 1 ;

Length ( rightWord ) <> 11 ; TestforSocialSecurityNumber ( LeftWords ( text ; wordNum - 1 ) ) ;

PatternCount ( rightWord ; "-" ) <> 2 ; TestforSocialSecurityNumber ( LeftWords ( text ; wordnum - 1 ) ) ;

0

)

)

Validation for field "validationtext" =

TestforSocialSecurityNumber ( validationtext )

I would also add a custom message if the validation fails:

"You must not have a Social Security Number in this field!"

How's that?

[LaRetta]

Hi Transpower,

You have appropriately satiated my burning curiosity and have vindicated yourself but could you fix your calc (it displays wonky in a few places).

I realized later Wolfe probably couldn't take advantage of it because he doesn't have Developer. Drat!!! So, although I'm happy ... I'm unhappy that we still haven't really helped Wolfe. I know what it's like to tell owner something and then struggle to 'make it so.'

Wolfe, I might suggest to the owner that ALL important information (phone and social security) should not be held within a text field. And since you can track who modified a field (and even audit log their changes), I'd also hold users accountable when they misuse that text field in this way. No person alive believes in helping protect Users from themselves more than I do ... but sometimes we just can't. If you wish to pursue Custom Function solution, you can have someone with Developer insert it for you.

I still believe there are better regular calcs which could bring you much closer to your goal than mine. I hope the great Text Magicians on this forum read this thread and help you further. Queue!? Can you help take this further? It will remain on my plate until a better non-CF answer is given. I doubt it will come from me, however. The above pushed my envelope quite a bit as it was.

LaRetta

[wolfe]

Tnx for the posts LaRetta and Transpower, but I am working with FMPro. I am fairly new to this stuff and went with the less expensive FMPro over the Developer.

Anywayzzz, I'm thinking of trying to work around the entire number issue like this. When someone types a "social security number" into a text field, they tend to abbreviate the qualifier with "SSN", "SOC" or "SS#". I'm thinking I will just look for these qualifiers in the text and act from there. It's not fool proof, but at least most of my users can't spell Microsoft.

PS That's the reason they abbreviate "Social Security Number"!

PSS I will still check back here and see if anyone does come up with anything that I can work with....I have until January 1st!

Wolfe

[Fenton]

In case transpower has quit for the day (looks like he's in PA), my handy HTML entities says that "&#8800;" is the "not equals" sign. It was likely done by the PHP engine when he posted.

Test: ?

No, I just get ?

The SS# test seems to be for 2 dashes in a word, which seems like a pretty safe assumption. Unless someone is entering phone#'s that way; which is kind of lame anyway. I imagine the calculation could go much further into the pattern. But, 1. That's tedious, and, 2. Someone's already done it; but I can't find it offhand.

I personally don't think that you shouldn't keep SS#'s in a FileMaker database, unless you're willing to encrypt it in some manner. But that's just my opinion. If I ran a business that required it, I would want to do research or consult a lawyer about the laws concerning this; there are laws.

I've made a small example file that uses a script to check a text field, one word at a time. It could be run on modification by one of the free plug-ins (search the forums).

But personally I'd consider a Custom function to be safer and more reliable. Besides this SS# check, there are many more of them available for free, at a couple of sites. There is one that does a quick "encoding" (I have no idea how safe it is; but a heck of lot safer than none at all).

MagicEncoder, in the list at briandunning.com/filemaker-custom-functions/list.php

MagicDecoder (good idea too -)

Any business that is seriously using FileMaker, and has an in-house developer, should fork out the few hundred for Developer, IMHO.

SS_Check.zip

[TerenceM]

This is definitely an interesting challenge. Here's my stab at a non custom function solution. It basically strips everything in your text field down to a ValueList of only numbers. The ValueList is then translated into a repeating array, where each Value is tested to see if its a social security number. The final validation in the text field just sums up the repeating array, which will return the value 0 if no social security #s are found. As written, it will only test the first 20 numbers in the textfield (the length of the array). From a storage, speed, and elegance standpoint, I would go with the Custom Function approach, but if Developer isn't an option then this will probably work.

-Terence

noSS#.fp7.zip

[transpower]

LaRetta:

The not_equal sign didn't copy correctly, so I've changed it to <>, in the edited post above.

Wolfe:

You should consider getting Developer--it's worth the extra money just for situations like this. You could look for SSN, SOC, or SS# using the PatternCount function, in the meantime.

[wolfe]

Tnx to all for your assistance. Murphy, I downloaded your file and tested a little. It works perfectly to catch a SSN but it sees other numbers as SSNs which aren't. For instance, if you enter an employee number 333 and type some random text and then have to enter the employee number again 333 type more text and enter the employee number again 333, this registers as a SSN.

I haven't had a lot of time to play with the file, but soon as I do, I will post here so others might use the info.

Tnx again to all responders!

[TerenceM]

Hmmm. It probably has to do with the order in which I filtered out text and converted to the value list. Regardless, I came up with a better solution, which doesn't require extra fields. Ironically, its based on exactly where this thread started, and the reason why I started reading through the thread in the first place: using wildcards in the Position function. Sure, Position doesn't recognize wildcards, but there's no reason why you can't change all numbers to a wildcard. Try this on for size in the validation calculation:

Let ([

PunctuationToWildcard = Substitute ( InputText ; [ "

[wolfe]

Terence.......your a geneus!

This works beautifully! You need to build one of those example files that have your name and contact info on this file and replace the one in this thread. This thing could be used to filter other numbers from generic text fields as well. Seems like it's too simple, but it sure gets the job done. What else can be said except FANTASTIC JOB!!

Thank you VERY much!

Wolfe

[TerenceM]

Glad to help. I've worked it up into a custom function (but left the calculated field, for those without Developer), and posted it in the Tips section (search for "Wildcard Numbers in Position function", I'm not sure how to add a link here?). I also added Tab and Return to the first substitute function, to deal with SS#s that begin or end with a tab or return.

-Terence