VM PRO Database function

[shib1148]

i have a list of 3000 exchanges (area code and next 3 digits of a telephone number), in an CSV file, that need to be routed to 3 separate hunt groups.

for example:
Exchanges 1-1000 should go to HG1
Exchanges 1001-2000 should go to HG2
Exchanges 2001-3000 should go to HG3

I assume that I can use VMPRO and the database function to achieve this but i can't seem to figure out the flow, syntax.

Can anyone help?

I asked our inside telephone vendor but he says that Avaya has never done this and we need to go out to another 3rd party to get this done.

This does not seem logical to me.

Any help would be appreciated.

 

[tlpeter]

Sure you can do this but i think you do not need vmpro for this.

Use the incoming CLI in the incoming call route to route the calls to the right groups.

If i understand correctly you do not need 3000 entries.


Homo sapiens non urinat in ventum

honey, i fried the IP Office !!!

 

[amriddle01]

Indeed, if the incoming CLI's from the certain excahnges are similar in some way i.e they all start 400xxx then Incoming call route will do this no problem, also VM if you prefer but that would be more work

ACSS Small and Medium Enterprise (SME) Communications

I've just bought a brand new car and on the way out of the dealership the engine seized, they want £2000 paid in advance to look at it which they will keep if they can find a way to blame me for it......Remind you of anything

 

[TaureanDragon]

amriddle: Agreed can be done using the Incoming call route, however disagree that it will be more work via the VmPro (eg Route by Variable Action or appropriate setting of the Menu Action assuming using the auto attendant to route)

 

[amriddle01]

I meant using the database scenario as first suggested would require more work, also less points of failure

ACSS Small and Medium Enterprise (SME) Communications

I've just bought a brand new car and on the way out of the dealership the engine seized, they want £2000 paid in advance to look at it which they will keep if they can find a way to blame me for it......Remind you of anything

 

[amriddle01]

Without wanting to derail the thread, did you go ahead with your business venture TD?

ACSS Small and Medium Enterprise (SME) Communications

I've just bought a brand new car and on the way out of the dealership the engine seized, they want £2000 paid in advance to look at it which they will keep if they can find a way to blame me for it......Remind you of anything

 

[TaureanDragon]

re using the database I'll concede, it would be more work. I had just viewed it from a pure VmPro perspective.

Re the business venture, am working on getting it off the ground, though an active, time bound, contract is consuming most of my time for the moment. Will let you know once I’m up and running.

 

[shib1148]

I don't mind more work initially, so long as it saves me time in the long run.

Does anyone know how I would get the dB option working?

 

[TaureanDragon]

The method I had in mind did not use the DB, it was as follows using your example numbers of:
Exchanges 1-1000 should go to HG1
Exchanges 1001-2000 should go to HG2
Exchanges 2001-3000 should go to HG3

And assuming the first four digits are passed in the $CLI, use the Auto Attendant with a Variable Routing action.

Have the Select a Variable set to '$CLI'

Then add the following
3000*
2000*
1000*
2???*
1???*
Ok the action

Then Connect the Action exits as follows:
3000* to a transfer to HG3 Action
2???* to a transfer to HG3 Action
2000* to a transfer to HG2 Action
1???* to a transfer to HG2 Action
1000* to a transfer to HG1 Action
No Match to a transfer to HG1 Action

Though all calls not matching any of the other criteria will be routed to HG1 if set this way.

 

[shib1148]

hmmm that's an interesting option. I try it out.

 

[TaureanDragon]

Just Curious, Did you get this resolved?

 

[shib1148]

Thank you for checking in, I did not get this resolved just yet.

Not sure if the variable Routing will work well for us as we have several hundred different exchanges that need to be entered. It's come cases it seems as if every other exchange goes to HG1.

For example.

484524 -> HG 1
484525 -> HG 2
484526 -> HG 1
484527 -> HG 2


The database route seems to be the best solution for initial setup and future maintenance.

I was hoping to make a dB, in Access, with 3 tables. Each table will contain only the exchanges for the desired HG.

Table 1 Table 2 Table 3
484123 484124 484125
484126 484127 484128
484129 484130 484131
484132 484133 484134


Then have VMPRO compare the incoming caller id's first 6 digits to Table 1, 2 and 3 and route appropriately.

For example.

Call comes in

VMPRO will compare first 6 digits with Table 1, if it matches then transfer to HG 1
If it does not match then compare with Table 2, if it matches then transfer to HG 2
If it does not match then compare with Table 3, if it matches then transfer to HG 3
If it does not match then then transfer to Operator.

The trouble we're running into, I think, is we can't figure out how to write a query to JUST look at the first 6 digits of the incoming caller ID.

Maybe the Access is not the best tool to use for the dB. I'm open to suggestions.

 

[kholladay]

Well I hate access to begin with. I'd suggest SQL Express and SQL Management Studio but that is just me. I also wouldn't break it up into multiple tables if you didn't have to. Best option:

tblExchanges
colExchange colGroup
484123 1000 (or whatever HG1 is)
484124 2000 (or whatever HG2 is)
484125 3000 (or whatever HG3 is)

SELECT colGroup FROM tblExchanges where LEFT (colExchange, 6) = $CLI

Then in your transfer set the destination to $DBD[0]

Alternatively if you must have multiple tables you can have your Database Get Data's Empty and At End options connect to a query that targets table 2 and then table 3.

Kyle Holladay
ACA-I, ACA Call Center, ACS-I, ACS-M,
ACE Implement: IP Office
TIA-CTP, MCP/MCTS Exchange 2007
Adtran ATSA, Aruba ACMA

"Thinking is the hardest work there is, which is the probable reason why so few engage in it." - Henry Ford

 

[TaureanDragon]

>>>The trouble we're running into, I think, is we can't figure out how to write a query to JUST look at the first 6 digits of the incoming caller ID.

You could use the Generic Action to copy the first 6 digits from the $CLI variable by:
Either use the free format string as
STRING:$CP0=left($CLI,6)
or the 'Select Generic command' as
Select Generic command = 'String Manipulation'
Select Variable = '$CLI'
Select Variable for result = $CP0
Select Operation = 'From the Left'
Number of Characters = 6

The first six digits of the $CLI variable will now be in the $CP0 variable which you could use for your database lookups.

 

[kholladay]

Ahh, in re-reading yes you are correct. If you want to modify the $CLI and not the table content you need to strip the information from the $CLI using TaureanDragon's method.

Kyle Holladay
ACA-I, ACA Call Center, ACS-I, ACS-M,
ACE Implement: IP Office
TIA-CTP, MCP/MCTS Exchange 2007
Adtran ATSA, Aruba ACMA

"Thinking is the hardest work there is, which is the probable reason why so few engage in it." - Henry Ford

 

[tmcd123]

Where do you input the Generic Action? We can't seem to get it right.
Here is our setup:
Start point to Database open
Sucess at DB open goes to Database Execute
- In specific tab, we have:
SELECT colGroup FROM tblExchanges where (Col Exchange = $CLI) From what I read above, should this be: SELECT colGroup FROM tblExchanges where LEFT (colExchange, 6) = $CLI? If I change it to this, it will get past this. If I keep it how I have it, it goes to my DB Execute failure.
If it is a sucess, we have it going to Database get Data where our specific is - Retrieve the next item in the list.
The success here goes to a transfer with specific tab $DBD[0]. It never is a success at this point though.

 

[intrigrant]

That is because the SQL Wizard create a wrong SQL query for csv files.
Open excel and create a SQL query to the csv file, copy and past that query in the SQL Wizard, then it will work.