Sql lookup phone number to customer

Discussion in 'Call Flow Designer' started by deanril@yahoo.com, Jun 13, 2017.

Thread Status:
Not open for further replies.
  1. deanril@yahoo.com

    Joined:
    Oct 27, 2016
    Messages:
    51
    Likes Received:
    1
    Hi,

    I want to use the Call Flow Designer to take the callers number and do a db (external sql db)lookup if the customer has a record (sales order)with us already.

    Then I want a way to output that to the 3cx client that yes this is customer #123 SO# 12345.

    Any examples of this, is this possible?

    Thanks!
     
  2. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,084
    Likes Received:
    77
    Hi,

    Please take a look at the following articles that explain how to perform the validation against a database:
    https://www.3cx.com/docs/cfd-creating-phone-support-portal-1
    https://www.3cx.com/docs/cfd-creating-phone-support-portal-4

    If you also need that 3CXPhone knows the customer ID when receiving the call, you will need a CRM plug-in for that. The CFD can't change any data from the SIP call, so it's not possible to manipulate the caller ID for example to show the customer number. What you can do is performing the lookup in a CRM plug-in and show the data to the agent in a screen pop-up for example. More info on creating CRM plug-ins can be found here: www.3cx.com/blog/releases/crm-integration-diy/

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. deanril@yahoo.com

    Joined:
    Oct 27, 2016
    Messages:
    51
    Likes Received:
    1
    Thank you I will look into all that!
     
  4. VictorSP

    Joined:
    Sep 6, 2016
    Messages:
    61
    Likes Received:
    10
    Hello,

    I'm in the same situation, trying to make an scenario where the app checks the CallerID on a MySQL database, but it doesn't work fine. Checking 3CX logs I see errors I don't know how resolve them (see attach file).

    Caller = 30502
    Queue (app) = 80009
    SQL statement = SELECT destino FROM clientes WHERE id

    In the SQL table we have two columns, first with id (callerid) and second with destination extension. In this call example it must be redirected to ext 10190.

    Could you help me, please?

    Bye
     

    Attached Files:

    • log.txt
      File size:
      13.8 KB
      Views:
      3
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,084
    Likes Received:
    77
    Hi @VictorSP

    First, please note that MySQL is NOT currently supported. You configured your connection for SQL Server, but that will not work if your database is MySQL.

    Then, if you can migrate your database to SQL Server or PostgreSQL (these are the supported databases), then you will need to take a look at your query, which is wrong:
    SELECT destino FROM clientes WHERE id

    The WHERE clause is not specifying a boolean condition. You need to change it for example to something like this:
    SELECT destino FROM clientes WHERE id = '123'

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    VictorSP likes this.
  6. VictorSP

    Joined:
    Sep 6, 2016
    Messages:
    61
    Likes Received:
    10
    Hi @edossantos, I found later MySQL is not supported so finally I've installed a MS SQL Server 2014 in our lab and migrated the bbdd. The exact SQL statement I've used is:

    Code:
    SELECT destino FROM clientes WHERE id={0}
    It's working fine! Now testing more scenarios. ;) Thanks for your help!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Thread Status:
Not open for further replies.