11

I'm writing a fluent query builder and have hit a snag. While looking at the documentation for the FOR UPDATE clause, it has this snippet:

Consider using the FOR REFERENCE clause with the FOR VIEW clause to update recent usage data for retrieved objects.

Okay, cool, so that answers my question of if the two clauses are supported in tandem. But it does not specify how to combine them. I can't seem to figure out the syntax. I tried all of the below queries with no luck.


Query

SELECT Id FROM Account FOR VIEW FOR REFERENCE

Error

unexpected token: REFERENCE


Query

SELECT Id FROM Account FOR REFERENCE FOR VIEW

Error

unexpected token: VIEW


Query

SELECT Id FROM Account FOR VIEW, FOR REFERENCE

Error

expecting right square bracket, found ','


Query

SELECT Id FROM Account FOR VIEW, REFERENCE

Error

expecting right square bracket, found ','


Also worth noting that either clause on its own can be combined with FOR UPDATE. At least...they work in Execute Anonymous. In the Query Editor, they still throw:

Unknown error parsing query

Valid Query

SELECT Id FROM Account FOR VIEW FOR UPDATE

Valid Query

SELECT Id FROM Account FOR REFERENCE FOR UPDATE
6

Based on the documentation of the syntax:

SELECT ***fieldList*** [subquery][...]
[TYPEOF typeOfField whenExpression[...] elseExpression END][...]
FROM objectType[,...] 
    [USING SCOPE filterScope]
[WHERE conditionExpression]
[WITH [DATA CATEGORY] filteringExpression]
[GROUP BY {fieldGroupByList|ROLLUP (fieldSubtotalGroupByList)|CUBE (fieldSubtotalGroupByList)} 
    [HAVING havingConditionExpression] ] 
[ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ]
[LIMIT numberOfRowsToReturn]
[OFFSET numberOfRowsToSkip]
[FOR {VIEW  | REFERENCE}[,...] ]
      [ UPDATE {TRACKING|VIEWSTAT}[,...] ]

and the Typographical Conventions (including a relevant example that uses the same syntax)

[...] and [,...]

Square brackets containing an ellipsis indicate that the preceding element can be repeated up to the limit for the element. If a comma is also present, the repeated elements must be separated by commas. If the element is a list of choices grouped with curly braces, you can use items from the list in any order. For example, in the clause UPDATE {TRACKING|VIEWSTAT}[,...], the [,...] indicates that you can use TRACKING, VIEWSTAT, or both:

UPDATE TRACKING

UPDATE VIEWSTAT

UPDATE TRACKING, VIEWSTAT

This should be able to be executed as

FOR VIEW, REFERENCE

Unfortunately, this throws syntax errors in a v.39 org. Not sure about a v.40 org myself. Currently it looks like a few places have bad documentation around this, since the consideration to use them in conjunction is sprinkled throughout the docs for those optional clauses.

  • 1
    I thought I was on the right track with the SOQL syntax chart. I completely missed going through the 'typographical conventions' to decode the [,...]. +1 from me. I still find it strange that some incarnation of [FOR {VIEW | REFERENCE}[,..]] has been in the docs since v33.0, and it still doesn't work. – Derek F Apr 28 '17 at 20:13
3

If I read the Typographical Conventions in This Document

| - The pipe character separates alternate elements. For example, in the clause UPDATE {TRACKING|VIEWSTAT}[,...], the | character indicates that you can use either TRACKING or VIEWSTAT after UPDATE.

And

[...] and [,...] - Square brackets containing an ellipsis indicate that the preceding element can be repeated up to the limit for the element. If a comma is also present, the repeated elements must be separated by commas. If the element is a list of choices grouped with curly braces, you can use items from the list in any order. For example, in the clause UPDATE {TRACKING|VIEWSTAT}[,...], the [,...] indicates that you can use TRACKING, VIEWSTAT, or both:

So, following query works for me for querying Knowledge Article using VIEWSTAT,TRACKING with FOR REFERENCE

Valid

List<Vendor_Sheet__ka> lstKav = [SELECT Id,LastViewedDate 
From Vendor_Sheet__ka WHERE Id = 'kA2q00000008UvV' 
FOR REFERENCE UPDATE VIEWSTAT,TRACKING];
System.debug('LastViewedDate=' + lstKav[0].LastViewedDate);

Tracking SOQL

That it also working for FOR VIEW

List<Vendor_Sheet__ka> lstKav = [SELECT Id,LastViewedDate 
From Vendor_Sheet__ka 
WHERE Id = 'kA2q00000008UvV' 
FOR VIEW UPDATE VIEWSTAT,TRACKING];
System.debug('LastViewedDate=' + lstKav[0].LastViewedDate);

update tracking for view

Invalid Scenario:

If I put wrong id (i.e. Knowledge article Id) to retrieve the Knowledge article version like this:

List<Vendor_Sheet__kav> lstKav = [SELECT Id From Vendor_Sheet__kav 
                                  WHERE Id='kA2q00000008UvV' 
                                  FOR VIEW UPDATE VIEWSTAT];

It shows me error message:

FOR VIEW/REFERENCE can only be used on an object where viewing is tracked

That's interesting !!!

So, accordingly to me based on error message, Salesforce supports either FOR VIEW or FOR REFERENCE and not both.

The pipeline between two words clearly applicable in case of FOR {VIEW | REFERENCE}

Also I am interpreting the pipe in ORDER BY fieldOrderByList {ASC|DESC} where same field cannot be used for both ASC and DESC.

Invalid

List<Account> acct = [SELECT Id, Name FROM Account 
Order By Name ASC, DESC LIMIT 2];

Valid

List<Account> acct = [SELECT Id, Name FROM Account 
Order By Name ASC, Id DESC LIMIT 2];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.