function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mike@MungosMike@Mungos 

​SOQL query on custom javascript button in order to create a validation rule on related object

Hi,

I am trying to create a custom button on a custom object which can only be clicked if a date on the parent oject is filled in.  I am new to SOQL and javascript but have managed to created custom buttons in the past with validation rules which related to the object they are on however I am struggling with cross object rules.

In this instance an Assessment__c can only be submitted if it's parent Referral__c has an accepted date that is not blank.

Any help would be very much apprecitated.

Here is the code so far:

{!REQUIRESCRIPT("/soap/ajax/30.0/connection.js")} 
{!REQUIRESCRIPT("/soap/ajax/24.0/apex.js")}

var ReferraAccepted = "SELECT Accepted_date__c FROM Referral__c WHERE Name = '{!Assessment__c.Referral__c}' LIMIT 1";

if (ReferraAccepted == null){alert ("Please submit the completed referral form to the Clearing House as this form cannot be submitted on it's own.")} 

else {

var newRecords = []; 
var c = new sforce.SObject("Assessment__c"); 
c.id ="{! Assessment__c.Id }"; 
c.Submit_for_assessment__c = true; 
newRecords.push(c); 
result = sforce.connection.update(newRecords); 
window.location.reload();
}
 
Best Answer chosen by Mike@Mungos
Apoorv Saxena 4Apoorv Saxena 4

Hi Mike,

Try this code -->>

{!REQUIRESCRIPT("/soap/ajax/30.0/connection.js")} 
{!REQUIRESCRIPT("/soap/ajax/24.0/apex.js")} 

var ReferraAccepted = sforce.connection.query("SELECT Referral__r.Accepted_Date__c from Assessment__c where id ='{!Assessment__c.Id}' limit 1");

records = ReferraAccepted.getArray("records"); 

var accDate = records[0].Referral__r.Accepted_Date__c; 

if(accDate==null){ 
alert("Please submit the completed referral form to the Clearing House as this form cannot be submitted on it's own.") 
} 
else { 

var newRecords = []; 
var c = new sforce.SObject("Assessment__c"); 
c.id ="{!Assessment__c.Id }"; 
c.Submit_for_assessment__c = true; 
newRecords.push(c); 
result = sforce.connection.update(newRecords); 
window.location.reload(); 
}


Let me  know if this works for you..!!

 

All Answers

viruSviruS
what exect error you are getting on javascript .. use try and catch for find out 
Mike@MungosMike@Mungos
I am not recieving an error.  However, the SOQL query does not appear to be working.

Any advice?
Apoorv Saxena 4Apoorv Saxena 4
Hi Mike,

You have your button on Assessment__c object so you can simply query from child to parent like -->

"SELECT Referral__r.Accepted_Date__c from Assessment__c where id ='{!Assessment__c.Id}' limit 1"; 

When we query from child to parent we append '__r'(denotes relationship) after the lookup field name to get access to fields in the parent record.
So, you can modify this query as per your needs.

In addition to this you'll need to use sforce.connection.query.sforce.connection.query request returns an object with a "records" field that is an array of the result SObjects and you can access the fields of the result SObjects using the dot notation.
So you will have to query like this -->>

var ReferraAccepted = sforce.connection.query("SELECT Referral__r.Accepted_Date__c from Assessment__c where id ='{!Assessment__c.Id}' limit 1");

See this link for some asynchronus ajax examples -->>

https://developer.salesforce.com/docs/atlas.en-us.ajax.meta/ajax/sforce_api_ajax_more_samples.htm

Hope this Helps..!!
Mike@MungosMike@Mungos
Thanks Apoorv Saxena 4, 

The SOQL query seems to work fine in Workbench but I still cannot get the results to work in my if statement.  Any ideas?
viruSviruS
Check
alert('::ReferraAccepted :::'+ReferraAccepted +':::');
 if (ReferraAccepted == null || ReferraAccepted  == 'null')
Apoorv Saxena 4Apoorv Saxena 4

