Formatting telephone number on NAB via AGENT

I need an agent that will format the current telephone numbers in the name and address book (NAB) of our domino server. The format should be (123) 123-1234. The agent needs to be able to run manually or automatically.
padillrrProject CoordinatorAsked:
Who is Participating?
 
ZvonkoConnect With a Mentor Systems architectCommented:
Ok, this is my Agent code:

FIELD Type:=Type;
@If(Type!="Person";@Return("");"");
FIELD OfficePhoneNumber:=OfficePhoneNumber;
phoneNum := @ReplaceSubstring(OfficePhoneNumber;"-":".":" ";"");
@If(@Length(phoneNum)<9;@Return("");"");
@SetField("OfficePhoneNumber";@Left(phoneNum;3)+"-"+@Middle(phoneNum;3;3)+"-"+@Middle(phoneNum;6;9));
SELECT @All

0
 
Sjef BosmanGroupware ConsultantCommented:
And what do they look like now? To define a process with a certain output, a description of the input would be nice...

Sjef
0
 
padillrrProject CoordinatorAuthor Commented:
the numbers are in there in different formats 1231231234 or 123-123-1234 or 123.123.1234 the users can enter the data so it's difficult to ensure the same format, I have a script that will cahnge it upon entering but I got this after we had the inforamtion entered, so any entries from here on will make the change on the fly but I still have entires that need to be changed.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CRAKCommented:
But always 10 digits? With only a few other characters like ( ) - . and spaces?
Never "+nn" (country code) etc?
0
 
padillrrProject CoordinatorAuthor Commented:
there are numbers with +nn and other country codes which I would like to remain as they are simply the US numbers need to be formatted. The company field can be the KEY field. It distinguishes the business units, so all with company field XXX could work
0
 
ZvonkoSystems architectCommented:
Do you like to normalize only the OfficePhoneNumber?
Or also OfficeFAXPhoneNumber, CellPhoneNumber, PhoneNumber_6, PhoneNumber(Home), HomeFAXPhoneNumber
0
 
Sjef BosmanGroupware ConsultantCommented:
Zvonko, instead of the @ReplaceSubstring, I'd probably use
    phoneNum := @Implode(@Trim(@Explode(OfficePhoneNumber;"-.:+() ")));

I think the Trim isn't even required.

Errors in the next line corrected:
    @SetField("OfficePhoneNumber";@Left(phoneNum;3)+"-"+@Middle(phoneNum;4;3)+"-"+@Middle(phoneNum;7;9));

0
 
ZvonkoSystems architectCommented:
Your last formula did invent an error. My version works correct.
The same is with your explode/implode proposal: it works only once! the second time call you get blank gaps!
0
 
Sjef BosmanGroupware ConsultantCommented:
Hi Zvonko,

According to the Help, @Middle's second parameter is an offset. Counting characters in a string starts at 1. You need positions 1, 4 and 7, with 3 characters each. What's wrong??

And please enlighten me on the gaps I would get the second time? Why is that so?
0
 
Sjef BosmanConnect With a Mentor Groupware ConsultantCommented:
Blast, one thing forgotten in the Implode:
    phoneNum := @Implode(@Trim(@Explode(OfficePhoneNumber;"-.:+() ")); "");
0
 
ZvonkoSystems architectCommented:
sjef, it is a big difference to say "This is my proposal:"  and to say: "Errors corrected:"
And I surely have not to enlighten you ;-)
You are approved Expert enough to put your proposal in Designer and give it a try.
At that occasion you can test my proposal too and see whether it works for you as good as it works for me ;-)

0
 
Sjef BosmanGroupware ConsultantCommented:
Touché.
0
 
ZvonkoSystems architectCommented:
:-)

Sorry, I was also not toooo polite :)
0
 
Sjef BosmanGroupware ConsultantCommented:
And you're right :( I should have known. There's one line in the Help for @Middle that I always forget:
    The middle begins one character AFTER the offset
I thought I studied the examples in the Help carefully, I seem to have mixed them up. Sincerest apologies! So the offset really starts at zero, whereas the offset in the Mid-function in LotusScript starts at .... 1! How nice!

"Corrections" in the line removed:
    @SetField("OfficePhoneNumber";@Left(phoneNum;3)+"-"+@Middle(phoneNum;3;3)+"-"+@Middle(phoneNum;6;9));

