function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
JayseJayse 

Apex query returns wrong value from formula field on custom object

Hi,

 

I have a simply query in my apex class to return a record from a custom object, code below:

 

userIncentivePlan = [select name, isActive__c, owner.name, Target__c, Growth_Incentive_Rate__c from Incentive_Plan__c where user__c =:UserId and isActive__c='True'];

 With the "isActive__c = 'True'" where criteria, the query fails to return any results, therefore I am assuming it  always evaluates to false. (also confirmed this by printing it out to a visual force page)

 

the formula on the field contains some nested if statements but always equates to 'True' or 'False', code below:

 

if (User__r.Profile.Name = 'System Administrator',  Incentive_Rate__r.isActive__c , 
if (User__r.Profile.Name = 'Corporate Sales Manager', Incentive_Rate__r.isActive__c, 
if (User__r.Profile.Name = 'Corporate Account Developer', Growth_Incentive_Rate__r.isActive__c, 
if (User__r.Profile.Name = 'Corporate Account Manager', Growth_Incentive_Rate__r.isActive__c, 
if (User__r.Profile.Name = 'Corporate Sales Super User', Incentive_Rate__r.isActive__c, 
if (User__r.Profile.Name = 'Account Development Manager', 
  if ( AND ( Incentive_Rate__r.isActive__c ='True',  Growth_Incentive_Rate__r.isActive__c ='True'), 'True', 'False'), 'False')
))))) 

The isActive__c field reference on the incentiveRate object is also a formula field [could that be the cause of the problem??]

 

Strangely, when I view the custom object record through salesforce, it evaluates correctly to 'True'

 

Having seen this forum posing, it suggests what I have done is correct : link

.... however it is not working. 

 

Any suggestions on how I can go about trying to resolve this would be appreciated.

 

Thanks in advance.

Jerun JoseJerun Jose

If you're seeing it through the UI, the query should show nothing different.

 

Can you just try this script through the developer console and see what the output is?

 

system.debug('@@@@@@@ value is '+[select isActive__c from Incentive_Plan__c where Id = 'xxxxxxxxxx'].isActive__c);

 

replace xxxxxxx with the ID of a Incentive_Plan__c record which shows the IsActive as true when looking at the UI.

 

If the debug shows True( which it should), then it might be something with the other filter.
JayseJayse

Thanks for the suggestion, running that code returns false in the developer console. 
Jerun JoseJerun Jose

Hmm.. There must be something silly here .. Could you try refreshing the page on the browser to make sure it shows True on the page layout. Can you verify if the API name is correct for the field we are checking.

 

Sorry that I sound so silly, but your scenario seems to question the fundamental behaviour of an SOQL query.
JayseJayse

That's fine Jerun,

 

I've refreshed the page (page layout ) several times to sanity check for myself that it's not something silly, returns True every time.

 

There is only 1 "is active" field on the custom object, with the API name "isActive__c"

 

you last comment was what I was beginning to think...  

 

Final though is that I need to retreive the incentive rate and or user records as part of the query (as the formula field refers to those fields) ... unsure how to re-build the query to do this though.
Jerun JoseJerun Jose

I would still wait and figure out why the SOQL is not reflecting the page layout data. Lets wait for some others to check on this.
NicolaiPNicolaiP

Funny. I have exactly the same issue. I have a "SELECT" query on 10-15 different "fields" from  "Assets". I'm then handling the results, but some of the values are "null" even though I can see in th UI that they have value. The funny thin is that if I delete almost all of the fields in the query and only ask for 1-2 fields, I get the correct value! Even on the fields that didn't bring a value when I asked for more fields.

 

So the same query object return a "null" value if I ask for 10-15 different fields in same query, but return the correct value if I onyl ask for 1-2 different fields in the same query.

 

Any insights? 

 

 
NicolaiPNicolaiP

Since I'm a newbie in SOQL, I thought an example would be appropriate_

 

This SELECT return a "null" for the Unit_Price__c value:

"SELECT CreatedById, CreatedDate, Deal_Type__c, IsDeleted, Install_Date__c, Install_Sell_Out_Date__c, LastModifiedById, LastModifiedDate, Opportunity__c, Order_Date__c, Install_Outlook__c, Outlook_Install_Date__c, Product__c, Quantity__c, Id, Total_Price__c, Transaction_type__c, Unit_Price__c FROM Equipment_Data__c"

 

This SELECT return a correct value" for the Unit_Price__c value:

"SELECT Id, Unit_Price__c FROM Equipment_Data__c"

 

Same rutine, some code pieace - just activation the one of the two SELECT lines... It doesn't make any sence - or does it?

 

Thanks.

 

 
JayseJayse

hummmm, i tried cutting down the fields to be returned in my query, but it still doesn't correctly evaluate the formula field.

 

Hope someone else can comment / explain this behaviour...

 

 
JayseJayse

Has anyone managed to resolve this?

 

I'm still stuck on this and loathe to have to code / program around it.
Danny@KCTSDanny@KCTS

Just to add context around this I am seeing a similar issue just using standard Salesforce formula. I have an extra long formula to generate a single digit code at the account level, it is too long to fit in one field so I am working around using two field updates and one formula to bring them together. 

The formula works fine and generates the correct digit in the UI for every record but when I run a report a small percentage give me an incorrect value, i have checked 20 or so different records and re-run different reports and every time I see an incorrect value in the report and a correct one in the record UI.

 

Afraid its not a solution but hopefully this will help to inform any fix!

 

P.S. I managed to resolve this by modifying the IF logic in my joining formula but that does not explain the error in the first place.
Faiza Naz 10Faiza Naz 10
well i was facing this same issue and the solution i have implement is a blind hit but sharing it here to help other.

My formula field use "Contains" function , so i removed that contains function and resaved it , then it started working fine.
BradCBradC
I am running into this as well. I'm not using the CONTAINS function. The web page shows the formula checkbox field is checked even though when I sign into the same user on the API and run a SOQL query, it shows the checkbox false. There is no contextual elements in the formula (and even if they were, I'm using the same SFDC account). Very strange.
pentayah yuvrajpentayah yuvraj
I am having he same issue.
Formula field referencing parent value is able to evaulate correctly on UI but not in SOQL.

Example : 
- Formula = Account.CreationDate__c
- UI = 23/09/2019
- SOQL = SELECT id, Name, Account.CreationDate__c FROM Contact (Result : Account.CreationDate__c = null)

This is creating inconsistencies for the business.