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
snippets@sfsnippets@sf 

SOQL - Calculating values

Dose SOQL Support Calculating values.

 

For Example a Custom Object Math__c  has fields n1_c , and n2_c

 

what I am looking for is 

User enters a Value form UI say n3 = 50

 

My Query should work something like

 

SELECT n1__c , n2__c FROM MATH__c WHERE n1+n2+n3 = 100

 

Value 100 is a static value.

 

 

Please suggest how can i achieve this in single Query/ or is ther a alternate way.

 

Thanks

 

 

 

 

 

 

Vinita_SFDCVinita_SFDC

Hello,

 

Unfortunately it is not possible to add fields in where clause. Workaround is create a formula field like num1num2Add which will add these two fields.

 

Now apply filter on this formula field.

 

SELECT n1__c , n2__c FROM MATH__c WHERE num1num2Add = 100

 

You can remove this field from pagelayout if you do not with to show it on UI.

 

 

 

 
Kamatchi Devi SargunanathanKamatchi Devi Sargunanathan

Hi,

 

Can you please exaplin in detail of when the query is going to be used and why?

 

Because, you told that you will enter the value for n3 as 50, and in your query you are searching for condition n1+n2+n3 = 100 why?

 

And also, you can use only the field values to compare in where clause of the sobject SOQL.

 

Hope this will help you...!

Please don't forget to give kudos and mark this as a solution, if this works out.
snippets@sfsnippets@sf

Thanks KamatchiDeviR

 

n1 and n2 are db values.

n3 is the value recived at Query exceution time.

 

and 100 is a static value which is derived based on user's input independent from db values.

 

 
Kamatchi Devi SargunanathanKamatchi Devi Sargunanathan

Hi,

 

Yeah thanks for your explaination. Now, I can explain that SOQL Support Calculating values.

 

Try the following trigger as an example,

 

trigger testAdding on CMDdata__c (before insert) {
   integer n1 = 2;
   integer n2 = 3;
   
   for(CMDdata__c c: trigger.new){
     List<Cmddata__c> cc = [select id,num__c from Cmddata__c where num__c =: Integer.valueOf(c.num__c + c.num__c)];
     if(cc.size()>0){
           //your code here
     }    
   }
}

 

It works for calculation to check for a value of that calculation with a field that matches and retrieve the record.

In the above example, I have a custom object Object1__c and field as num__c. Im dynamically checking the field with its value added to itself.

 

Hope this will help you...!

 

Please don't forget to give kudos by clicking on the Star icon and mark this as a solution, if this works out.

 
snippets@sfsnippets@sf

Thanks Kamatchi.

 

I was looking for a third input which calulates with the db values and filters with a static value.

 

May be i din explain it right

 

 
Peter_sfdcPeter_sfdc

I guess the way I would tackle this is as follows. Your hoped for query is this: 

 

SELECT n1__c , n2__c FROM MATH__c WHERE n1__c + n2__c + n3 = 100

First, make a formula field that adds n1__c + n2__c. We'll call it n1_n2_sum__c

 

Then you need to adjust your math a bit. 

 

SELECT n1__c , n2__c FROM MATH__c WHERE n1_n2_sum__c = : 100 - n3

Since this formula field n1_n2_sum__c would be deterministic, I would also contact support and request they place a custom index on this formula field for improved query performance. 

 

Get to know declarative features, without them, you only know part of the platform.  
snippets@sfsnippets@sf

Thanks Peter-sfdc.

Thats a Brilliant approach.

 

 

But when we look at it closely the requirement need the value n3 to be processed with a db value n1__c and n2__c.

Example n1__c = 2500 n2__c = 10 n3 = 100

 

SELECT n1__c , n2__c FROM MATH__c WHERE ((n3*n2__c)+n1__c) = 3500

 

Thanks,

Snippets

 

 
Peter_sfdcPeter_sfdc

Shoot!

 

It had to be that! ;-)

 

I was wondering if there wasn't some more complicated math that you needed to peform in your calculation. 

 

I can only see two ways forward, I'm afraid.

 

1. Query for more records than what you need, and then loop over and collect the records that apply using your calculation. 

2. Somehow get the value of n3 into the DB too and add it to your formula field. 
snippets@sfsnippets@sf
Thanks Peter.
you were a great help to me.
At least now I know what should be done.