Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Lukasz PiziakLukasz Piziak 

Formula to calculate value based on many number fields.

Hi all,
I'm looking for a help with creating formula field based on four number fields (Qty1_c, Qty2_c, Qty3_c, Qty4_c) :

If Qty1_c > 0
and Qty2_c = no value
return ashould be = Qty1_c

if Qty2_c > 0
and Qty3_c = no value
return shoudl be = Qty2_c

if Qty3_c > 0
and Qty4_c = no value
return shoudl be = Qty3_c

if Qty3_c > 0
and Qty4_c > 0
return shoudl be = Qty4_c

Thanks for help.

 
Best Answer chosen by Lukasz Piziak
Keyur ModiKeyur Modi
Hi,
i can understand the issue , see in the formula we are checking for null and here in the qty2 u are entering 0 that's why this issue happen .
To resolve this issue you can put condition like this.

IF((Qty_1__c > 0 && (Qty_2__c =null || Qty_2__c=0 )), Qty_1__c, IF((Qty_2__c > 0 && (Qty_3__c =null|| Qty_3__c=0 )), Qty_2__c ,IF( (Qty_3__c > 0 && (Qty_4__c =null || Qty_4__c =0 ), Qty_3__c ,IF((Qty_3__c > 0 && Qty_4__c > 0), Qty_4__c ,null) ) ))

please let me know if this will help,

Thanks,
Keyur Modi

 

All Answers

Lukasz PiziakLukasz Piziak
Hi Keyur, Thank you for provided code. I have used and I'm getting the following error, could you please advise. Error message: Error: Incorrect parameter type for function 'IF()'. Expected Number, received Boolean Code I have used: IF( Qty_1__c > 0 && Qty_2__c =null, Qty_1__c, IF( Qty_2__c > 0 && Qty_3__c =null, Qty_2__c , IF( Qty_3__c > 0 && Qty_4__c =null, Qty_3__c , IF( Qty_3__c > 0 && Qty_4__c > 0, Qty_4__c ,false)))) Regards, *Lukasz* *Lukasz Piziak *| IT | SalesForce *hose*tech | euro business park | little island | co. cork Tel: +353 (021) 4520600 Fax: +353 (021) 4354906 email: lpiziak@hosetech.ie total hose technology
Keyur ModiKeyur Modi
Hi,
I think your ReturnType of the Fromula field is number that's why it is giving that error inplace of false put null or 0(zero).
just like below code.

IF((Qty_1__c > 0 && Qty_2__c =null), Qty_1__c, IF((Qty_2__c > 0 && Qty_3__c =null), Qty_2__c ,IF( (Qty_3__c > 0 && Qty_4__c =null), Qty_3__c ,IF((Qty_3__c > 0 && Qty_4__c > 0), Qty_4__c ,null) ) ))


Thanks,
Keyur Modi
Lukasz PiziakLukasz Piziak
Hi Keyur, I have used exactly the same code and I was able to save it without any errors, but when checking formula fields is not populating values as per formula used. e.g I have qty 1 = 1 and qty 2 = 0 and formula field is not populating value 1 [image: Inline images 1] Regards, *Lukasz* *Lukasz Piziak *| IT | SalesForce *hose*tech | euro business park | little island | co. cork Tel: +353 (021) 4520600 Fax: +353 (021) 4354906 email: lpiziak@hosetech.ie total hose technology
Keyur ModiKeyur Modi
Hi,
i can understand the issue , see in the formula we are checking for null and here in the qty2 u are entering 0 that's why this issue happen .
To resolve this issue you can put condition like this.

IF((Qty_1__c > 0 && (Qty_2__c =null || Qty_2__c=0 )), Qty_1__c, IF((Qty_2__c > 0 && (Qty_3__c =null|| Qty_3__c=0 )), Qty_2__c ,IF( (Qty_3__c > 0 && (Qty_4__c =null || Qty_4__c =0 ), Qty_3__c ,IF((Qty_3__c > 0 && Qty_4__c > 0), Qty_4__c ,null) ) ))

please let me know if this will help,

Thanks,
Keyur Modi

 
This was selected as the best answer