assigning extension from sql database query

Discussion in 'CRM / Helpdesk / App Integration' started by zman574, Jul 8, 2016.

Thread Status:
Not open for further replies.
  1. zman574

    Joined:
    Jun 24, 2010
    Messages:
    57
    Likes Received:
    0
    I have created a call flow that will successfully verify a customer from caller id via a sql database

    The functionality is simple

    When call comes in go directly to verify if the callerid exists in a sql database

    If it exist look for the assigned agent from the last call by finding the extension of that agent in the database.

    Everything seems to work as it should until I assign the query result of my query to get the agents direct extension

    Here is my query

    DECLARE @maxid INT

    SET @maxid =
    (SELECT MAX(ID)
    FROM [CRM].[dbo].[CallLog]
    WHERE Callerid = 2075708994)-- RIGHT({0}, 10))

    SELECT LEFT(DirectCallExtension,4) As DirectCallExtension
    FROM [CRM].[dbo].[CallLog]
    WHERE ID = @maxid
    AND DirectCallExtension IS NOT NULL

    The result is a single 4 digit nchar of 7201 which is the extension I want.

    I am pretty sure through my testing that the query is fine however when I add the variable assignment it makes the whole thing not work and just disconnects the call. I have attached a screenshot of the Expression Editor where I am assigning the value to the Variable

    Really stuck on this and have a hard deadline to meet and support is not responding.

    Thanks

    Jim
     

    Attached Files:

    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  2. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,372
    Likes Received:
    110
    Hi Jim,

    I have answered you to this via the support system. First suggestion is using query type "Scalar" instead of "Query" as the result is a single value. Next, we need to check the errors you get, either in the file 3CXIvrServer.log or Errors_DatabaseAccess.log, as explained here:
    http://www.3cx.com/blog/docs/troublesho ... lications/

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. Cees Rijken

    Joined:
    Apr 7, 2017
    Messages:
    3
    Likes Received:
    0
    Hi Jim,

    I am a newbie in the 3CX area, but a rather seasoned DBA. How did you get this part working:

    "When call comes in go directly to verify if the callerid exists in a sql database "

    Cheers,

    Cees
     
Thread Status:
Not open for further replies.