Calculation Help

[Joseph31]

Hi all,

Does anyone have any sugestions for this?

I need to make a unique code out of a SSN. What I would like to do is this:

Example:

249-65-4465

I would like to take the first 3 numbers of the SSN and convert them into letters

2=B,4=D,9=I this then would equal BDI

I would like to keep the next two digits

then I would like to multiply the last 4 digits by 9

So the final answer would be:

BDI-65-40185

Can someone show me how to do this in a calculation.

Or can someone show me how to make a identifier out of a SSN.

Thank you

[LaRetta]

Well here's one way. I think Position() can be used to improve it. I'll post a better calc if I can think of one:

Choose ( Left ( SSN ; 1 ) ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "I"; "J" ) &

Choose ( Middle ( SSN ; 2 ; 1 ) ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "I"; "J" ) &

Choose ( Middle ( SSN ; 3 ; 1 ) ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "I" ; "J" ) &

Middle ( SSN ; 4 ; 4 ) & Right ( SSN ; 4 ) * 9

You didn't identify 0. So 0 will be A. I modified the calc to add J because of it. If you want 0 to appear as J, just put J first (1 position, 0 result) then start A in position 2 (1 result) etc.

Oh. And it will break without the dashes. It might be best to use the dashes as an anchor. Let me know if the SSN format is exactly as you've specified (and is being validated at the data-entry level).

LaRetta

Edited October 17, 2005 by Guest

[LaRetta]

It's really a shame that the Choose() position results can't be specified as multiline text or another calculation or even a repeater.

I have run into this frequently ... wanting to specify the Choose() results, starting at position 0 and even extending to infinity. I realize I can use fields between each position but that still feels like hard-coding to me and it is limited to the number of results you specify.

L

[Joseph31]

Thank you very much... How do I put the dashes in my calculation?

Joseph

[Joseph31]

Got it... Thank you very much both of you.

:

Joseph

[LaRetta]

Thank you very much both of you.

?? I realize I have split personality sometimes but ony one of me was out and about yesterday! Honestly!

Well, to end the thread in case someone else gets to this point and then scratches their head, I'll finish it. I added a bit of protection also (concerned that the field may not be validated:

If (

PatternCount ( SSN ; "-" ) or Length ( SSN ) ≠ 9 ; "ERROR" ;

Choose ( Left ( SSN ; 1) ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "I"; "J" ) &

Choose ( Middle ( SSN ; 2 ; 1) ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "I"; "J" ) &

Choose ( Middle ( SSN ; 3 ; 1 ) ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "I" ; "J" ) &

"-" &

Middle ( SSN ; 4 ; 2 ) &

"-" &

Right ( SSN ; 4 ) * 9

)

Or can someone show me how to make a identifier out of a SSN.

Although I've never done it, some just use the SSN as the unique ID. I would never trust User entry to create unique IDs. And although you are modifying it, it will be entered by a User initially, right? That is my concern and suggestion to heavily validate. But if User enters SSN (and it is unique itself), then why modify it further? I'm just curious as to the purpose here.

LaRetta

[-Queue-]

I would use something more like

...

Middle( "JABCDEFGHI"; Left( SSN; 1 ) + 1; 1 ) &

Middle( "JABCDEFGHI"; Middle( SSN; 2; 1 ) + 1; 1 ) &

Middle( "JABCDEFGHI"; Middle( SSN; 3; 1 ) + 1; 1 )

...

which also accounts for '0' appropriately.

You would want to tweak your Choose a bit also, LaRetta, or else 0 becomes A, 1 becomes B, etc.

[LaRetta]

I was hoping you'd show up and present a better way, JT!

Middle() here? I tried working it with Position(). Hmmmm ... back to the whiteboard.

LaRetta