+ Start a Discussion
NishanNishan 

Passing a string variable into a WHERE statement in SOQL Query

Hi everyone,

                      I have an object 'Inventory' with a field 'Issue to' and a button. A number input via prompt from this button should update a field named 'Number Of Gifts' which is in an another object named 'Field Executive'. But I am not able to pass a string in the soql statement. Is there anyway to achieve this? Here is my code.

 

 

{!requireScript("/soap/ajax/26.0/connection.js")}
var url = parent.location.href;

var inp=prompt("Enter the number of gifts to issue");       

var abc=sforce.connection.query("SELECT Issue_To__c FROM Inventory__c WHERE Id='{!Inventory__c.Id}'");       

var record1 = abc.getArray("records");

var pqr = sforce.connection.query("SELECT Id FROM Field_Executive__c WHERE Id=\''' + record1[0] + '\'");

var record2 = pqr.getArray("records");

var fe = new sforce.SObject("Field_Executive__c");

fe.Id = "abc";

fe.Number_of_Gifts__c = parseFloat(record2[0].Number_of_Gifts__c) +
parseFloat(inp);

result = sforce.connection.update([fe]);
parent.location.href = url;

 

 

 

The error says     "unexpected token: '+'', detail:{MalformedQueryFault:{exceptionCode:'MALFORMED_QUERY', exceptionMessage: ' Field_Executive__c WHERE Id='' + record1[0] + ''"

Dhaval PanchalDhaval Panchal

Try Below.

 

{!requireScript("/soap/ajax/26.0/connection.js")}
var url = parent.location.href;

var inp=prompt("Enter the number of gifts to issue");       

var abc=sforce.connection.query("SELECT Issue_To__c FROM Inventory__c WHERE Id='{!Inventory__c.Id}'");       

var record1 = abc.getArray("records");

var pqr = sforce.connection.query("SELECT Id, Number_of_Gifts__c FROM Field_Executive__c WHERE Id='" + record1[0].Id + "'"); //I have used record1[0].Id because I don't know which field you want to use in comparison

var record2 = pqr.getArray("records");

var fe = new sforce.SObject("Field_Executive__c");

fe.Id = record2[0].Id;

fe.Number_of_Gifts__c = parseFloat(record2[0].Number_of_Gifts__c) +
parseFloat(inp);

result = sforce.connection.update([fe]);
parent.location.href = url;

 

NishanNishan

Hi Dapanchal,

 

                          Thanks for the reply. But the syntax of WHERE is still returning the following error.

 

A problem with the OnClick JavaScript for this button or link was encountered:

{faultcode:'sf:INVALID_QUERY_FILTER_OPERATOR', faultstring:'INVALID_QUERY_FILTER_OPERATOR: 
FROM Field_Executive__c WHERE Id='null'
                              ^
ERROR at Row:1:Column:61
invalid ID field: null', detail:{UnexpectedErrorFault:{exceptionCode:'INVALID_QUERY_FILTER_OPERATOR', exceptionMessage:'
FROM Field_Executive__c WHERE Id='null'
                              ^
ERROR at Row:1:Column:61
invalid ID field: null', }, }, }

            I found an entirely different solution for the problem, so no worries here. But it would be nice to know more about the above error if you are not very busy.

gbu.varungbu.varun

Hi

 

I have done it.

Try the following lines:

               

                var record1 = abc.getArray("records");
                var ttt = "'"+record1[0].value+"'";
                var query = "SELECT Id FROM Field_Executive__c WHERE Id= "+temp+" limit 1";                
                var pqr = sforce.connection.query(query);


If it does not work please let me know.

NishanNishan

Hi Varun,

               Thanks for the code. I will check it out and let you know.

Dhaval PanchalDhaval Panchal
Error is in below query.
var pqr = sforce.connection.query("SELECT Id, Number_of_Gifts__c FROM Field_Executive__c WHERE Id='" + record1[0].Id + "'");

now we are fetching id from record1, but we are not fetching field id in first query (below)

var abc=sforce.connection.query("SELECT Issue_To__c FROM Inventory__c WHERE Id='{!Inventory__c.Id}'");

so try replace this query with below query


var abc=sforce.connection.query("SELECT Id, Issue_To__c FROM Inventory__c WHERE Id='{!Inventory__c.Id}'");
Dhaval PanchalDhaval Panchal
and there is no any other syntax error. error is only that id field is getting null because we are not fetching value for field id thats it.