ifElse with a condition in a different module whitin a calculated field

Hi Arthurs Carpentier ,

Welcome to Sugar! Your calculated formula has good structure for what you want to achieve. A few things come to mind as potential causes as well as some clarifying questions:

• Which module did you create the calculated field in? Opportunities or sa001_socleaffaire?
• What type of relationship exists between the Opportunities and sa001_socleaffaire module (e.g. one-to-one, one-to-many, etc.)? Do multiple relationships exist between the modules or is there only one? If the module containing the lot_c field is in the "many" side of a relationship, this formula will not work reliably as it will randomly pick one of the records to evaluate against if multiple records are related to the parent.
• For the lot_c dropdown field, does 'Lot 1' match the key value of the dropdown entry you are expecting to match on? The display value may be different and Sugar Logic will not work properly if trying to match on a display value.

If you are confident your relationship and dropdown key are appropriate for this use, then I recommend changing your ifElse condition from equal to isInList. I typically use isInList for dropdown fields even if I only want to check for a single value because the function gives me added flexibility to add additional values down the road if I decide I want to check for more than one value. Your formula would look like the following:

ifElse(
    isInList(
        related(
            $sa001_socleaffaire_opportunities_1,
            "lot_c"
        ),
        createList(
            "Lot 1"
        )
    ),
    subtract(
        $montant_1_c,
        add(
            $prelevements_1_c,
            $prelevement_2_c,
            $prelevement_3_c,
            $com_commerciale_c,
            $markup_3_c
        )
    ),
    subtract(
        $montant_1_c,
        add(
            $prelevements_1_c,
            $prelevement_2_c,
            $prelevement_3_c,
            $com_commerciale_c
        )
    )
)

Chris

Hi Arthurs Carpentier ,

Welcome to Sugar! Your calculated formula has good structure for what you want to achieve. A few things come to mind as potential causes as well as some clarifying questions:

• Which module did you create the calculated field in? Opportunities or sa001_socleaffaire?
• What type of relationship exists between the Opportunities and sa001_socleaffaire module (e.g. one-to-one, one-to-many, etc.)? Do multiple relationships exist between the modules or is there only one? If the module containing the lot_c field is in the "many" side of a relationship, this formula will not work reliably as it will randomly pick one of the records to evaluate against if multiple records are related to the parent.
• For the lot_c dropdown field, does 'Lot 1' match the key value of the dropdown entry you are expecting to match on? The display value may be different and Sugar Logic will not work properly if trying to match on a display value.

If you are confident your relationship and dropdown key are appropriate for this use, then I recommend changing your ifElse condition from equal to isInList. I typically use isInList for dropdown fields even if I only want to check for a single value because the function gives me added flexibility to add additional values down the road if I decide I want to check for more than one value. Your formula would look like the following:

ifElse(
    isInList(
        related(
            $sa001_socleaffaire_opportunities_1,
            "lot_c"
        ),
        createList(
            "Lot 1"
        )
    ),
    subtract(
        $montant_1_c,
        add(
            $prelevements_1_c,
            $prelevement_2_c,
            $prelevement_3_c,
            $com_commerciale_c,
            $markup_3_c
        )
    ),
    subtract(
        $montant_1_c,
        add(
            $prelevements_1_c,
            $prelevement_2_c,
            $prelevement_3_c,
            $com_commerciale_c
        )
    )
)

Chris