Triaging The Salesforce Error Message “field 'NewValue' cannot be filtered in a query call”

Have you encountered this error message? Are you wondering where it comes from, and what it means?

{
    "message": "SELECT Id FROM LeadHistory WHERE NewValue = 'great' ^ ERROR at Row:1:Column:34 field 'NewValue' cannot be filtered in a query call",
    "errorCode": "INVALID_FIELD",
}

I was assigned a bug ticket describing how the customer encountered this error. I set out to perform a root cause analysis. I’m documenting the main parts of that process. If you happen to encounter a similar error message, hopefully this will help shed some clues, even if the clues indicate that the bug you are looking at is a different one.

Steps For Triage

Here are the steps I take when I triage a problem.

1. Read the description of the ticket. Read the error message.

2. Write down what you know and don't know.

   Write down what you understand and don’t understand about the application that is displaying the error. Write down what you know and don’t know about the error message.

   Share this with your teammates and ask them questions to see if they have any clues. Your teammates can include engineers, product managers, and the support representatives who communicate with customers and prepare the bug tickets.

3. Come up with a plan to isolate the problem and check your hunch(es).

   The goal is to replicate the bug. By replicating the bug, you can confirm your understanding of the behavior of the product. You can relay that information to the support representatives who will then help the customers understand why they encountered the bug. Again, share your plans with your teammates by thinking out loud. They can help you fill in any details, or help you consider looking into another facet of the software, that you might have missed.

4. Replicate the bug.

   This step usually goes hand-in-hand with Step 3 above. You’re doing detective work, and your hunches will change as you work through them.

   Take screenshots to document the results of your findings. You might have multiple plans to try out before reaching a conclusion, and it is important to keep track of your findings. Share your findings with your teammates.

5. Propose a solution or a few options that would resolve the bug, if possible.

6. Based on the proposed solutions, estimate the effort required and value, and prioritize the bug.

   This is usually involves a discussion with the product managers or anyone else on the team who will weigh in on the decisions. Some problems are considered low priority, and it’s ok to decide it is not worth sinking more time into them.

7. Mark the resolution status of the ticket.

It helps to think out loud and bounce your thoughts with your teammates along each step of the way. They will have context and perspectives that you won’t have. I like to post my thought process in jira comments, or on the team’s slack channel, as I work through them. That way, it is visible, and if my coworkers see my comments, they can point out the things I haven’t thought about, which helps us resolve the problem faster.

Let’s get right to the root cause of the error message “field 'NewValue' cannot be filtered in a query call”. I’ll walk through each step briefly, and go in detail in Step 4 to show you how you can reproduce this error message in Salesforce.

1. Read Description Of Ticket. Read Error Message.

The ticket that our support representatives prepared describes the actions the customer was taking that led to this bug. Our product has a Salesforce integration, and it was making a query to Salesforce’s SOQL query endpoint.

The error message:

{
    "message": "SELECT Id FROM LeadHistory WHERE NewValue = 'great' ^ ERROR at Row:1:Column:34 field 'NewValue' cannot be filtered in a query call",
    "errorCode": "INVALID_FIELD",
}

2. What I Know and Don’t Know

What I know:

• The error happens when the SOQL query is being executed by Salesforce.

What I don’t know:

• Why couldn’t the field be filtered in the query call? What is special about this field? Why were some fields able to be filtered on, but not this one?
• What is a LeadHistory? What is a “History Object”? Our product manager mentioned something about “History Objects”, and I was not familiar with this concept, yet.

3. Hunches And Plans

A quick internet search on the error message led me to Salesforce’s documentation on encrypted fields. The INVALID_FIELD error code made it seem possible that there was an encrypted field at play. However, after the support representative on my team checked with the customer, it was ruled out.

I had a few hunches at this point:

• The field was a TextArea field.
• The field was an encrypted field.
• The History Object has different properties than non-History Objects.

After my initial internet search, I asked the support representative to find out if the customer’s salesforce field was a TextArea field or an encrypted field. It turns out it was neither. However, the field was a History Object field. So, I was able to rule out the first two hunches, and follow the investigation on the third.

A quick internet search didn’t yield any documentation about filtering on History Object fields. (I may not have searched enough.) I set out to reproduce the error myself, which leads us to Step 4.

4. How To Reproduce Salesforce Error Message “field 'NewValue' cannot be filtered in a query call”

1. Sign up for Salesforce developer instance. (I'll leave out the steps for that here.)

2. Login. I like to use the classic view, so I switch to it. Find it under the Profile Menu > Switch to Salesforce Classic. (The “Profile Menu” is what I’m calling the avatar that you see in the Lightning view, or the user name in the Classic view.)

   

3. Open the “Setup” view. The link for that is at the top of the page.

   

4. Find the object for which you want to add Field History Tracking. I want to add Field History Tracking to the Lead object. In the left hand panel, I navigate to Build > Customize > Leads > Fields.

   

5. This brings up the Lead Fields setup page.

   

6. Enable the history tracking on this object by clicking on the “Set History Tracking” button on the Lead Fields setup page. Then mark the checkbox for “Enable Lead History”, and mark the checkboxes for the fields to track their history.

   

7. (Optional) Create some Lead objects, and change their fields to create some data that you can query and filter on. (I will skip these steps here, because you can raise the error without data!)

8. Open the developer console. Find it under Profile Menu > Developer Console. This will open up a new window for the Developer Console, which allows us to test out our SOQL queries.

   

9. Go to File > Open Resource.

   

10. Search for and select the LeadHistory object. Click the Open button at the bottom right.

    

11. You should see the Query Editor tab. Modify the query to include a filter on the NewValue or OldValue. These fields are tracking the changes to the Lead fields that have history tracking turned on.

    

12. Click the Execute button at the lower left corner. The error message is reproduced!

    

5. Propose Solutions

These are the two solutions to address this error:

• Don’t allow our product to apply filters on the field whenever making a query on the History Objects. It is fine for our product to apply filters for non-History Objects, just not the History Objects.
• Provide a better error message, instead of displaying Salesforce’s error message to the customers.

6. Estimate Effort and Value, And Prioritize

I shared the reproduced bug and the proposed solutions with the product manager, and they were able to make a decision given these options.

7. Mark Resolution Status

I marked the bug ticket as DONE. The product manager and support representatives are now informed of why the bug happened, and what the solution will be. They were able to prioritize the task on the product roadmap, and communicate with customers in a timely manner about the cause and the expected solution delivery date.

TIL

I learned that:

• In Salesforce, you can track the history of a field. When you track the history of a field, there is a History Object created to store the changes to that field. The History Object is different from the Object of the field. For example, if the Title field in the Lead object has history tracking turned on, there will be a LeadHistory object. The LeadHistory object will store the history of changes for the Title field in the LeadHistory.OldValue and LeadHistory.NewValue fields.
• The Field History Tracking fields cannot be filtered in a query.
• Field can be encrypted, and if they are encrypted, they cannot be filtered in a query.
• Salesforce’s developer console allows you to test out your SOQL queries.
• I didn't learn all the details about Salesforce's History Objects, and it wasn't necessary. As part of triage, it was my job to be efficient. That means doing just enough while being thorough to confidently identify the root cause, propose a solution, and move onto the next bug ticket!

Thanks for reading! I really enjoyed documenting the triage process for this specific error. I hope you enjoyed this post!