Scripting Questions 1

[nawtykitty]

With some help from various other users, I have come up with the following script to remove hyphens from a social security field called P_SSN in a DBase table (the name of the process script is noHyphen):
;*********code for a script

method run(var eventInfo Event)

cmProcessMain()

endMethod

;******************************
method cmProcessMain()

var
strOrigSS,strNewSS string
tc tcursor
endvar

tbl = ":work:abraxport.dbf"

if not tc.open(tbl) then
errorshow("open tc error&quot
return
endif

tc.edit()

scan tc :

strOrigSS = tc."OrigSsFieldname"

if cmNoHyphen(strOrigSS,strNewSS)
then tc."NewSsFieldname" = strNewSS
else
msgInfo("Data odd : ",strOrigSS)
endif
endscan

tc.endedit()
tc.close()
endMethod

;*********************************

method cmNoHyphen(strOrig string, var strNew string) logical

;111-22-3333
; should be 9 + 2 = 11 long

if strOrig.size() <> 11 or
strOrig.substr(4,1)<> &quot;-&quot; or
strOrig.substr(7,1)<> &quot;-&quot;
then
return(false)
endif

stNew = strOrig.substr(1,3) + ; 111
strOrig.substr(5,2) + ; 22
strOrig.substr(8,4) ; 3333
return(true)

endMethod

I get an error of 'Cannot Change a Built-In Header' at the second instance of cmProcessMain()(in bold). What am I doing wrong? Also, how the heck do I embed a paradox 9 script into a DBase table? Is it possible. I have been working on this for work all day, and my brain is fried. New insight would be great

 

[footpad]

nawtykitty,

I'm not certain what's causing your error message, but I suspect there's a hidden assumption in your code that I'm just not seeing this early in the morning. (I have seen the error before, but don't recall the cause.)

In looking at the basic approach you've taken, though, I notice that your procedure is very tied to the default layout of SSN's; that is, they're looking for hyphens at specific character positions. While that's probably the case in most cases, there's no guarantee that it's the case in all cases.

Here's a different version of the same script that will remove all hyphens from a given string, regardless of where they're located:

var
   tc  tCursor   ; Points to data
   ls,           ; Left side of match
   rs,           ; right side of match
   str String    ; SSN placeholder
endVar

   if not tc.open attach( ":work:abraxport.dbf" ) then
      errorShow( "Can't Attach to Data", "Use [>>] for details..." )
   else

      scan tc :

         str = tc."SSNField"
         while str.match( "..-..", ls, rs )
            str = ls + rs
         endWhile

         if tc.&quot;SSNField&quot; <> str then
            tc.edit()
            tc.&quot;SSNField&quot; = str
            tc.unlockRecord()
            tc.endEdit()
         endIf

      endScan

      ; always do this, especially in older Paradox versions
      if tc.isAssigned() then
         tc.close()
      endIf

   endIf

In this approach, we strip hyphens from the underlying SSN until none are found. This will work if there's one, two, or even no hyphens in the field. value.

You cannot embed scripts into tables, as if they were stored procedures in a client/server database. Unfortunately, neither the Paradox nor dBASE file formats allow that.

Instead you create scripts that use (or open) tables. Thus, you'll need to run your script on a regular basis.

While this can become tedious, it's the way things work with most local database formats.

One way to prevent that tedium, however, is to avoid the need for it. Instead of allowing users to enter SSN's containing hyphens, create a custom data entry form and then add a picture to the SSN field object that does not accept non-numeric characters.
Depending on the version of Paradox you're using, you may need to create and save a custom picture statement. The following walkthrough shows how to do this using Paradox 9. (If you're using a different version, you may need to &quot;fudge&quot; certain steps:

1. Open your existing form in Design mode.

2. Select the SSN field, right-click it, and then choose Properties from the Shortcut menu.

3. When the Property sheet appears, choose the Picture Tab and then choose Add Custom Picture. This displays the Picture Assistant dialog box, which lets you design and test new picture validation strings.

4. Click Add and notice that focus moves to the Pictures list. At this point, you need to name your picture; call yours &quot;SSN Test 1&quot; (no quotes, please).

5. When you've entered the name, click on the Picture Code edit box and then type the following:

[*9#]

6. Now, choose the OK Button and notice that the Field Properties dialog shows your new picture statement as the current picture. Choose OK to apply and close the properties dialog.

7. Next, save your form, run it, and then add a few test values. As you do this, you should notice the following:

-- You can only enter numbers into the SSN Field and you must either enter all nine digits or leave the field blank.

-- Previous values are not affected; that is, pictures are not applied to existing data. You need to use scripts like the one you've already createed to &quot;fix up&quot; existing data.

However, once you've added the picture and fixed up the data, you shouldn't need to run the script again--provided that all data values are entered through your data entry form.

I suspect, though, that your table is being produced by another program, perhaps one that extracts query results from a different database. If this is the case, then you'll need to run the script each time you extract the data. You may be able to prevent that by talking with the administrator of that other database. If you can get the admin to strip the hyphens for you, that would save you the work of having to run the script each time you need those results.

In any event, I hope this helps...

-- Lance

 

[nawtykitty]

I have gone over the reply and will try this out tonight. Hopefully all will go well. Will update the thread on my progress. I really appreciate your thorough response and the great tip pf running a picture as well.

 

[nawtykitty]

I am sory for the lateness of my progress post. However, I had abandoned my previous script process for a new one. With various tweaking and help I have developed the following working script(The name of my database file was &quot;Abraxport.DBF&quot; and the field that I needed to have the hyphens removed was P_SSN) :

Under the Proc Window:

proc nohyphen()

var nohyphen tcursor tc, x,y string
endvar
nohyphen.open(&quot;abraxport.DBF&quot
nohyphen.edit()

scan nohyphen for nohyphen.p_ssn.match(&quot;..-..&quot;,x,y):
nohyphen.p_ssn = x + y

endscan
nohyphen.endedit()

endproc

Under the Run Window:

method run(var eventInfo Event)

nohyphen()

endMethod


I have to run the script twice, but it removes all hyphens from my existing data under the P_SSN field. I hope this helps other people, and I am grateful for everyone's help and suggestions from this forum.