Combine 2 fields

[grecon]

Hi how can I combine two fields, I have a phone number and and extension so right now it is

PHONES.NUM PHONE.EXT

I would like to combine these so it shows in one field.

 

[grecon]

Actually this is what I want to do but I must be leaving something out cause I am getting an error remaining text doesn't appear to be part of the formula

IF Len({PHONES.PHONE_NUM})>10 then {PHONES.PHONE_NUM} else
"("&Mid({PHONES.PHONE_NUM},1,3)&") "&Mid({PHONES.PHONE_NUM},4,3)&"-"&Mid({PHONES.PHONE_NUM},7,4)
then {PHONES.EXTENSION}+""+ {PHONES.PHONE_NUM}

 

[fisheromacse]

It looks like you have your THEN and your ELSE swapped. try like below:

IF Len({PHONES.PHONE_NUM})>10 then {PHONES.PHONE_NUM} then "("&Mid({PHONES.PHONE_NUM},1,3)&") "&Mid({PHONES.PHONE_NUM},4,3)&"-"&Mid({PHONES.PHONE_NUM},7,4)
else {PHONES.EXTENSION}+""+ {PHONES.PHONE_NUM}

 

[lbass]

Try the following:

picture({phones.num},"(xxx) xxx-xxxx")+
(
if isnull({PHONES.EXTENSION}) then
"" else
"Ext "+{PHONES.EXTENSION}
)

-LB

 

[MCuthill]

There is still an error with an extra "Then" in the formula.

I am unsure why you are checking the length of phone number. I assume if >10 characters then the number is already formatted, otherwise it requires formatting (forgive me if this is an incorrect assumption).

What I am unsure on is when you wish to combine the fields and when not to... as the extension is only listed in the last ELSE, as will not be caught on the assumption above.

If you would like the extension added to all situations, this formula should work.

IF Len({PHONES.PHONE_NUM})>10 then {PHONES.EXTENSION}+""+ {PHONES.PHONE_NUM} ELSE
{PHONES.EXTENSION} & "("&Mid({PHONES.PHONE_NUM},1,3)&")" & Mid({PHONES.PHONE_NUM},4,3) & "-" & Mid({PHONES.PHONE_NUM},7,40

I hope this helps.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

LB,

Thanks! I had tried to acheive that result using "ToText" and was getting errors.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[grecon]

Thank you so much that worked perfectly: One more think how can I also ask it to choose only the Office Number: PHONES.PHONE_TYPE="O" Where would I fit this into the formula?

IF Len({PHONES.PHONE_NUM})>10 then {PHONES.EXTENSION}+""+ {PHONES.PHONE_NUM} ELSE{PHONES.EXTENSION} & "("&Mid({PHONES.PHONE_NUM},1,3)&")" & Mid({PHONES.PHONE_NUM},4,3) & "-" & Mid({PHONES.PHONE_NUM},7,40

 

[MCuthill]

grecon,

Are you wanting to limit the report to only Office Numbers or are you wanting this formula to only populate when it is an office number (and all types contained on the report)?

If you only want the report to contain Office Numbers, add this to your Record Selection Criteria: {PHONES.PHONE_TYPE}="O"

If you want the formula to only populate for those records which are an office number and are blank for the rest, modify the formula as follows:

[COLOR=red]IF {PHONES.PHONE_TYPE}="O" THEN
( [/color]
IF Len({PHONES.PHONE_NUM})>10 then {PHONES.EXTENSION}+""+ {PHONES.PHONE_NUM} ELSE{PHONES.EXTENSION} & "("&Mid({PHONES.PHONE_NUM},1,3)&")" & Mid({PHONES.PHONE_NUM},4,3) & "-" & Mid({PHONES.PHONE_NUM},7,4)
[COLOR=red]) ELSE
""[/color]

Hope this helps!



Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."