But the Implode/Explode stuff works, don't it? I think it's easier to maintain that string than a stringlist.

Sjef



0
 
ZvonkoSystems architectCommented:
Of course is your way with the explode more convenient to maintain then my List of single characters.
And this one was really Experts exchange when we can come back to things that make fun too us ;-)
0
 
padillrrProject CoordinatorAuthor Commented:
Hello Gentlemen, first thank you so much for your efforts, I have created the agent and this is what I am using:
FIELD Type:=Type;
@If(Type!="Person";@Return("");"");
FIELD OfficePhoneNumber:=OfficePhoneNumber;
phoneNum := @Implode(@Trim(@Explode(OfficePhoneNumber;"-.:+() ")));@If(@Length(phoneNum)<9;@Return("");"");
@SetField("OfficePhoneNumber";@Left(phoneNum;3)+"-"+@Middle(phoneNum;3;3)+"-"+@Middle(phoneNum;6;9));SELECT @All

What it is currently doing is formating the international numbers by deleting the + and adding - between the 2nd and third number and the 3rd number after that example it was +65 1231231234 now it's 65-123-1231234 did I miss something?
0
 
Sjef BosmanGroupware ConsultantCommented:
I absolutely HATE @Middle...

Zvonko, it's all yours! The code says clearly Left(.., 3), so how on earth only 2 characters (65) came out, I honestly don't know.
0
 
Sjef BosmanGroupware ConsultantCommented:
You could add a @Prompt to the Agent, just before the @SetField, and make sure you run it on one document only.
    @Prompt([Ok]; "PhoneNum"; "<" + phoneNum + ">")
The <> are there to see if there are any invisible characters in the string.
0
 
padillrrProject CoordinatorAuthor Commented:
So the agent would look like this? This would then run on one documnet at a time? Is this going to add the (123) between the first 3 numbers? Sorry guys but I'm really clueless here....Is there anything else I have to set in order to make this work?

FIELD Type:=Type;
@If(Type!="Person";@Return("");"");
FIELD OfficePhoneNumber:=OfficePhoneNumber;
phoneNum := @Implode(@Trim(@Explode(OfficePhoneNumber;"-.:+() ")));@If(@Length(phoneNum)<9;@Return("");"");
@Prompt([Ok]; "PhoneNum"; "<" + phoneNum + ">")
@SetField("OfficePhoneNumber";@Left(phoneNum;3)+"-"+@Middle(phoneNum;3;3)+"-"+@Middle(phoneNum;6;9));SELECT @All
0
 
Sjef BosmanGroupware ConsultantCommented:
The Prompt is only meant to display the phoneNum variable before it gets transformed with "-" characters. Ah, you left a semicolon out.

    phoneNum := @Implode(@Trim(@Explode(OfficePhoneNumber;"-.:+() ")));@If(@Length(phoneNum)<9;@Return("");"");
    @Prompt([Ok]; "phoneNum"; "<" + phoneNum + ">");
    @SetField("OfficePhoneNumber";@Left(phoneNum;3)+"-"+@Middle(phoneNum;3;3)+"-"+@Middle(phoneNum;6;9));SELECT @All

I assume you added this agent to the Actions list, and selected "run on selected documents". Then, if you are in the N&A-book in the users view, select one user and click Actions/YourAgent. It will first prompt the phoneNum variable and then it will change the OfficePhoneNumber field. phoneNum should be displayed as <12345678999>. If not, then there's something wrong.
0
 
padillrrProject CoordinatorAuthor Commented:
I did just that and it did display the number in < > and it did change the format from 123.123.1234 to 123-123-1234 all it's missing is to add the ( ) around the first 3 numbers ie (123) 123-1234.
0
 
padillrrProject CoordinatorAuthor Commented:
This formula works on the fly in a field can it be used as an agent:

phone := @Trim(extension);
phonestripped :=
   @Trim(@Implode(@Explode(@LowerCase(phone); "et- ()./+"); ""));
   @If(@Length(phonestripped) = 10; "(" + @Left(phonestripped; 3) + ") " +
   @Right(@Left(phonestripped; 6); 3) + "-" + @Right(phonestripped; 4); phone)
