• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Return Result Based on Area Code in Phone Number

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
brianbailey
Asked:
brianbailey
  • 13
  • 10
  • 3
  • +2
1 Solution
 
HemanthaKumarCommented:
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
 
ksi2001Commented:
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
 
brianbaileyAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ksi2001Commented:
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
 
brianbaileyAuthor Commented:
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
 
ksi2001Commented:
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
 
ksi2001Commented:
typo fixed:

tmp:=@Left(PhoneNumber;3);
@If(tmp="Name1";"Region1";tmp="Name2";"Region2";tmp="Name3";"Region3";"Unknown name")
0
 
ksi2001Commented:
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
 
ksi2001Commented:
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
 
madheeswarCommented:
on target
0
 
qwaleteeCommented:
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
 
HemanthaKumarCommented:
too fast .. Anyway everyone covered the basics for you.
0
 
brianbaileyAuthor Commented:
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
 
ksi2001Commented:
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
 
brianbaileyAuthor Commented:
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
 
HemanthaKumarCommented:
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
 
ksi2001Commented:
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
 
brianbaileyAuthor Commented:
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
 
ksi2001Commented:
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
 
brianbaileyAuthor Commented:
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
 
ksi2001Commented:
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
 
brianbaileyAuthor Commented:
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
 
ksi2001Commented:

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
 
brianbaileyAuthor Commented:
OKay...added an override button, and put in the code.  However, the check will not stay once the document is saved.  It disappears. ???
0
 
ksi2001Commented:
Weird enough.  Is it editable field?  Have you put any value for choices?
0
 
brianbaileyAuthor Commented:
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
 
ksi2001Commented:
right. Instead of labeling field with a separate text, put this text into choices window.

just write there - "Override tech name".
0
 
brianbaileyAuthor Commented:
Thank you so much for being patient.  Works perfectly.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 10
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now