function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
chidevchidev 

Reference field not returning id in JavaScript

I'm trying to query something in a custom button JavaScript.  The query uses a reference field on the custom object record.  However the {!} returns with referenced object's name instead of id, so I can't use it in a SOQL statement.  For instance,with SFDC_Projects__c defined as a lookup/reference on SFL5_Time__c object,

soql=soql + "'{!SFL5_Time__c.SFDC_Projects__c}'";

gets 'steves project" instead of the id for the project.  Since I'm trying to find out another object that's related to the same project, I really need the id instead of the name.

 

The same way works just fine in APEX trigger.  What did I miss in the JavaScript/AJAX Toolkit environment?

Best Answer chosen by Admin (Salesforce Developers) 
AlexPHPAlexPHP

I see what you mean now.  You are trying to access related objects via a merge field.

 

Merge fields are limited in their immediate accessibility.  And in some cases, will return you the display information of a relationship instead of it's actual ID when you access the relationship... like in your case, "SFL5_Time__c.SFDC_Projects__c".

 

If the related object's name is unique, you can do a workaround, which requires more work, by doing a separate query on the related object.

 

Select Id 
From SFDC_Projects__c
Where Name = '{!SFL5_Time__c.SFDC_Projects__c}';

// and then you have the id to construct your original query

If the name is not unique however, I don't have a good solution for you besides to abandon use of a Javascript Ajax Toolkit to execute the query, but rather call a APEX webservice class to do your queries/logic.

 

All Answers

Ispita_NavatarIspita_Navatar

I think you should try the following:-

 

In the instance provided by you ,with SFDC_Projects__c is defined as a lookup/reference on SFL5_Time__c object,

so use :-   {!SFL5_Time__c.SFDC_Projectsid__c}'


soql=soql + "' {!SFL5_Time__c.SFDC_Projectsid__c}'";

 

try the above and do let me know if it works , conversely you can use ecllipse to find the actual name of the field which holds the id of the related - SFL5_Time__c , object and use it in the above query.

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

Imran MohammedImran Mohammed

I am not sure why you are getting name instead of id.

Make sure that you are using the custom field SFDC_Projects__c API name.

 

Else you have to take a different approach.

Query the SFDC_Projects__c object with the name you are getting with the '{!SFL5_Time__c.SFDC_Projects__c}' merge field to get ID value.

Then use the ID value in your SOQL.

 

Hope this helps.

 

Else paste the code.

Are you using SControl or Visualforce page?

chidevchidev

Ispita,

 

SFDC_Projectsid__c did work, but it returned 15 digit ID.  How can I use it in SOQL conditions, which I think expect 18 digit IDs?

 

Thanks!

rscottrscott

SOQL queries will take a 15 or 18 character Id.

Ispita_NavatarIspita_Navatar

rscott is absolutely correct.

chidevchidev

You're right the ID type probably doesn't matter, but I'm still not getting what I need...  I'm trying to use this query with the ID I got from {!SFL5_Time__c.SFDC_ProjectsId__c}:

sforce.connection.query("Select  Id, project__c from Proposed_Bill__c WHERE Invoice_processed__c = FALSE AND project__c = '{!SFL5_Time__c.SFDC_ProjectsId__c}'");

 

project__c is a reference field defined on SFL5_Time__c.  I checked in Apex Explorer to make sure of the field name.  I know there's an unprocessed Proposed_Bill entered with the same Project (I can view the record from UI just fine), but the query returned nothing.  So I just removed the project__c condition and returned all records (this is on a sandbox) and viewed project__c values, and they're "undefined" in JavaScript.  Code to return project__c:

records=myProject.getArray("records");  //myProject got the return from sforce.connection.query with no project__c condition
alert(records[0].project__c);

 

Did I need to use the ref field a little differently in the query?

AlexPHPAlexPHP

Why don't you just output your query to the screen or to the debug log first, to see if the values are even there?

 

If the values are not there, then the field is simply not set or does not have the proper permission to access the field.

 

If the values are there, but the query doesn't return anything, that's more of a data issue.

 

In either case, if you are getting the related object's name instead of the ID, you probably have something wrong with the way you setup the lookup relationship.

chidevchidev

I'm pretty sure my test data and permissions are fine.  It's easy to check them. 

 

I guess my question now really is how to access results in relationship queries in a JavaScript environment (AJAX Toolkit).  So for a query like

"Select  Id, project__c, project__r.id from Proposed_Bill__c WHERE Invoice_processed__c = FALSE AND project__r.id='a0BQ0000002eW6d'",

it's easy to retrieve relationship results in APEX or some other languages.  It's like an inner object.  But how do I do it in JavaScript?  I tried to use getArray("records") a second time but that comes up "undefined".

AlexPHPAlexPHP

I see what you mean now.  You are trying to access related objects via a merge field.

 

Merge fields are limited in their immediate accessibility.  And in some cases, will return you the display information of a relationship instead of it's actual ID when you access the relationship... like in your case, "SFL5_Time__c.SFDC_Projects__c".

 

If the related object's name is unique, you can do a workaround, which requires more work, by doing a separate query on the related object.

 

Select Id 
From SFDC_Projects__c
Where Name = '{!SFL5_Time__c.SFDC_Projects__c}';

// and then you have the id to construct your original query

If the name is not unique however, I don't have a good solution for you besides to abandon use of a Javascript Ajax Toolkit to execute the query, but rather call a APEX webservice class to do your queries/logic.

 

This was selected as the best answer
chidevchidev

I can't guarantee the name is unique...  Your suggestion confirmed what I've been thinking - it may just have to be implemented as WebService APEX code.  Thanks!

chidevchidev

Thanks to Ispita too - your answer was helpful to me as well.  Too bad this board doesn't allow split solutions.