Protecting Social Security Info

Our business has used Act for a number of years & now we're using Act 2007 Version 9. We have thousands of Social Security numbers in our database, but we have been told recently by an oversight agency that because of identity theft concerns, we can ONLY collect and keep the LAST FOUR DIGITS now. To change all of the existing Socials by hand will literally take months of long tedious sorting, typing & clicking. Our Soc. Sec. field is currently defined as "Character" with an 11 digit field length and a field format as "###-##-####" Is there a way to change the existing format and keep just the last 4 digits of the current database of numbers? Or is there a way to replace the first 5 digits with all Zeros? We have the same problem with birthdate info too as they would also like us to only keep the birth year (but they're not as worried about that...yet). Any suggestions? Thanks!

Re: Protecting Social Security Info

Well you've certainly asked for a tough one. One suggestion that may work for you is a program called Data Fields Manager. You can find it here: Data Field Manager. They have an option called Trim that may work for you. I am not completely familiar with this option and don't know what side of the data file the trim occurs on. As will all things, BEFORE you test the Addon Program, you should make a clean backup of your data.

If that does not work, you may find yourself in the position of programmatically altering the field. By this, I mean, you may need to contract a programmer to create a small program to change the data. This can be done in a couple ways: Through Act! directly or using an external program and an exported database.

Re: Protecting Social Security Info

Data Field Manager is a good tool for advanced field management in ACT!, but I believe that the Trim feature will only adjust the size of the field and then will probably only display the 1st four instead of the last digits.

There is a method you can use that utilizes the Import/Export functionality in ACT and some Excel functionality as well. Basically you will be exporting the SSN numbers to Excel, reformattng them to keep only the last 4-digits, then reimporting them back into ACT! and replacing the original SSNs. Here are the steps, but please keep in mind that the Excel portion is unsupported by ACT! as it involves functionality for a another software:

In ACT! - Exporting your contacts to Excel

- Have all user exit the program and backup your database

- Go to Tools/Preferences then click Duplicate Checking

- Make sure Record Type is Contact, then choose the Contact, Company and Phone field respectively in each of the three fields below

- Click OK, then OK again

- Do a Lookup for all contacts that have a value in the SSN field (choose Non-Empty Field from the Lookup dialog box)

- On the Contact List results, click Options in the upper right, then Customize Columns, then add the SSN, Company and Phone columns (if not already there)

- Click the Export to Excel button on the toolbar to create an Excel spreadsheet with the contacts on it

In Excel - formatting your SSN data to just show the last four digits

- Delete all columns on the spreadsheet EXCEPT the Contact, Company, Phone and SSN columns

- Make sure the numbers in the SSN column are in ###-##-#### format..if not then highlight, the column, right-click and choose

Format Cells. Choose Special, then Social Security Number

- Highlight the SSN Column, then go to the menu and click Data/Text To Columns

- On Step 1, make sure Delimited is selected...click Next

- On Step 2, uncheck any checked Delimiters and check the Other box. Put a "-" (minus sign) in the empty box next to Other

- The Data Preview should now show the numbers divided into 3 columns...click Next

- On Step 3, click inside the Destination box so you see the blinking cursor.

- Then go outside the Convert Text dialog box and click the letter of the first empty column on your spreadsheet. For example, if your Contact, Company, Phone and SSN are in columns A, B, C, and D then click the E column to highlight it.

- After you do this, then look back in the dialog box and look at the Destination field...it should read $E:$E (assuming column E)

- Click Finish and you should see the 3 parts of the SSN now in 3 separate columns

- Delete the original SSN column and the 2 columns that have the first 3 and second 2 numbers in it... leaving just Contact, Company and the last 4 of the SSN

- Rename the header of this column to same thing as the SSN field in ACT!

- Go to File/Save As, change Save As Type to CSV (Comma Delimited) and save it to My Documents

- Exit Excel

In ACT! - Import data back into ACT and replace full SSNs with modified SSNs using merge

- Go to Tools/Define Fields, choose your SSN field and click Edit Field. Change the format from ###-##-#### to just ####.

WARNING: After this is done, you will lose a portion of your Social Security Numbers. If you want to retain the full SSN for the contacts for your records, then make sure you have a backup of your database or Export the contacts to Excel and save that spreadsheet.

- After you save this change, you will notice that the data in the SSN fields has changed or been removed (scary, I know, but expected - this is why you made a backup)

- Go to File/Import..click Next

- Screen 2....change file type to Text Delimited

- Click Browse to go to your .csv file your saved earlier in Excel. On the Open screen, changed the File Type to Comma Separated Values, then browse to where you saved the .csv file and double-click. When you return to the Import screen, click Next

- Screen 3....Make sure Contact Records is selected. Click Next.

- Screen 4....Make sure Comma is selected and that the checkbox is empty. Click Next

- Screen 5....Leave as is and click Next

- Screen 6...Make sure the all fields on the left (your .csv file) match up to the all fields on the right (ACT! database). Click Next.

- Screen 7...Click the Contact button. On the Merge Options Screen, change the value of the field on the right to "Merge". Click Ok. Click Next

- Screen 8...Click Finish to perform the import. This will merge the .csv and ACT! data together. There should be no duplication of contacts

- What you will see is that the SSN fields for each contact will now be populated with the 4-digit modified SSN from the .csv file

I know this is a lot of steps and will probably take an hour or more to do, but it is quicker than changing the numbers one at a time.

Please share your success or questions on this process with the community.

Message Edited by ghollister on 08-21-2008 05:20 PM

Greig Hollister

Note: Effective 6/1/13, Sage no longers provides support for the Act! software. This is now provided by Swiftpage.