formatting imported data

[phillip dziewior]

Can anyone tell me if a character string such as a phone number no matter what format it is originally in, be reformatted to the following format such as ( 000) 000-0000 without losing the original data. I have attempted to use the transform function on a practice database and the original data was lost. Again any and all help is greatly appreciated.

[Tom Cone Jr]

Phillip,

show us some sample phone numbers and the transform expression you're using.

tell us how and where you're trying to do the transformation

describe the sequence you are following

-- tom

[phillip dziewior]

Formatting data imported from another database

Okay, the information is imported from a access database into A5V7, then I have gone into the field rules of each of the four phone number fields and selected simple mask and select phone. Then I would use the update records function and use the expression: Transform(Home_phone, "@R (999) 999-9999"). A sample of what I start with is attached.

[Peter.Greulich]

Can anyone tell me if a character string such as a phone number no matter what format it is originally in, be reformatted to the following format such as ( 000) 000-0000 without losing the original data.

Yup, I have a simple function that does that.

[phillip dziewior]

And what function would that be???

[Peter.Greulich]

And what function would that be???

It's not an Alpha function, it's a home made one.

[phillip dziewior]

Okay if it is not an Alpha function and it is one you created, then what does it cost me to find out what this function is because dependent on the cost I may want to use it as I have about 1100 phone numbers that I really do not want to re-enter manually.

[Peter.Greulich]

Okay if it is not an Alpha function and it is one you created, then what does it cost me to find out what this function is because dependent on the cost I may want to use it as I have about 1100 phone numbers that I really do not want to re-enter manually.

No cost, you just had to ask. Here is the code [if you want to insert the (area code parenthesis) you will need to modify the function. If you don't know how, let me know and I'll do it for you]:

'Date Created: 13-Jan-2005 11:04:47 AM
'Last Updated: 30-Aug-2005 02:27:44 PM
'Created By  : PG
'Updated By  : PG

FUNCTION funcPhoneTransform AS C (vPhoneNo as C, vMask=0 as N )
    ''Transform a phone no by stripping out non-alpha/non-numeric characters
    ''Thus allowing pasting of odd phone nos into a phone_no field w. a mask
    ''e.g. If vMask=0 ===> "123-456(78.9.0" ===> "1234567890"
    ''e.g. If vMask=1 ===> "123-456(78.9.0" ===> "123-456-7890"
    
    Dim vPhoneNo as C
    Dim vPhoneNo2 as C
    vPhoneNo = ALLTRIM(Remspecial(vPhoneNo))
    
    vLen = LEN(vPhoneNo)
    FOR i = 1 TO vLen
        IF isdigit(substr(vPhoneNo,i,1)) = .t.
            vPhoneNo2 = vPhoneNo2 + substr(vPhoneNo,i,1)
        END IF
    next
    vPhoneNo = vPhoneNo2

    IF vMask=0
        funcPhoneTransform = vPhoneNo
    ELSE IF    vMask=1
        vPhoneNo = Left(vPhoneNo,3) + "-" + substr(vPhoneNo,4,3) + "-" + right(vPhoneNo,4)
        funcPhoneTransform = vPhoneNo        
    END IF
END FUNCTION

I'll post this as an attachment in the code archive...

[crimmelcp]

This function would not be hard to write in Alpha X-Basic.
At most you would have 10 or 20 different seniaros.
(),No (),spaces,dashes


Charlie Crimmel

[phillip dziewior]

Peter, Thank you for all of your help with this particular question and if it is not too much trouble, could you please edit the expression to insert the area code parenthesis. I hate to ask but until I found and started using Alpha5 I have never even heard of xbasic, and now I have to find reference material on this type of code so I can try to figure out how to write expressions for myself. Until then I'm stuck having to bother others, like yourself for help. Thanks again

[brett s]

Peter, Thank you for all of your help with this particular question and if it is not too much trouble, could you please edit the expression to insert the area code parenthesis. I hate to ask but until I found and started using Alpha5 I have never even heard of xbasic, and now I have to find reference material on this type of code so I can try to figure out how to write expressions for myself. Until then I'm stuck having to bother others, like yourself for help. Thanks again

Phillip, check out this link to obtain a copy of the A5 Xbasic reference manual documentation:

http://www.alphasoftware.com/support...umentation.asp

Xbasic is IMHO the best part of A5.

[Peter.Greulich]

if it is not too much trouble, could you please edit the expression to insert the area code parenthesis.

Use the following expression example:

funcPhoneTransform("1234567890",2) = "(123) 456-7890"

Here is the revised function:

'Date Created: 13-Jan-2005 11:04:47 AM
'Last Updated: 30-Aug-2005 02:27:44 PM
'Created By  : PG
'Updated By  : PG

FUNCTION funcPhoneTransform AS C (vPhoneNo as C, vMask=0 as N )
    ''Transform a phone no by stripping out non-alpha/non-numeric characters
    ''Thus allowing pasting of odd phone nos into a phone_no field w. a mask
    ''e.g. If vMask=0 ===> "123-456(78.9.0" ===> "1234567890"
    ''e.g. If vMask=1 ===> "123-456(78.9.0" ===> "123-456-7890"
    ''e.g. If vMask=2 ===> "123-456(78.9.0" ===> "(123) 456-7890"
    
    Dim vPhoneNo as C
    Dim vPhoneNo2 as C
    vPhoneNo = ALLTRIM(Remspecial(vPhoneNo))
    
    vLen = LEN(vPhoneNo)
    FOR i = 1 TO vLen
        IF isdigit(substr(vPhoneNo,i,1)) = .t.
            vPhoneNo2 = vPhoneNo2 + substr(vPhoneNo,i,1)
        END IF
    next
    vPhoneNo = vPhoneNo2

    SELECT
        CASE vMask=0
            funcPhoneTransform = vPhoneNo
        CASE vMask=1
            funcPhoneTransform = Left(vPhoneNo,3) + "-" + substr(vPhoneNo,4,3) + "-" + right(vPhoneNo,4)
        CASE vMask=2
            funcPhoneTransform = "(" + Left(vPhoneNo,3) + ") " + substr(vPhoneNo,4,3) + "-" + right(vPhoneNo,4) 
	CASE else
	    funcPhoneTransform = "Error"         
    END SELECT
END FUNCTION

[crimmelcp]

Phillip
Here is the same concept using buttons on a form

Charlie Crimmel

[phillip dziewior]

Peter, Thanks for your help this does the trick.

[phillip dziewior]

Charlie, thanks for the additional help as I can use this as well.