Adding a Format to dynamic DW to do a partial SSN mask

TomBodet

unread,

Oct 13, 2006, 11:48:20 AM10/13/06

to

Hi all,

We've been required to mask out all but the last 4 digits of users
social security numbers in our app anywhere they show up.

I've found that for datawindows that are not updateable, it's easy
enough to just put in a format for the column:

"*****" + right(social_security_nr, 4)

It works great. However.

I have a datawindow that is created on the fly using SyntaxFromSQL
which contains the above social_security_nr field.

The closest I can get is based on the format example in help:

this.dw_sheet.Modify('social_security_nr.Format = "*****" +
right(social_security_nr, 4);"None"')

The problem is that the field value is not getting pulled. In the
output DW I'm just getting a column full of *****. What's the proper
way to append the asterisks to the last for characters of the actual
field value in a modify statement? (or in a setformat).

Thanks!

JKeese

unread,

Oct 13, 2006, 12:38:09 PM10/13/06

to

Could you run the sql through a function that simply
replaces the "social_security_nr" with
'*****'+right(social_security_nr,4) prior to calling the
SyntaxFromSQL ?

John

TomBodet

unread,

Oct 13, 2006, 12:44:47 PM10/13/06

to

JKeese wrote:
> Could you run the sql through a function that simply
> replaces the "social_security_nr" with
> '*****'+right(social_security_nr,4) prior to calling the
> SyntaxFromSQL ?
>
> John
>

I tried doing a physical mask in the SQL, but the problem is that the
users can select a row in the DW in order to delete it from the
database. Therefore when the window tries to delete the person (based
on the SSN) it fails since "*****4567" is not a valid SSN. The format
property looks to be exactly what I want, it's just the fact that it's
a dynamic (and generic) window that's giving me the problem.

Brad Wery [TeamSybase]

unread,

Oct 13, 2006, 1:03:01 PM10/13/06

to

Could you select the column twice? Select the column as JKeese suggested
and again as normal:

E.g.

SELECT '*****'+right(social_security_nr,4) as 'display_ssn',
social_security_nr

Set your update properties on the real column and display the fudged one
('display_ssn').

Brad

TomBodet

unread,

Oct 13, 2006, 1:07:37 PM10/13/06

to

Brad Wery [TeamSybase] wrote:
> Could you select the column twice? Select the column as JKeese suggested
> and again as normal:
>
> E.g.
>
> SELECT '*****'+right(social_security_nr,4) as 'display_ssn',
> social_security_nr
>
> Set your update properties on the real column and display the fudged one
> ('display_ssn').
>
> Brad
>

I just tried that and it looks good. However I did find that the
"delete" function in the window does not work AT ALL, even when
unmodified. So this is obviously a window that nobody ever uses since
there has not been a complaint about the bug. LOL

Regardless I'll settle for that, thanks.

If anyone does however figure out how to do the format mask above, I'd
love to see how it's composed.

philipsalgannik

unread,

Oct 13, 2006, 2:50:30 PM10/13/06

to

FWIW, if this format does work when put in thru the
datawindow painter, it MUST work if it is put in properly
dynamically with a Modify call...

M. Searer

unread,

Oct 13, 2006, 10:30:49 PM10/13/06

to

It appears to me that you need to put your expression into the expression part
of the format, the first part of the format is the static value:

this.dw_sheet.Modify('social_security_nr.Format = "~t*****" +
right(social_security_nr, 4);"None" ')

"TomBodet" <TomB...@gmail.com> wrote in message
news:1160754500.5...@i3g2000cwc.googlegroups.com...