Solved

Return Result Based on Area Code in Phone Number

Posted on 2003-11-20
28
321 Views
Last Modified: 2013-12-18
How would I create a field that returned a result based on the first 3 digits of a phone number?

There are techs in my group that are based on regions.  I want to create a field in my calltrack form that would read the first three digits of a phone number and return the name of the person who supports that individual region.

Any ideas?
0
Comment
Question by:brianbailey
  • 13
  • 10
  • 3
  • +2
28 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9789816
Create a view with Phone Numbers as the I Sorted Column but only take first 3 digits out of it like this

@Left(PhNumber; 3);

Now in your PersonList field (I believe it is a dialog list) enter this formula as choices

t := @DBLookup( "":"NOCache"; @DBName; "PhoneListView"; @Left(Phone;3); "Persons");
@IF(@iserror(t); ""; @Unique(t));

~Hemanth
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9789844
Create a form named TechPerson. Create a view TechPersons with first column sorted by field TechPersonAreaCode. Put TechPersonName into second column

formula for your field:
@DBLookup("":"NoCache";"";"TechPersons";TechPersonAreaCode;2)
0
 

Author Comment

by:brianbailey
ID: 9791344
OKay...I'm sorry I must apologise.  A novice here, but getting a little better.

Hemanth: Where does your solution pull the list of Technician names from?  What do you mean by PersonList field?

ksi: would your solution involve filling out the form for each techperson?
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9791517
yes, both my and Hemantha's solutions (they are the same) require documents for each techperson, or for each region.

this is because you should store somewhere the relationship betwen area code and person.


0
 

Author Comment

by:brianbailey
ID: 9791568
There is no way to indicate in the code that:

If these area codes, then populate this name; if these area codes then populate THIS name; and these area codes populate this name.

A coworker of mine was able to create a view similar to this, only viewing a list of area codes in his region.  Can something similarly be done to indicate the one of 3 names that would populate based on area code without having to save approx 150 documents?
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9791722
so you want to store this relationship in code. This is possible of course.

tmp:=@Left(PhoneNumber,3);
@If(tmp="Name1";"Region1";tmp="Name2";"Region2";tmp="Name3";"Region3";"Unknown name")

you can use this as a column formula in a view. Three leftmost charachters are assumed to be area code, so if you're using different format for the phone number you need to fix it a little bit.
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9791740
typo fixed:

tmp:=@Left(PhoneNumber;3);
@If(tmp="Name1";"Region1";tmp="Name2";"Region2";tmp="Name3";"Region3";"Unknown name")
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9791962
oops. sorry. It's too late :-) Names are actually area codes and Regions - tech names :-)

here is the right one

tmp:=@Left(PhoneNumber;3);
@If(tmp="001";"Name1";tmp="002";"Name2";tmp="003";"Name3";"Unknown name")
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9792263
and if you have multiple area codes per one name then

code:=@Left(PhoneNumber;3);
zone1:="001":"002":"003";
zone2:="004":"005":"006";
zone3:="007":"008":"009";
@If(code=zone1;"Name1";code=zone2;"Name2";code=zone3;"Name3";"Unknown name")
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 9795150
on target
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 9795158
You don't need 160 documents.  You only need one per person.  Create a form with only two fields, as desicribed earlier -- PersonName, and AreaCodes.  Make the AReaCodes field multivalued (nornally, the separator would be a comma).

Now, you can create a record for the technician that has his/her name, and ALL the area codes covered by that person.  You just enter multiple area codes in a single field.

The lookup view shoudl work as follows:

Column 1 contains the area code field.  It is sorted, and has EITHER sort-categorized on, OR has show multiple values as separate entries on.  Either will do.

Column 2 is name, no need to sort.

Now, you can lookup a name by area code, since each area code has its own view entry, even though there is only one document for many of them.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9797423
too fast .. Anyway everyone covered the basics for you.
0
 

Author Comment

by:brianbailey
ID: 9797784
Okay...

I used Ksi's code for multiple area codes.  Just one more thing to make it perfect.

It fills in on information based on when the document is created.  Since the phone number is not entered until the user is editing it, I would need that field to update when the document is saved.  Once that is in, I am set.
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9798205
If you placed my formula into field on your form, then go to field properties and set the field type to Computed.

I thought you' are going to place the formula into view column.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:brianbailey
ID: 9798250
Okay...one last thing...I promise.

In rare cases, the area code will not match with the actual field rep, and will need to be manually changed.  I set "Computed" but doing so makes it uneditable.  How can I make it so editted text will override the computed value?
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 9798300
Have two fields one for display purpose and other for edit ... In hide formula for computed for display field

