Jul 7, 2013

New SOQL Clauses and System Fields

Summer' 13 and v28.0 brought a few new SOQL features for developers.  Included in those features are the FOR VIEW and FOR REFERENCE SOQL Clauses and their related LastViewedDate and LastReferencedDate date/time fields.  The new fields, unlike the other system date/time fields are dependent on the current user, meaning your values will be different from mine (illustrated below).

Let's define what a view and a reference are, per the release notes.  Records are considered:
Viewed - "when the user sees the details associated with it, but not when the user sees it in a list with other records."  
Referenced = "when a related record is viewed."
Here's a sample scenario to help understand these fields and how the clauses can be used.

User A creates a "Kirk Steffke" Contact record that is related to a CRM Science Account.  Upon creation, the LastViewedDate and LastReferencedDate for both will the same as the CreatedDate.

Contact:  Kirk Steffke

Account:  CRM Science

Notice above the LastViewedDate and LastReferencedDate fields.  These values are for User A, the user that created both records.  Observe closely and you'll notice that the Account's LastReferencedDate is slightly later than the LastViewedDate.  This is due to creating the Account record through the "New" button in the Contact's Account Lookup window.  The Account is first referenced upon the Account record's creation and a second time when User A is taken to the newly created Contact record.  The reference comes from the Account Lookup field on the Contact.

Since these two fields are user dependent, let's see how the data differs for another user that has never accessed either record.

Account:  CRM Science as viewed by User B

Contact:  Kirk Steffke as viewed by User B

The LastViewedDate has an impact certain areas of the user interface such as the Recent Items list in the toolbar or the items that quickly appear when using the search feature.  Below is a side by side comparison for the two different users.  The new Contact and Account records do not display in the quick search pop-down or in the Recent Items.

As seen here, just like the other sytem date/time fields, they aren't directly updatable.

However, the new SOQL clauses will do just that:

Don't expect to see an updated value when you run the above query; the query will update the LastReferencedDate for any rows returned as they are returned.  When you use the FOR REFERENCE or FOR VIEW clauses, the values returned will never be the actual values, they'll be the last values.  To see the actual values without updating them, use the original query where the clauses are omitted.