You need to sign in to do that

Don't have an account?

snippets@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_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.SELECTn1__c , n2__cFROM MATH__cWHEREnum1num2Add = 100You can remove this field from pagelayout if you do not with to show it on UI.

Kamatchi 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 kudosandmark this as a solution, if this works out.snippets@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 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 kudosby clicking on theStar iconandmark this as a solution, if this works out.snippets@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_sfdc

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

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.

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@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

Thanks,

Snippets

Peter_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@sfThanks Peter.

you were a great help to me.

At least now I know what should be done.