Data validation

[Garry Flanigan]

I wrote the little function below to remspecial() on a list of fields prior to the record being saved. If users paste text to a character field that includes a crlf() it is accepted by Alpha and later can cause problems, particularly when you export the data, so this is a way to remove all non-alphabetic and non-numeric characters from lots of fields in one hit.

FUNCTION RemoveSpecial AS C (fields as c)
' fields is a comma separated list of object names in the form to be processed
	f = parentform.this
	count = f.children()
	for i = 1 to count
    	object = f.child(i)
	    if object.type() = "C" then
	    	name = object.name()
	    	if inlist2(fields,name) then
			    evaluate_template(name+".value=remspecial("+name+".value)")
		end if
	    end if
	next i
END FUNCTION

Hopefully others find this of use.

Garry

[Stan Mathews]

Re: Data validation

After some side discussion with Garry I borrowed/stole his idea and modified his function to leave the punctuation but remove everything outside of the ascii 32 to 126 range. By changing the ranges in the for...next loops one could tailor the function to remove or include other characters.

This might be useful to someone who wanted to validate text that contained dashes, colons, etc.

FUNCTION RemoveSpecial AS C (fields as c)
	' fields is a comma separated list of object names in the form to be processed
	finds = "" 'create list of "special characters" to be removed
	           'ascii 0 to 31 and 127 to 255
	FOR i = 0 TO 31
		finds = finds + crlf()+ chr(i)
	NEXT i
	FOR i = 127 TO 255
		finds = finds + crlf()+chr(i)
	NEXT i
	
	f = parentform.this
	count = f.children()
	FOR i = 1 TO count
		object = f.child(i)
		IF object.type() = "C" THEN
			name = object.name()
			IF inlist2(fields,name) THEN
				evaluate_template(name+".value=stritran_multi("+name+".value,finds,\"\")")
			END IF
		END IF
	NEXT i
END FUNCTION

[Garry Flanigan]

Re: Data validation

Stan's suggestion is certainly an improvement on my original idea as it will more thoroughly remove unwanted characters.
I then thought that sometimes we may want to remove other characters that are within ascii 32 to 126 such as a space, comma (as it messes up exports in csv files), quotes, asterisks and question marks.
(The OnKey event is ideal to prevent unwanted characters being typed into fields, but in this day of cut and paste, OnKey is not sufficient.)
So below is a modified version:

FUNCTION RemoveSpecial AS L (fields as c,exclude as c)
	' fields is a comma separated list of object names in the form to be processed
	' exclude is a string of additional characters to exclude
	finds = "" 'create list of "special characters" to be removed
	           'ascii 0 to 31 and 127 to 255
	FOR i = 0 TO 31
		finds = finds + crlf()+ chr(i)
	NEXT i
	FOR i = 127 TO 255
		finds = finds + crlf()+ chr(i)
	NEXT i
	if len(exclude)>0 then
	    for i = 1 to len(exclude)
	    	finds = finds + crlf() + substr(exclude,i,1)
	    next i
	end if
	
	f = parentform.this
	count = f.children()
	FOR i = 1 TO count
		object = f.child(i)
		IF object.type() = "C" THEN
			name = object.name()
			IF inlist2(fields,name) THEN
				evaluate_template(name+".value=stritran_multi("+name+".value,finds,\"\")")
			END IF
		END IF
	NEXT i
END FUNCTION

If you want to do it on all character fields (no need to specify list):

FUNCTION RemoveSpecial2 AS L (exclude as c)
	' removes all characters outside ascii 32-126 and in exclude string from all character fields
	' exclude is a string of additional characters to exclude
	finds = "" 'create list of "special characters" to be removed
	           'ascii 0 to 31 and 127 to 255
	FOR i = 0 TO 31
		finds = finds + crlf()+ chr(i)
	NEXT i
	FOR i = 127 TO 255
		finds = finds + crlf()+ chr(i)
	NEXT i
	if len(exclude)>0 then
	    for i = 1 to len(exclude)
	    	finds = finds + crlf() + substr(exclude,i,1)
	    next i
	end if
	
	f = parentform.this
	count = f.children()
	FOR i = 1 TO count
		object = f.child(i)
		IF object.type() = "C" THEN
			name = object.name()
			IF inlist2(fields,name) THEN
				evaluate_template(name+".value=stritran_multi("+name+".value,finds,\"\")")
			END IF
		END IF
	NEXT i
END FUNCTION

As you may want to warn the user that you have changed the value in a field, here is a version to do it on just a string.

FUNCTION RemoveSpecial3 AS C (instring as c,exclude as c)
	' exclude is a string of additional characters to exclude that are within ascii 32 to 126
	finds = "" 'create list of "special characters" to be removed
	           'ascii 0 to 31 and 127 to 255
	FOR i = 0 TO 31
		finds = finds + crlf()+ chr(i)
	NEXT i
	FOR i = 127 TO 255
		finds = finds + crlf()+ chr(i)
	NEXT i
	if len(exclude)>0 then
	    for i = 1 to len(exclude)
	    	finds = finds + crlf() + substr(exclude,i,1)
	    next i
	end if
	RemoveSpecial3 = stritran_multi(instring,finds,"")
END FUNCTION

Once again, thanks Stan.

Garry