husseinco | 2018-10-05 16:49:35 UTC | #1
Hi here,
Is it possible to get input from caller in the IVR and then insert them into SQL database through Bridge Server (SQL-connector)?
Regards,
Hussein
Jason_Mathison | 2018-10-05 18:01:40 UTC | #2
Hi Hussein,
I believe that the Bridge SQL connector is for synchronizing users between PureCloud and the SQL Database. It is not a general purpose way to run SQL commands.
--Jason
husseinco | 2018-10-05 19:24:42 UTC | #3
If that the case, then is there any sort of way to insert data directly from IVR to SQL DB ?
anon28066628 | 2018-10-05 22:07:30 UTC | #4
You'd need to use the webservice data dip bridge connector. If it's not available in your org yet, PureCloud Care can add it for you.
You'll need to add a Custom Action. The bridge data dip custom actions aren't as configurable as the newer Data Actions, For example:
- It only sends POST requests
- The URL (which can be an IP) is static
- Headers are static and not configurable
- No authorization is supported (Basic, Oauth, etc.)
- Request and response payloads must be flattenable.
In practice, this means in addition to the bridge you need a local middleware service that accepts the request from the Bridge connector. It then does the backend work to insert data into your SQL db.
The req and res payloads are specified by the schemas in the configuration. By "flattenable", it means basically no complex nested objects or arrays-of-arrays. In general it's simplest to just use flat responses, just simple key/value pairs:
{ "key1": "value1", "key2": "value2", "keyN": "valueN" }
https://help.mypurecloud.com/articles/add-a-bridge-action-for-connectors/
Jason_Mathison | 2018-10-08 12:56:17 UTC | #5
As RJ noted, there are a lot of limitations to the bridge webservice connector. There are a couple of ways that you could use Data Actions instead:
- You could use Data Actions if your SQL server is already publicly accessible.
- You could use a service to make your SQL server available publicly, such as ngrok. We documented setting this up here:
https://developer.mypurecloud.com/blog/2018/09/11/access-local-service-from-the-cloud/
husseinco | 2018-10-09 12:38:12 UTC | #6
Thank you for feedback, I will try it soon, but for now I'm trying to integrate with a REST API and I keep getting the following error message
` 9. Resolve translation map: Failed while processing the translation map. Could not resolve value for the key: 'id' and no default value was configured. Additional details: Expected to find an object with property ['id'] in path $['data'] but found 'java.util.ArrayList'. This is not a json object according to the JsonProvider: 'com.jayway.jsonpath.spi.json.JsonSmartJsonProvider'.`
is there a way to resolve this issue @anon28066628 & @Jason_Mathison
Jason_Mathison | 2018-10-09 12:52:47 UTC | #7
I would recommend taking the results you get from the execution step and then testing out your translation map entries on Jsonpath.com. If you can't sort out the issue with that site then please post again with an export of your action attached along with the output from the execute step. Feel free to change any sensitive text inside of quotes for the action or output.
husseinco | 2018-10-09 19:07:48 UTC | #8
Hi Jason, I managed to fix the translation map issue through the Jsonpath.com but another issue arose in the output is shows me empty field (as shown below)
and here is the execute
{ "data": [ { "id": "2280800", "name": "user", "status": "cancelled", "guests": "2", "type": "online", "from": "2018-09-09 19:45:00", "to": "2018-09-09 21:44:59", "loyalty": "Loyalty for 1524269 restaurant with \"VIP\" status", "tables": [ "39-S" ], "feedbacks": [], "client": { "id": 1525555, "name": "user", "email": "abcd0@GMAIL.COM", "phone": "+966123456789", "stats": { "booked": 54, "cancelled": 31, "notarrived": 3, "latestbooking": "2018-05-28 18:30:00" } } } ], "count": 1, "self": { "title": "Self", "href": "https://app.tablein.com/api/v1.0/reservations" } }
attached is the custom action
<a class="attachment" href="/forum/uploads/db6296/original/1X/48c37e048c0d15d62b8deb74db08c7078a14eb73.json">RetrieveReservationInfoByID-20181009215320.custom.json</a> (3.0 KB)
Jason_Mathison | 2018-10-09 21:01:59 UTC | #9
I ran into a couple of issues getting this to work. First, the output contract had the output value an extra level deep. I had to remove the outer object, I think it was called "data".
Next, while several of the values are logically numeric, the web service is returning them as strings. For example guests is returning "2", not 2. I changed the output contract for those properties to be strings.
Finally, tables is returned as an array, so I had to extract the value using firstFromArray, as documented here: https://help.mypurecloud.com/articles/velocity-macros-data-actions/#firstFromArray
Ending up with a success template like this: "successTemplate": "{\r\n \t\"id\": ${id},\r\n\"name\": ${name}\r\n, \t\"status\": ${status}\r\n, \t\"guests\": ${guests}\r\n, \t\"type\": ${type}\r\n, \t\"from\": ${from}\r\n, \t\"to\": ${to}\r\n, \t\"loyalty\": ${loyalty}\r\n, \t\"tables\": ${successTemplateUtils.firstFromArray(\"${tables}\")}\r\n}"
--Jason
husseinco | 2018-10-09 21:40:39 UTC | #10
I tried to apply the above steps but I'm facing the below error when applying the "SuccessTemplate"
{ "translationMap": { "id": "$.data[0].id", "name": "$.data[0].name", "status": "$.data[0].status", "guests": "$.data[0].guests", "type": "$.data[0].type", "from": "$.data[0].from", "to": "$.data[0].to", "loyalty": "$.data[0].loyalty", "tables": "$.data[0].tables" }, "translationMapDefaults": {}, "successTemplate": "{\r\n \t\"id\": ${id},\r\n\"name\": ${name}\r\n, \t\"status\": ${status}\r\n, \t\"guests\": ${guests}\r\n, \t\"type\": ${type}\r\n, \t\"from\": ${from}\r\n, \t\"to\": ${to}\r\n, \t\"loyalty\": ${loyalty}\r\n, \t\"tables\": ${successTemplateUtils.firstFromArray("${tables}")}\r\n}" }
Jason_Mathison | 2018-10-10 01:15:13 UTC | #11
Sorry, if I don't put the JSON in "preformatted text" block some of the characters can be removed. See if this works better:
"successTemplate": "{\r\n \t\"id\": ${id},\r\n\"name\": ${name}\r\n, \t\"status\": ${status}\r\n, \t\"guests\": ${guests}\r\n, \t\"type\": ${type}\r\n, \t\"from\": ${from}\r\n, \t\"to\": ${to}\r\n, \t\"loyalty\": ${loyalty}\r\n, \t\"tables\": ${successTemplateUtils.firstFromArray(\"${tables}\")}\r\n}"
husseinco | 2018-10-10 15:44:30 UTC | #12
Hi Jason,
I managed to get the first part to work perfectly, but when I added the other part (client&stats) and run the test it shows me empty cells even though I'm able to access the response through Jsonpath.com correctly
<a class="attachment" href="/forum/uploads/db6296/original/2X/c/cfc7751c122657031dd72f2f40a7b1141dbcd944.json">RetrieveReservationInfoByID-20181010183604.custom.json</a> (4.4 KB)
Thank you for your kind assistance.
husseinco | 2018-10-10 15:46:57 UTC | #13
this is the response I'm getting back
{ "data": [ { "id": "2290006", "name": "Example reservation", "status": "approved", "guests": "10", "type": "phone", "from": "2018-09-13 15:30:00", "to": "2018-09-13 17:59:59", "offer": "Example discount", "loyalty": "Example loyalty", "tables": [ "T1", "T2", "T3", "T6", "T7", "T8", "T9", "T10" ], "feedbacks": [ { "id": 123, "ambience": 4, "cleanliness": 5, "food": 4, "service": 2, "total": 3.75, "comment": "Great dinner" } ], "client": { "id": 123, "name": "Example client", "email": "example@client.com", "phone": "+355123123", "stats": { "booked": 2, "cancelled": 0, "notarrived": 0, "latestbooking": "2018-09-11 15:30:00" } } } ], "count": 1, "self": { "title": "Self", "href": "https://app.tablein.com/api/v1.0/reservations" } }
anon28885283 | 2018-10-11 04:55:28 UTC | #14
I think it's because your output schema and your success template does not match.
Maybe try this as your success template:
"{\r\n \"id\": ${Reservationid},\r\n \"name\": ${Reservationname}, \t\r\n \"status\": ${Reservationstatus},\r\n \"guests\": ${guests}, \t\"type\": ${Reservationtype},\r\n \"from\": ${Reservationfrom},\r\n \"to\": ${Reservationto},\r\n \"loyalty\": ${loyalty},\r\n \"tables\": ${successTemplateUtils.firstFromArray(\"${tables}\")}, \r\n \"client\": {\r\n \"name\": ${clientname}, \r\n \"email\": ${clientemail}, \r\n \"phone\": ${clientphone},\r\n \"stats\": {\r\n \"booked\": ${statsBooked}, \r\n \"cancelled\": ${statsCancelled}, \r\n \"not_arrived\": ${statsNot_arrived}, \r\n \"latest_booking\": ${statslaTest_booking}\r\n }\r\n }\r\n}"
husseinco | 2018-10-11 08:41:48 UTC | #15
Hi Prince,
I applied your proposed success template but it showed me errors as shown below
anon28885283 | 2018-10-11 09:41:25 UTC | #16
Could you post the entire response config?
husseinco | 2018-10-11 09:53:54 UTC | #17
Here you go
{ "translationMap": { "Reservationid": "$.data[0].id", "Reservationname": "$.data[0].name", "Reservationstatus": "$.data[0].status", "guests": "$.data[0].guests", "Reservationtype": "$.data[0].type", "Reservationfrom": "$.data[0].from", "Reservationto": "$.data[0].to", "loyalty": "$.data[0].loyalty", "tables": "$.data[0].tables", "clientname": "$.data[0].client.name", "clientemail": "$.data[0].client.email", "clientphone": "$.data[0].client.phone", "statsBooked": "$.data[0].client.stats.booked", "statsCancelled": "$.data[0].client.stats.cancelled", "statsNotarrived": "$.data[0].client.stats.notarrived", "statslaTestbooking": "$.data[0].client.stats.latestbooking" }, "translationMapDefaults": {}, "successTemplate": "{\r\n \t\"id\": ${Reservationid},\r\n\"name\": ${Reservationname}\r\n, \t\"status\": ${Reservationstatus}\r\n, \t\"guests\": ${guests}\r\n, \t\"type\": ${Reservationtype}\r\n, \t\"from\": ${Reservationfrom}\r\n, \t\"to\": ${Reservationto}\r\n, \t\"loyalty\": ${loyalty}\r\n, \t\"tables\": ${successTemplateUtils.firstFromArray(\"${tables}\")}\r\n, \t\"name\": ${clientname}\r\n, \t\"email\": ${clientemail}\r\n, \t\"phone\": ${clientphone}\r\n, \t\"booked\": ${statsBooked}\r\n, \t\"cancelled\": ${statsCancelled}\r\n, \t\"notarrived\": ${statsNotarrived}\r\n, \t\"latestbooking\": ${statslaTestbooking}\r\n}" }
anon28885283 | 2018-10-11 10:02:12 UTC | #18
Try this entire response config, parse errors mean there's some syntax error with the json/template.
{ "translationMap": { "Reservationid": "$.data[0].id", "Reservationname": "$.data[0].name", "Reservationstatus": "$.data[0].status", "guests": "$.data[0].guests", "Reservationtype": "$.data[0].type", "Reservationfrom": "$.data[0].from", "Reservationto": "$.data[0].to", "loyalty": "$.data[0].loyalty", "tables": "$.data[0].tables", "clientname": "$.data[0].client.name", "clientemail": "$.data[0].client.email", "clientphone": "$.data[0].client.phone", "statsBooked": "$.data[0].client.stats.booked", "statsCancelled": "$.data[0].client.stats.cancelled", "statsNotarrived": "$.data[0].client.stats.notarrived", "statslaTestbooking": "$.data[0].client.stats.latestbooking" }, "translationMapDefaults": {}, "successTemplate": "{\r\n \"id\": ${Reservationid},\r\n \"name\": ${Reservationname}, \t\r\n \"status\": ${Reservationstatus},\r\n \"guests\": ${guests}, \t\"type\": ${Reservationtype},\r\n \"from\": ${Reservationfrom},\r\n \"to\": ${Reservationto},\r\n \"loyalty\": ${loyalty},\r\n \"tables\": ${successTemplateUtils.firstFromArray(\"${tables}\")}, \r\n \"client\": {\r\n \"name\": ${clientname}, \r\n \"email\": ${clientemail}, \r\n \"phone\": ${clientphone},\r\n \"stats\": {\r\n \"booked\": ${statsBooked}, \r\n \"cancelled\": ${statsCancelled}, \r\n \"notarrived\": ${statsNotarrived}, \r\n \"latestbooking\": ${statslaTestbooking}\r\n }\r\n }\r\n}" }
husseinco | 2018-10-11 10:22:06 UTC | #19
Hi Prince,
Result still the same, empty field as shown below
ChrisDibble | 2018-10-11 12:47:36 UTC | #20
If you expand the JSON section in the bottom of your screenshot, you will see the actual response from the action execution. Does that JSON match what you expect? Often, it's easier to spot any problems (like json nested at the wrong level or a property name that was misspelled) by looking directly at the JSON response.
-Chris
husseinco | 2018-10-11 13:37:52 UTC | #21
The response I'm getting back is like this
[ { "client.stats.notarrived": 3, "loyalty": "Loyalty for 1524269 restaurant with \"VIP\" status", "type": "online", "client.phone": "+9661234567890", "tables": "39-S", "client.stats.booked": 54, "name": "user", "guests": "2", "from": "2018-09-09 19:45:00", "id": "2280800", "to": "2018-09-09 21:44:59", "client.email": "example@GMAIL.COM", "client.stats.latestbooking": "2018-05-28 18:30:00", "client.name": "user", "client.stats.cancelled": 31, "status": "cancelled" } ]
and it's exactly what I want but as the above screenshot showing the field is not populated with the values even though I'm using JSONpath.com to make sure I'm on the right path.
Plus I have noticed something strange in the sense some field has up and down arrows as shown below
anon28885283 | 2018-10-11 14:01:12 UTC | #22
Hmm, could you try unchecking the 'Flatten output' before running the action
husseinco | 2018-10-11 14:08:57 UTC | #23
I'm able to see the values now, but will I be able to use the values in the Architect? @anon28885283
ChrisDibble | 2018-10-11 14:11:19 UTC | #24
I've recreated your action in my environment and I believe you've run into an issue with the UI that populates those values from the response. It doesn't appear to properly handle the flattened fields. Based on the JSON output, Architect should work fine. I'll create an internal ticket to make sure the UI is able to successfully extract those fields going forward.
-Chris
husseinco | 2018-10-11 16:58:02 UTC | #25
Hi Prince & Chris,
I have added a new section into my action which is the "Feedbacks" and tried to modify the output contract and the success template and I ran into another issue as shown below
and this is the my response config
{ "translationMap": { "Reservationid": "$.data[0].id", "Reservationname": "$.data[0].name", "Reservationstatus": "$.data[0].status", "guests": "$.data[0].guests", "Reservationtype": "$.data[0].type", "Reservationfrom": "$.data[0].from", "Reservationto": "$.data[0].to", "loyalty": "$.data[0].loyalty", "tables": "$.data[0].tables",
"feedbackid":"$.data[0].feedbacks[0].id", "feedbackambience":"$.data[0].feedbacks[0].ambience", "feedbackcleanliness":"$.data[0].feedbacks[0].cleanliness", "feedbackfood":"$.data[0].feedbacks[0].food", "feedbackservice":"$.data[0].feedbacks[0].service", "feedbacktotal":"$.data[0].feedbacks[0].total", "feedbackcomment":"$.data[0].feedbacks[0].comment",
"clientid": "$.data[0].client.id", "clientname": "$.data[0].client.name", "clientemail": "$.data[0].client.email", "clientphone": "$.data[0].client.phone", "statsBooked": "$.data[0].client.stats.booked", "statsCancelled": "$.data[0].client.stats.cancelled", "statsNotarrived": "$.data[0].client.stats.notarrived", "statslaTestbooking": "$.data[0].client.stats.latestbooking" }, "translationMapDefaults": { "feedbackid": "\"UNKNOWN", "feedbackambience": "\"UNKNOWN", "feedbackcleanliness": "\"UNKNOWN", "feedbackfood": "\"UNKNOWN", "feedbackservice": "\"UNKNOWN", "feedbacktotal": "\"UNKNOWN", "feedbackcomment": "\"UNKNOWN"
}, "successTemplate": "{\r\n \"id\": ${Reservationid},\r\n \"name\": ${Reservationname}, \t\r\n \"status\": ${Reservationstatus},\r\n \"guests\": ${guests}, \t\"type\": ${Reservationtype},\r\n \"from\": ${Reservationfrom},\r\n \"to\": ${Reservationto},\r\n \"loyalty\": ${loyalty}, \r\n \"tables\": ${successTemplateUtils.firstFromArray(\"${tables}\")},\r\n \"feedbacks\": {\r\n \"id\": ${feedbackid}, \r\n \"ambience\": ${feedbackambience}, \r\n \"cleanliness\": ${feedbackcleanliness}, \r\n \"food\": ${feedbackfood},\r\n \"service\": ${feedbackservice},\r\n \"total\": ${feedbacktotal},\r\n \"comment\": ${feedbackcomment},\r\n \"client\": {\r\n \"name\": ${clientname}, \r\n \"id\": ${clientid}, \r\n \"email\": ${clientemail}, \r\n \"phone\": ${clientphone},\r\n \"stats\": {\r\n \"booked\": ${statsBooked}, \r\n \"cancelled\": ${statsCancelled}, \r\n \"notarrived\": ${statsNotarrived}, \r\n \"latestbooking\": ${statslaTestbooking}\r\n }\r\n }\r\n}\r\n}" }
ChrisDibble | 2018-10-11 17:12:51 UTC | #26
husseinco, post:25, topic:3683
"feedbackid": "\"UNKNOWN",
Looks like your default values are missing the closing quote character. Try updating them to look like this: "feedbackid": "\"UNKNOWN\"",
husseinco | 2018-10-11 20:28:29 UTC | #27
Hi
There is a wired behavior in the sense when I apply this success template
"successTemplate": "{\r\n \"id\": ${Reservationid},\r\n \"name\": ${Reservationname}, \t\r\n \"status\": ${Reservationstatus},\r\n \"guests\": ${guests}, \t\"type\": ${Reservationtype},\r\n \"from\": ${Reservationfrom},\r\n \"to\": ${Reservationto},\r\n \"loyalty\": ${loyalty},\r\n \"tables\": ${successTemplateUtils.firstFromArray(\"${tables}\")}, \r\n \"client\": {\r\n \"name\": ${clientname}, \r\n \"id\": ${clientid}, \r\n \"email\": ${clientemail}, \r\n \"phone\": ${clientphone},\r\n \"stats\": {\r\n \"booked\": ${statsBooked}, \r\n \"cancelled\": ${statsCancelled}, \r\n \"notarrived\": ${statsNotarrived}, \r\n \"latestbooking\": ${statslaTestbooking}\r\n }\r\n }\r\n}"
the fields in the " client & stats" are shown perfectly, but when I add the "Feedbacks" section to the success template the "client & stats" sections disappears it only shows "Feedbacks" section populated with data
"successTemplate": "{\r\n \"id\": ${Reservationid},\r\n \"name\": ${Reservationname}, \t\r\n \"status\": ${Reservationstatus},\r\n \"guests\": ${guests}, \t\"type\": ${Reservationtype},\r\n \"from\": ${Reservationfrom},\r\n \"to\": ${Reservationto},\r\n \"loyalty\": ${loyalty}, \r\n \"tables\": ${successTemplateUtils.firstFromArray(\"${tables}\")},\r\n \"feedbacks\": {\r\n \"id\": ${feedbackid}, \r\n \"ambience\": ${feedbackambience}, \r\n \"cleanliness\": ${feedbackcleanliness}, \r\n \"food\": ${feedbackfood},\r\n \"service\": ${feedbackservice},\r\n \"total\": ${feedbacktotal},\r\n \"comment\": ${feedbackcomment},\r\n \"client\": {\r\n \"name\": ${clientname}, \r\n \"id\": ${clientid}, \r\n \"email\": ${clientemail}, \r\n \"phone\": ${clientphone},\r\n \"stats\": {\r\n \"booked\": ${statsBooked}, \r\n \"cancelled\": ${statsCancelled}, \r\n \"notarrived\": ${statsNotarrived}, \r\n \"latestbooking\": ${statslaTestbooking}\r\n }\r\n }\r\n}\r\n}"
anon28885283 | 2018-10-12 05:06:56 UTC | #28
I'm inferring this from the translation map you've posted (which seems like client shouldn't be a child of feedbacks) but there may be a mismatch with your output contract and response config. Your new success template is organized as such:
"successTemplate": { ... "feedbacks": { ... "client": { ... "stats": { ... } } } }
Is that also how the new output contract is defined?
husseinco | 2018-10-12 07:37:43 UTC | #29
Hi @anon28885283,
I believe yes, and here is my output contract
{ "$schema": "http://json-schema.org/draft-04/schema#", "type": "object", "properties": { "id": { "type": "string" }, "name": { "type": "string" }, "status": { "type": "string" }, "guests": { "type": "string" }, "type": { "type": "string" }, "from": { "type": "string" }, "to": { "type": "string" }, "loyalty": { "type": "string" }, "tables": { "type": "string" }, "feedbacks": { "properties": { "id": { "type": "string" }, "ambience": { "type": "integer" }, "cleanliness": { "type": "integer" }, "food": { "type": "integer" }, "service": { "type": "integer" }, "total": { "type": "number" }, "comment": { "type": "string" } }, "type": "object" }, "client": { "properties": { "id": { "type": "integer" }, "name": { "type": "string" }, "email": { "type": "string" }, "phone": { "type": "string" }, "stats": { "properties": { "booked": { "type": "number" }, "cancelled": { "type": "number" }, "notarrived": { "type": "number" }, "latestbooking": { "type": "string" } }, "type": "object" } }, "type": "object" } } }
anon28885283 | 2018-10-12 07:55:48 UTC | #30
Thanks for providing the info.
You may notice there is a difference in nesting:
Output Contract:
{ ... "feedbacks": { ... }, "client": { ... "stats": { ... } } }
Success Template:
{ ... "feedbacks": { ... "client": { ... "stats": { ... } } } }
My recommendation is try building the successTemplate in JSON format and then run it through an escape tool like: https://www.freeformatter.com/json-escape.html before assigning it to the successTemplate property.
husseinco | 2018-10-12 08:42:26 UTC | #31
Thank you very much @anon28885283 it's working fine now, the issue was from the nested successTemplate I just had to add `\r\n}, after the end of the Feedbacks.
@Jason_Mathison & @ChrisDibble BIG thanks for your kind contributions as well.
system | 2018-11-12 08:41:59 UTC | #32
This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.
This post was migrated from the old Developer Forum.
ref: 3683