Is there a model condition that returns records by comparing two fields on the same record

Questions
1

Is there a way to build a model condition that returns records based on comparing two fields in the same row?

For example:
I have a Salesforce formula field that returns TEXT: let’s call it “formula__c”
I have a Salesforce Picklist field: let’s call it “picklistfield__c”
I want to return all records where “formula__c” <> “picklistfield__c”

I’ve seen a few topics that attempt to address this issue, but I have not seen a successful solution.
Things I’ve tried:

  • setting the “Field from another model”.
    I’ve tried it with both the “any row” and the “first row”, but it did not work because I am not trying to compare the value with any/all of the values in the model, I want to compare the same row with itself.

  • Subquery.
    This did not work because I need the same object in the subquery that I have in the original query, and the system won’t allow that.

  • Single Specified value with a merge field. {{formula__c}}
    I’m guessing this did not work because it couldn’t resolve the field since there is technically no data in the model yet.

I was hoping I could have had something similar to the lookup filters with the option “another field from row”.

  • I also tried adding a new field in Salesforce that did the comparison for me and returned a checkbox, but, alas, the dreaded “Error: Compiled formula is too big to execute” squashed that idea.

Any solutions out there?

2

The only way I’ve seen this work is by doing the field to field comparison in a formula field on Salesforce (Maybe a boolean), and then running your condition on that formula field.

3

Thanks for replying Rob. That workaround is the only thing I have found to work as well. Unfortunately, in this case, the comparison in the formula field is too long for the Salesforce compiler so I can’t perform that workaround. I was hoping there was something else, until I found this nugget on the interwebs:
“Salesforce does not allow direct field to field comparison in SOQL query.”
I guess it was not a consideration when they built the SOQL query language.

image
image1024×340 20.1 KB

https://help.salesforce.com/s/articleView?id=000331053&type=1

4

If a formula field can’t be used to do this conditioning, the only other way I’m aware of to do it would be querying all the data you are looking for and then processing the queried data yourself (eg. via javascript and looping over the data to check on it, then removing rows that shouldn’t be there)

This is what I do when Salesforce limits me; in particular when I’m trying to use too many subquery conditions I’ll need to just query multiple models and then intelligently merge them. It’s unfortunately far more painstaking to do this, but you can get the data you’re looking for this way.