@IsMember(@left(PhoneNumber;3); zone1:zone2:zone3)

For edit field

@IsNotMember(@left(PhoneNumber;3); zone1:zone2:zone3)

0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9798325
create another editable field. 'manualName'

change formula in the computed field:

code:=@Left(PhoneNumber;3);
zone1:="001":"002":"003";
zone2:="004":"005":"006";
zone3:="007":"008":"009";
@If(code=zone1;"Name1";code=zone2;"Name2";code=zone3;"Name3";manualName)

now if the code is unknown, the content of manualName field will be used.  
0
 

Author Comment

by:brianbailey
ID: 9798374
Okay...let me explain why I am doing this.  Sometimes there are companies based in NY, but they have people using the product all over the country.  Even though the client may be in California, the tech would be the NY based tech.  This is a situation where I would need to be able to manually change the name of the tech from one name to another, and have it stick.  Using the following suggestion, how can I make this work without having to add extra fields:

code:=@Left(PhoneNumber;3);
zone1:="001":"002":"003";
zone2:="004":"005":"006";
zone3:="007":"008":"009";
@If(code=zone1;"Name1";code=zone2;"Name2";code=zone3;"Name3";"Unknown name")

Basically, I just want to be able to override the field with one of the other two tech names if need be.
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9799294
you can not change the content of the computed field directly. you have to create another field for this. Here is another try

code:=@Left(PhoneNumber;3);
zone1:="001":"002":"003";
zone2:="004":"005":"006";
zone3:="007":"008":"009";
@If(manualName<>"";manualName;code=zone1;"Name1";code=zone2;"Name2";code=zone3;"Name3";"Unknown name")

Now if the content of the manualName field is not empty it will be computed as value of the techName field. You can hide the content of computed field if manualName is not empty.

The interface could be nicer if using hidden ManualName field and a dialog to populate it, but it's a little bit more difficult.





0
 

Author Comment

by:brianbailey
ID: 9799337
So...can I do the following?

Have the two fields invisible, one that automatically calculates and one that allows edits.  Then, have a new field that references one of the two invisible fields, the one that calculates if the editable field is empty, or the editable one if it is not empty.  Then if it needs to be changed, have a button or something that allows the invisible editable field to be populated with one of 3 names?

So, two fields, both invisible: TechCalc and TechEdit.  The visible field, TechVis, will transpose TechCalc if TechEdit is empty, and will transpose TechEdit if there is data in that field.  Then add a button to allow a dialog to choose the name for TechEdit.

Make sense?
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9799716
yes it will work. However you will need to have an ability to clean this field and return to computed value. Here is the final solution.

checkbox IsOverride "Override tech name"

TechEdit field hidden when checkbox is not selected   @If(IsOverride="";@True;@False)

TechCalc field hidden when checkbox is selected  @If(IsOverride="";@False;@True)

formula for the TechCalc

code:=@Left(PhoneNumber;3);
zone1:="001":"002":"003";
zone2:="004":"005":"006";
zone3:="007":"008":"009";
@If(IsOverride="";@If(code=zone1;"Name1";code=zone2;"Name2";code=zone3;"Name3";"Unknown name");TechEdit)





0
 

Author Comment

by:brianbailey
ID: 9800029
I feel so stupid.  Maybe my version is really old, but I cannot find a way to add a checkbox.  Where would I normally find it?

Is this a possibility:

TechCalc is invisible if TechEdit has text
TechEdit is invisible if Techedit has no text

I really appreciate this.  
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9800135

you are probably using R4.  I don't have on hand but it should be field type keywords and then there is an additional control in a field properties window to select the subtype - checkboxes, radiobuttons, etc.


as for

TechEdit is invisible if Techedit has no text

this means you will never see this field

0
 

Author Comment

by:brianbailey
ID: 9800278
OKay...added an override button, and put in the code.  However, the check will not stay once the document is saved.  It disappears. ???
0
 
LVL 2

Expert Comment

by:ksi2001
ID: 9800319
Weird enough.  Is it editable field?  Have you put any value for choices?
0
 

Author Comment

by:brianbailey
ID: 9800339
I think it wants choices of some kind.  When I put true and false into the choices section, it worked, but it threw two checkboxes in, one labeled true and the other label false.  I would prefer if those didn't show up.
0
 
LVL 2

Accepted Solution

by:
ksi2001 earned 500 total points
ID: 9800353
right. Instead of labeling field with a separate text, put this text into choices window.

just write there - "Override tech name".
0
 

Author Comment

by:brianbailey
ID: 9800465
Thank you so much for being patient.  Works perfectly.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now