This is actually the continuation of the last community post where we are trying to send a query from app.js to request.json via context and getting an error saying, Malformed Query
last post: Request Template platform 2.3 - #7 by thimayarohit
Here we have an getContactInfo function which is making a get request on salesforce and as part of the request the query is build using a helper function called createAccount Query mentioned after this.
**app.js**
/**
* Get Contact info and related cases
*/
function getContactInfo() {
// Clear info
$("#contact_info").empty();
$("#contact_info").append($("<p></p>").text('Searching a contact...'));
$("#user_info").empty();
$("#cases_info").empty();
client.data.get("user").then(
function (data) {
console.info('Freshchat Contact data: ', data);
// getting site ID
const siteId = getSiteID(data);
// success output
// Contact
const freshChatContact = new SFDC.freshChatContact(
data.user.first_name,
data.user.last_name,
data.user.first_name + ' ' + data.user.last_name,
data.user.email,
data.user.phone,
siteId
);
console.info('Contact: ', freshChatContact);
// Save in variable
selfApp.freshchatContact = {
'assignedToUserId': (selfApp.salesforceUser && selfApp.salesforceUser.Id) ? selfApp.salesforceUser.Id : null,
'assignedToUserName': (selfApp.salesforceUser && selfApp.salesforceUser.Name) ? selfApp.salesforceUser.Name : null,
'firstName': data.user.first_name,
'lastName': data.user.last_name,
'phone': data.user.phone,
'email': data.user.email,
'personType': siteId
};
// save additional custom properties
saveCustomProperties(data.user.properties);
// Save Contact as current
selfApp.currentContact = selfApp.freshchatContact;
console.info('Current Contact:', selfApp.currentContact);
let query = createAccountQuery(
selfApp.freshchatContact.siteName,
selfApp.freshchatContact.jurisdiction,
selfApp.freshchatContact.channel,
selfApp.freshchatContact.externalId,
selfApp.freshchatContact.email,
selfApp.freshchatContact.acctUuid
);
if (!query) {
return null;
}
console.log("Query: " + query)
// Return contact from Salesforce
return client.request.invokeTemplate("getContactInfo", {
context: {
query: query
},
})
},
function (error) {
// failure operation
throw new Error(error);
}
)
.then(data => {
if (!data) {
showMessageInsteadContact('No contact matches in Salesforce.');
return null;
}
const response = JSON.parse(data.response);
console.info('Salesforce Contact data: ', response);
if (response.totalSize === 0) {
console.warn('No contact matches in Salesforce.');
showMessageInsteadContact('No contact matches in Salesforce.');
return null;
} else if (response.totalSize > 1) {
console.warn('More than one match of contacts found in Salesforce.');
showMessageInsteadContact('More than one match of contacts found in Salesforce.');
return null;
} else {
// Store contact in global variable
selfApp.salesforceContact = response.records[0];
// Save Contact as current
selfApp.currentContact = response.records[0];
// Add User to current Contact
selfApp.currentContact.assignedToUserId = (selfApp.salesforceUser && selfApp.salesforceUser.Id) ? selfApp.salesforceUser.Id : null;
selfApp.currentContact.assignedToUserName = (selfApp.salesforceUser && selfApp.salesforceUser.Name) ? selfApp.salesforceUser.Name : null;
console.info('Current Contact:', selfApp.currentContact);
showContact(response.records[0]);
// Return contact Id for searching related Cases
return response.records[0].PersonContactId;
}
})
.catch(error => {
console.error('Error received contact data: ', error);
$("#case_loader").hide();
$("#info_from_sf").show();
throw new Error('Error received contact data');
})
.then(contactId => {
contactId ? getCases(contactId) : showMessageInsteadCase();
})
.catch(error => {
console.error(error);
})
}
This is the helper function that dynamically creates the query.
**app.js**
/**
* Create query for Salesforce Account SOQL
* @param {String} siteName
* @param {String} jurisdiction
* @param {String} channel
* @param {*} externalId
* @param {String} userEmail
* @param {String} acctUuid
* @returns
*/
function createAccountQuery(siteName, jurisdiction = JURISDICTION_BC, channel, externalId, userEmail, acctUuid) {
siteName = siteName ? siteName.replace(/www\./gi, '') : null;
siteName = siteName ? siteName.replace(/qa[0-9]\./gi, '') : null;
siteName = siteName ? siteName.replace(/dev[0-9]\./gi, '') : null;
siteName = siteName ? siteName.replace(/dq0[0-9]\./gi, '') : null;
console.info('app.js query');
console.info('Account query parameters: siteName - ' + siteName +
'; jurisdiction - ' + jurisdiction +
'; channel - ' + channel +
'; externalId - ' + externalId +
'; userEmail - ' + userEmail +
'; acctUuid - ' + acctUuid);
// flag
let isQuery = false;
let queryString =
"SELECT+Id,+Name,+FirstName,+LastName,+PersonEmail,+IsPersonAccount,+Person_Type__pc,+" +
"PersonContactId,+Owner.Id,+Owner.Name+" +
"FROM+Account+" +
"WHERE+IsPersonAccount+=+True";
switch (siteName) {
case 'bclc.com':
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
break;
case 'corporate.bclc.com':
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
break;
case 'horizonsrg.bclc.com':
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
break;
case 'casinosbc.com':
if (channel == GENERAL_SUPPORT_BC_CHANNEL_NAME) {
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
} else if (channel == ENCORE_REWARDS_CHANNEL_NAME) {
queryString += "+AND+Person_Type__pc+=+'BC+Gold'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
} else if (channel == GAMESENSE_ADVISOR_CHANNEL_NAME) {
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
}
break;
case 'playnow.com':
if (channel == GENERAL_SUPPORT_BC_CHANNEL_NAME && jurisdiction == JURISDICTION_BC) {
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+Jurisdiction__c+=+'" + encodeURI(JURISDICTION_BC) + "'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
} else if (channel == PLAYNOW_ACCOUNT_CHANNEL_NAME && jurisdiction == JURISDICTION_BC && acctUuid) {
queryString += "+AND+PlayNow_Uuid__pc+=+'" + encodeURI(acctUuid) + "'";
isQuery = true;
} else if (channel == PLAYNOW_ACCOUNT_CHANNEL_NAME && !acctUuid && jurisdiction == JURISDICTION_BC) {
queryString += "+AND+Person_Type__pc+=+'PlayNow'+" +
"AND+Jurisdiction__c+=+'" + encodeURI(JURISDICTION_BC) + "'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
} else if (channel == GAMESENSE_ADVISOR_CHANNEL_NAME && jurisdiction == JURISDICTION_BC) {
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
} else if (channel == GENERAL_SUPPORT_MB_CHANNEL_NAME && jurisdiction == JURISDICTION_MB) {
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+Jurisdiction__c+=+'" + encodeURI(JURISDICTION_MB) + "'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
} else if (channel == PLAYNOW_ACCOUNT_CHANNEL_NAME && jurisdiction == JURISDICTION_MB && acctUuid) {
queryString += "+AND+PlayNow_UUID__pc+=+'" + encodeURI(acctUuid) + "'+" +
"AND+Jurisdiction__c+=+'" + encodeURI(JURISDICTION_MB) + "'";
isQuery = true;
} else if (channel == PLAYNOW_ACCOUNT_CHANNEL_NAME && !acctUuid && jurisdiction == JURISDICTION_MB) {
queryString += "+AND+Person_Type__pc+=+'PlayNow'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'+" +
"AND+Jurisdiction__c+=+'" + encodeURI(JURISDICTION_MB) + "'";
isQuery = true;
} else if (channel == GAMESENSE_ADVISOR_CHANNEL_NAME && jurisdiction == JURISDICTION_MB) {
queryString += "+AND+Person_Type__pc+=+'PlayNow'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'+" +
"AND+Jurisdiction__c+=+'" + encodeURI(JURISDICTION_MB) + "'";
isQuery = true;
} else if (channel == GENERAL_SUPPORT_SK_CHANNEL_NAME && jurisdiction == JURISDICTION_SK) {
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+Jurisdiction__c+=+'" + encodeURI(JURISDICTION_SK) + "'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
} else if (channel == PLAYNOW_ACCOUNT_CHANNEL_NAME && jurisdiction == JURISDICTION_SK && acctUuid) {
queryString += "+AND+PlayNow_UUID__pc+=+'" + encodeURI(acctUuid) + "'+" +
"AND+Jurisdiction__c+=+'" + encodeURI(JURISDICTION_SK) + "'";
isQuery = true;
} else if (channel == PLAYNOW_ACCOUNT_CHANNEL_NAME && !acctUuid && jurisdiction == JURISDICTION_SK) {
queryString += "+AND+Person_Type__pc+=+'PlayNow'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'+" +
"AND+Jurisdiction__c+=+'" + encodeURI(JURISDICTION_SK) + "'";
isQuery = true;
} else if (channel == GAMESENSE_ADVISOR_CHANNEL_NAME && jurisdiction == JURISDICTION_SK) {
queryString += "+AND+Person_Type__pc+=+'PlayNow'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'+" +
"AND+Jurisdiction__c+=+'" + encodeURI(JURISDICTION_SK) + "'";
isQuery = true;
}
break;
case 'gamesense.com':
if (channel == GENERAL_SUPPORT_BC_CHANNEL_NAME) {
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
} else if (channel == GAMESENSE_ADVISOR_CHANNEL_NAME) {
queryString += "+AND+Person_Type__pc+=+'Consumer'+" +
"AND+PersonEmail+=+'" + encodeURI(userEmail) + "'";
isQuery = true;
}
break;
}
console.info('Was Person Account matching: ', isQuery);
if (isQuery) {
console.info('Person Account Query is: ', queryString);
}
return isQuery ? queryString : null;
}
in getContact function just before we try to make a request we are console logging the query which is the query that we are passing as part of the request
Query: SELECT+Id,+Name,+FirstName,+LastName,+PersonEmail,+IsPersonAccount,+Person_Type__pc,+PersonContactId,+Owner.Id,+Owner.Name+FROM+Account+WHERE+IsPersonAccount+=+True+AND+Person_Type__pc+=+'PlayNow'+AND+Jurisdiction__c+=+'BC'+AND+PersonEmail+=+'test@test.com'
it means here let query = is the above query.
Now as seen in getContactInfo the way we are using the request template is
return client.request.invokeTemplate("getContactInfo", {
context: {
query: query
},
})
"getContactInfo": {
"schema": {
"method": "GET",
"protocol": "https",
"host": "<%= iparam.settings.salesforceDomainType %>.salesforce.com",
"path": "/services/data/v46.0/query/?q=<%= context.query %>",
"headers": {
"Authorization": "OAuth <%= access_token %>"
}
},
"options": {
"maxAttempts": 5,
"isOAuth": true
}
}
doing this creates a malformed query error:
After meeting with developers, the conclusion was they will add our app to the whitelisted domain which will solve this issue but unfortunately, the issue still exists please have a look and let me know If I am missing something!
Also to provide more context if I try to this where in request.json
"getContactInfo": {
"schema": {
"method": "GET",
"protocol": "https",
"host": "<%= iparam.settings.salesforceDomainType %>.salesforce.com",
"path": "/services/data/v46.0/query/?q=SELECT+Id,+Name,+FirstName,+LastName,+PersonEmail,+IsPersonAccount,+Person_Type__pc,+PersonContactId,+Owner.Id,+Owner.Name+FROM+Account+WHERE+IsPersonAccount+=+True+AND+Person_Type__pc+=+'PlayNow'+AND+Jurisdiction__c+=+'BC'+AND+PersonEmail+=+'test@test.com'",
"headers": {
"Authorization": "OAuth <%= access_token %>"
}
},
"options": {
"maxAttempts": 5,
"isOAuth": true
}
}
It works perfectly fine and gets the data without any error, so the cause exists in how the request template in building this string so please let me know if there is something needs to be done or consider.
@Thakur_Ganeshsingh @kaustavdm @zach_jones_noel @Srikanth_Reddy @gdimopoulos