Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Supriyo Ghosh 9Supriyo Ghosh 9 

Formula field If else condition

Hi ,
I am having a formula field.I want to remove some condition but I am unable to do that.
IF(ISPICKVAL(Vertical__c,'LI'), 
IF(NOT(ISPICKVAL(ProductDetail__r.Pricing_Category__c,'ULIP')), 
((NULLVALUE(Base_Premium__c,0)+NULLVALUE(Rider_Premium__c,0))+ 
IF((Inception_Branch__c != '641'), 
IF((Principle_Code__c = 'MLI'), 
IF(Year_of_Policy__c=1, 
(NULLVALUE(Base_Premium__c,0)*0.045 )+(Rider_Premium__c*0.1800), 
(NULLVALUE(Base_Premium__c,0)*0.0225 )+(Rider_Premium__c*0.18)), 
IF(AND(ProductDetail__r.Scheme_Code__c!='BPP1',ProductDetail__r.Scheme_Code__c != 'BPP2'), 
NULLVALUE(Base_Premium__c,0)*0.045, 
NULLVALUE(Base_Premium__c,0)*0.1800)+(Rider_Premium__c*0.1800)), 
IF((Principle_Code__c='BSLI'), 
IF(AND(ProductDetail__r.Scheme_Code__c!='BPP1',ProductDetail__r.Scheme_Code__c!='BPP2'), 
NULLVALUE(Base_Premium__c,0)*0.045, 
NULLVALUE(Base_Premium__c,0)*0),0))),NULLVALUE(Base_Premium__c,0)), 

IF(ISPICKVAL(Vertical__c,'GI'), 
IF(AND(ISPICKVAL(ProductDetail__r.Pricing_Category__c ,'Motor'),ProductDetail__r.CommercialValue_Flag__c= False), 
(NULLVALUE(Own_Damage_Premium__c,0)+NULLVALUE(Third_Party_Premium__c,0) ) * (1.1800), 

IF(AND(ISPICKVAL(ProductDetail__r.Pricing_Category__c ,'Motor'),ProductDetail__r.CommercialValue_Flag__c= True ), 
(NULLVALUE(Own_Damage_Premium__c,0)+NULLVALUE(Third_Party_Premium__c,0) ) + NULLVALUE(Own_Damage_Premium__c,0) *0.18 + NULLVALUE(Third_Party_Premium__c,0) * 0.12 , 

IF(ISPICKVAL(ProductDetail__r.Pricing_Category__c,'Non-Motor'), 
(NULLVALUE(Base_Premium__c,0)+NULLVALUE(Terrorism_Premium__c,0)) * ( 1.1800) , 
NULLVALUE(Base_Premium__c,0)))), 

IF(ISPICKVAL(Vertical__c,'MF'), 
NULLVALUE(MF_Amount__c,0), 

IF(or(ISPICKVAL(Vertical__c,'HI'),ISPICKVAL(Vertical__c,'HC')), 
NULLVALUE(Base_Premium__c,0)*(1.1800),0))))

In the above mentioned formula I want to remove below mentioned portion.

IF(AND(ProductDetail__r.Scheme_Code__c!='BPP1',ProductDetail__r.Scheme_Code__c != 'BPP2'), 
NULLVALUE(Base_Premium__c,0)*0.045, 
NULLVALUE(Base_Premium__c,0)*0.1800)+(Rider_Premium__c*0.1800)), 
IF((Principle_Code__c='BSLI'), 
IF(AND(ProductDetail__r.Scheme_Code__c!='BPP1',ProductDetail__r.Scheme_Code__c!='BPP2'), 
NULLVALUE(Base_Premium__c,0)*0.045, 
NULLVALUE(Base_Premium__c,0)*0),0))),

But I am getting error.Please help.
Greg HGreg H
There’s a whole bunch of nested IF statements in there. So you cannot simply remove all that without rethinking how to handle the various IF conditions that you have in place. Nonetheless, I mostly removed the logic you requested and replaced it with zeroes. 
IF(
    ISPICKVAL(Vertical__c,'LI'), 
    IF(
        NOT(
            ISPICKVAL(ProductDetail__r.Pricing_Category__c,'ULIP')
        ),
        NULLVALUE(Base_Premium__c,0) + NULLVALUE(Rider_Premium__c,0) + 
            IF(
                Inception_Branch__c != '641',
                IF(
                    Principle_Code__c = 'MLI', 
                    IF(
                        Year_of_Policy__c = 1, 
                        (NULLVALUE(Base_Premium__c,0)*0.045)+(Rider_Premium__c*0.1800), 
                        (NULLVALUE(Base_Premium__c,0)*0.0225)+(Rider_Premium__c*0.18)
                    ), 
                    0
                ), 
                0
            ),
        NULLVALUE(Base_Premium__c,0)
    ), 
    IF(
        ISPICKVAL(Vertical__c,'GI'), 
        IF(
            AND(ISPICKVAL(ProductDetail__r.Pricing_Category__c ,'Motor'),ProductDetail__r.CommercialValue_Flag__c= False), 
            (NULLVALUE(Own_Damage_Premium__c,0)+NULLVALUE(Third_Party_Premium__c,0)) * 1.1800, 
            IF(
                AND(ISPICKVAL(ProductDetail__r.Pricing_Category__c ,'Motor'),ProductDetail__r.CommercialValue_Flag__c= True ), 
                (NULLVALUE(Own_Damage_Premium__c,0)+NULLVALUE(Third_Party_Premium__c,0)) + NULLVALUE(Own_Damage_Premium__c,0) *0.18 + NULLVALUE(Third_Party_Premium__c,0) * 0.12 , 
                IF(
                    ISPICKVAL(ProductDetail__r.Pricing_Category__c,'Non-Motor'), 
                    (NULLVALUE(Base_Premium__c,0)+NULLVALUE(Terrorism_Premium__c,0)) * 1.1800, 
                    NULLVALUE(Base_Premium__c,0)
                )
            )
        ), 
        IF(
            ISPICKVAL(Vertical__c,'MF'), 
            NULLVALUE(MF_Amount__c,0), 
            IF(
                or(ISPICKVAL(Vertical__c,'HI'),ISPICKVAL(Vertical__c,'HC')), 
                NULLVALUE(Base_Premium__c,0)*(1.1800),
                0
            )
        )
    )
)

You will likely need to alter it a bit. But this should get you closer.
-greg