How to do customer lookup then insert customer ID into caller ID

I am looking to do this:

  1. Caller calls in to main IVR and is prompted to press “1” if existing customer
  2. Existing customer gets transferred to custom application and gets prompted to enter their 10-digit account number
  3. PBX looks up that account number from spreadsheet/csv to find the matching customerID (about 5 digits long)
  4. PBX transfers caller to ring group and replaces the caller’s callerID name field with the customerID so that the agent can right away see what customer it is about through the short approx. 5-digit customerID number which is in the callerID name field.

How to do this? I can make the spreadsheet into sql DB if needed

You can do it with DynRoutes:

Or, if you are familiar, then a custom dialplan.

Thanks, Dynamic Routes was the perfect way to do this.
Added a database to the mariadb already setup on FreePBX, configured the dynamic route and used the destination as Set Caller ID. Then in Set Caller ID I referenced the variable in the caller ID name field and it is correctly looking up and displaying everything now. This great lookup automation will save a lot of time.

1 Like

Looks good. Be aware that the sql integration in dynroutes currently relies on deprecated Asterisk MySQL applications that are removed in Asterisk 19, you will not be able to upgrade beyond Asterisk 18 while this limitation exists in the module.

The cidlookup module has be fixed already for this, it now uses an AGI instead of the deprecated applications. It would probably not be all that difficult to leverage the cidlookup module agi in dynroutes.

1 Like

Another option would be ODBC. Once you get the hang of it and you write your own functions, it makes life a ton easier.

All I am trying to do is query:
select customerID from accounts where account_number = ‘[INPUT]’
All I did is add a DB and table to FreePBX’s existing mariadb instance
Would this be easy to query with ODBC / AGI and if so could you please send an example of querying the table for the above command since the MySql query function will be deprecated soon? I’d switch to ODBC/AGI which ever is easier for this super simple query that is checking the database.