0
 
Sjef BosmanGroupware ConsultantCommented:
The @Right(@Left..) I don't understand, @Middle will do just fine (even though I don't like it ;). You can adapt Zvonko's agent with the code above, no problem. Are you sure that there are always 10 digits?
0
 
padillrrProject CoordinatorAuthor Commented:
Only on the US numbers the international numbers have the +nn. I wish I could explain the code to you but I am not a coder, this was dropped on my lap when our developer left......how do I incorporate this code or this one:
phone := @Trim(Field Name);phonestripped :=
   @Trim(@Implode(@Explode(@LowerCase(phone); "et- ()./+"); ""));
   @If(@Length(phonestripped) = 10; "(" + @Left(phonestripped; 3) + ") " +
   @Right(@Left(phonestripped; 6); 3) + "-" + @Right(phonestripped; 4); phone)

into an agent....could you send me the format please..........I'm really in need of a fix here.
0
 
padillrrProject CoordinatorAuthor Commented:
Gentlemen,
This worked:
FIELD Type:=Type;
@If(Type!="Person";@Return("");"");
FIELD OfficePhoneNumber:=OfficePhoneNumber;
phoneNum := @Implode(@Trim(@Explode(OfficePhoneNumber;"-.:+() ")); "");
@If(@Length(phoneNum)<9;@Return("");"");
@SetField("OfficePhoneNumber";"(" +@Left(phoneNum;3)+")"+@Middle(phoneNum;3;3)+"-"+@Middle(phoneNum;6;9));
SELECT @All

I'd like to give you guys parial credit how do I do that? you can split the 500 pts. just let me know how I can do that!
0
 
ZvonkoSystems architectCommented:
Please do me the favor and give the points to sjef_bosman.
Cheers,
Zvonko
0
 
Sjef BosmanGroupware ConsultantCommented:
If you give me Zvonko's points, then give him mine :)

His first solution works, no discussion about that. He deserves the majority of the points. You asked the question, it up to you to award points. To split, or not to split, ...

Success!
0
 
ZvonkoSystems architectCommented:
If you insist on that :-)

Here is the discription for points split: http:help.jsp#hi19
0
 
CRAKCommented:
Great teamwork guys! Loved the show!
0
 
ZvonkoSystems architectCommented:
:-)
0
 
CRAKCommented:
I think EE should have some kind of a fair-play award!
0
 
ZvonkoSystems architectCommented:
As you stated that I already got one :-)

But hey, come on, the main reward spending time and efforts here on EE is to get good feedbacks. And so far I cannot complain.

But padillrr, please make it fifty-fifty ;-)
0
 
padillrrProject CoordinatorAuthor Commented:
Thanks guys, you are truly experts. I have learned a great deal from this interaction, I am even more pleasantly impressed at the truely profeesional manner in which both of you interacted and had the patience with a novice such as myself. I will spli the points 50/50 and hope to work with you guys again in the future, if your ever in florida look me up!
0
 
Sjef BosmanGroupware ConsultantCommented:
Florida? That's where the swamps and the crocs and the tornadoes are? It's good to see them on tv, but that's close enough! ;) Mmmm, to see a live tornado... Must be awesome.

I'll keep your invitation in mind, but please update your profile so we can reach you. Thanks for the points!! Any reason for you to give a B, was there something wrong?? Not completely satisfied?
0
 
ZvonkoSystems architectCommented:
Thanks for the kind feedback.
But please be aware that experts are irritated by grading B (mostly)
Because usually grading B is given only if you get disappointed by the experts after several requests for clarification.
If you agree that your grading can be corrected, then I think it will not be a problem for the Page Editor CRAK to adjust your grading to Excellent! ;-)

(And we know that grading recommendations are confusing :)
0
 
padillrrProject CoordinatorAuthor Commented:
Thatnks guys, and I'm truly sorry, your right about the recommendations a little confuisng, If crak would be so kind as to correct that for me. You guys did a great job and an A is well deserved! Don't worry about the crocs, the hurricanes blew them away :-) If you do visit Florida just saty away during hurricanes season July- 1st Nov it should be safe!
0
 
ZvonkoSystems architectCommented:
Thanks :)
0
All Courses

From novice to tech pro — start learning today.