Hi Mike,

Try this code -->>

{!REQUIRESCRIPT("/soap/ajax/30.0/connection.js")} 
{!REQUIRESCRIPT("/soap/ajax/24.0/apex.js")} 

var ReferraAccepted = sforce.connection.query("SELECT Referral__r.Accepted_Date__c from Assessment__c where id ='{!Assessment__c.Id}' limit 1");

records = ReferraAccepted.getArray("records"); 

var accDate = records[0].Referral__r.Accepted_Date__c; 

if(accDate==null){ 
alert("Please submit the completed referral form to the Clearing House as this form cannot be submitted on it's own.") 
} 
else { 

var newRecords = []; 
var c = new sforce.SObject("Assessment__c"); 
c.id ="{!Assessment__c.Id }"; 
c.Submit_for_assessment__c = true; 
newRecords.push(c); 
result = sforce.connection.update(newRecords); 
window.location.reload(); 
}


Let me  know if this works for you..!!

 

This was selected as the best answer
Mike@MungosMike@Mungos
Thanks again Apoorv Saxena 4 and viruS,

However, it still doesn't work.  I attempted to see what is being returned with the var accDate using alert('::accDate  :::'+v accDate  +':::') and the result is coming up as undefined?
viruSviruS

alert('::accDate  :::'+accDate  +':::') ;
Mike@MungosMike@Mungos
That's what I typed, sorry made a typo on the forum reply.  Unfortunately it's coming up as undefined.
viruSviruS
Mike .. it's long conversation .. can share your creedntials for  2 minute to fix the issue direct 
 
Apoorv Saxena 4Apoorv Saxena 4

You need to make sure that you are using correct API names. 

Cross Check..See if this helps
Mike@MungosMike@Mungos
Yes definately using the correct API names.  

If I run this check, alert('::records   :::'+ records  +':::') ; I see the result of my query, however when I check var accDate = records[0].Referral__r.Accepted_Date__c;  I'm getting an undefined result.......
Apoorv Saxena 4Apoorv Saxena 4
Whe you run alert('##records'+records), do you see Accepted_Date__c set as null?? like -->
Accepted_Date__c:null
 
Mike@MungosMike@Mungos
Hi Apoorv Saxena 4,

That is correct.  The complete alert is {type: 'Assessment__c', Id:null, Referral__r:{type:'Referral__c, Id:null, Accepted_date__c:null,},}.
Apoorv Saxena 4Apoorv Saxena 4

Hi Mike,

If thats returning null then i dont see any reason why it is showing as undefined there.

Ok, just try this code -->>

{!REQUIRESCRIPT("/soap/ajax/30.0/connection.js")} 
{!REQUIRESCRIPT("/soap/ajax/24.0/apex.js")} 

var ReferraAccepted = sforce.connection.query("SELECT Referral__r.Accepted_Date__c from Assessment__c where id ='{!Assessment__c.Id}' limit 1");

records = ReferraAccepted.getArray("records"); 

if(records[0].Referral__r.Accepted_Date__c==null){ 
alert("Please submit the completed referral form to the Clearing House as this form cannot be submitted on it's own.") 
} 
else { 

var newRecords = []; 
var c = new sforce.SObject("Assessment__c"); 
c.id ="{!Assessment__c.Id }"; 
c.Submit_for_assessment__c = true; 
newRecords.push(c); 
result = sforce.connection.update(newRecords); 
window.location.reload(); 
}

 

Please let me know if this works.!!

 
Mike@MungosMike@Mungos
I wish I had positive news to share but it is still not working.

I'm sure the error is here "records[0].Referral__r.Accepted_Date__c" as code is not finding the field even though it is being found in the query.
Mike@MungosMike@Mungos
Thanks everyone.  I finally got it to work!!!
SandeepbvSandeepbv
Hello,

I am having the same problem, can you tell me how you fixed this?
SandeepbvSandeepbv
I am able to figure out. variables are case-sensitive.