Dismiss Notice
We would like to remind you that we’re updating our login process for all 3CX forums whereby you will be able to login with the same credentials you use for the Partner or Customer Portal. Click here to read more.

Working with 3CX Phone System Database

Discussion in 'CRM / Helpdesk / App Integration' started by igor.snezhko, May 31, 2014.

Thread Status:
Not open for further replies.
  1. igor.snezhko

    igor.snezhko Active Member

    Joined:
    Jan 7, 2008
    Messages:
    668
    Likes Received:
    7
    Hi!
    I'm still novice with VAD. I have a request from a customer to build a 'call stickness' script. This script shouild check the incoming internal Caller ID and route this call to internal employee (Extension) that has dialed this external number.
    So, if a customer calling back to the common company's number, it will be routed to employee that called him before.

    [​IMG]

    The queue3cxdatabase looks like this

    [​IMG]

    [​IMG]

    This is a queue

    Code:
    SELECT
      callhistory3.from_no
    FROM
      public.callhistory3
    WHERE
      callhistory3.dialednumber LIKE '%{0}' AND
      callhistory3.is_fromoutside = 'FALSE'
    ORDER BY
      callhistory3.idcallhistory3 DESC
     LIMIT 1
    And the queue is working well

    [​IMG]

    But at the debugging stage I'm not getting any results.

    [​IMG]
     
  2. VAD_Support

    VAD_Support Active Member

    Joined:
    Aug 6, 2009
    Messages:
    690
    Likes Received:
    0
    Hi Igor,

    How are you getting the query result in the conditional component below?

    In this case where the query returns just a single value, you can use "Scalar" type, and then you get the value using ScalarResult. Query is most often used when the query returns a table of values.

    Kind regards.
     
  3. igor.snezhko

    igor.snezhko Active Member

    Joined:
    Jan 7, 2008
    Messages:
    668
    Likes Received:
    7
    Yes, thank you!
    I just changed to ScalarResult and receive the right Extension number. But I still cannot understand which exactly Scalar value VAD takes. How does it works?

    That's strange, but today a got same result by using this method

    Code:
    GET_TABLE_CELL_VALUE(queue3cxdatabase.QueryResult,'0','0')
    This code simpy takes value of the first cell, isnt it?

    Another issue I noticed is a delay between the actual call and the 3CX database refresh. So, if two extensions call to the same external number, and then this number quickly calling back,it is possible that call will be routed to the "first" extension, and not to the "last" extension.
     
  4. igor.snezhko

    igor.snezhko Active Member

    Joined:
    Jan 7, 2008
    Messages:
    668
    Likes Received:
    7
    I just got idea, why my first experiment failed. This is because Extensions are calling national and local numbers in different way. National is 10 digits long, and local is 6 or 7 digits long.

    But the external number CallerID I always received in national format. So, some local number I tried first simply not produced right database queues.

    Example: I receive (query) CallerID 0567265546, but 3CX database contains only 7265545.

    Now I check (query) last 6 digits only and this seems ok.

    [​IMG]
     
  5. VAD_Support

    VAD_Support Active Member

    Joined:
    Aug 6, 2009
    Messages:
    690
    Likes Received:
    0
    Hi Igor,

    The ScalarResult is the value at the first row and the first column. So you can get the same using Query type and getting the value with the GET_TABLE_CELL_VALUE function.

    Let me know if you need additional assistance with this.

    Kind regards.
     
Thread Status:
Not open for further replies.