Timetrak – 3CX – Batch File/Powershell script to open browser tabs anytime you answer a call.

TimeTrak is a service management software/website that ties into ERP (Accounting) packages and allows for easy coordination of staff/management of tasks. I wanted to have TimeTrak open the tab for any client I was talking to whenever I answered my phone.   My work uses 3CX to manage our telephony, and I figured that meant I could trigger things.  The below doesn’t use the 3CX API, which is paid, but instead the integration function which is free. So here’s the full detail.  This integration relies on:

• 3CX as the telephony program
• Timetrak, a Browser based service management software
• A Batch file (I couldn’t get 3CX to call a Powershell script directly)
• A Powershell Script

Steps are:

1. Add a view to the ERP database (Exo) to add phone number lookups in a consistent manner
2. Set up a Powershell file to use this lookup
3. Set up a batch file to run Powershell script and open a Browser
4. Configuring 3CX to run the batch file.

 

1. View in ERP (Exo) – X_VW_ContactFinder

I won’t go into this in much detail here, each business will need different logic, but the View just consolidates contacts out of Exo and forces choices between them.  It needs to be there for the Powershell script though.

2. Set up Powershell script for Lookup I set up the below script, here I’ve scrubbed the identifiers for the TimeTrak instance, but you can see the structure. Note this script uses Windows Domain Authentication to access the SQL Database, which means we don’t have to record any passwords in the file.

param ([string]$ID =-1,
    [string]$Name = 'Empty'
    )
$dataSource = ""  #SQL Server Name
$Database="" #Database Name
$Log='C:\Temp\PhoneNonsense.Txt'
Write-Output $ID+' '+$Name | Out-File $Log -Append
$Chrome='"C:\Program Files (x86)\Google\Chrome\Application\Chrome.exe"'
$TT='https://'+''+'/TimeTrak.aspx'
$Prefix='?clientID='
$Suffix='&tab=TabCustomFields'

$query = "SELECT ACCNO FROM X_VW_ContactFinder WHERE Number like '$ID' AND RN=1"
$connection = New-Object System.Data.SqlClient.SqlConnection
#$connection.ConnectionString = $connectionString
$connection.ConnectionString = "Server=$dataSource;Database=$database;Integrated Security=True;"
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()

$table = new-object "System.Data.DataTable"
$table.Load($result)
$connection.Close()


foreach ($row in($table))
    {
        #Assign Values
        $TTRecord=$row.("ACCNO")
        $WebLink=$TT+$Prefix+$TTRecord+$Suffix
        Start-Process $Chrome -ArgumentList "/new-tab",$WebLink

    }

3.  Write a Batch File that 3CX can call to open the Powershell script. echo %1 Echo %2 Powershell.EXE -ExecutionPolicy Bypass -File "C:\Scripts\PhoneInteract.PS1" -ID %1 -Name %Scripts You can see in the above, I don’t know how to sign my scripts properly to get past execution policies, so I’ve just brute forced Powershell into doing what I want. Definitely be careful before allowing random people the ability to execute a script file bypassing restrictions though. 4. 3CX Configuration So now the part that is just some screenshots: Open 3CX client and use the Settings button at the lower right From there choose Advanced Settings From there choose Behavior (This way I didn’t need to learn how to do .NET Scripts under Integration) Set the External Application to the Batch file you saved in 3. The Parameters to Send needs to be at least %CallerNumber% Choosing Notify when “Connected” works well if you’re answering a group-seeking queue, as it won’t open the tab if the phone is ringing for multiple people. If you’re getting calls that come to you alone, you might want to set Notify to “Ringing”, to get extra information on screen before the call starts.