Formula issue

Hi Penny Whitlock ,

There are a few minor corrections to the formula. I think you have all the right elements based on your business requirements, but the formatting is off:

• Your second ifElse formula has improper capitalization
• Your second ifElse formula only has an action to execute if it is true since you have a parentheses immediately following the 10% received date field
• I'm assuming this formula is being used in a date field. If so, you can't set date fields to a value of 0, so I changed that to an empty string

Here is the formula I think should work:

ifElse(
    and(
        equal(
            $pr_planning_required_c,
            "Yes"
        ),
        equal(
            $thirtyfive_payment_received_c,
            0
        )
    ),
    addDays(
        $pr_approved_date_c,
        30
    ),
    ifElse(
        and(
            equal(
                $pr_planning_required_c,
                "No"
            ),
            equal(
                $thirtyfive_payment_received_c,
                0
            )
        ),
        $pr_10_percentage_payment_date_c,
        ""
    )
)

Let me know if that works!

Chris

Thanks Chris, i really appreciate your help and explanations - things are starting to make more sense!

I am now getting the error 'the formula must be of type date'

and yes it is going in a date field

This is an annoyance I have encountered when using nested ifElse formulas in the past; see if encasing the original formula I wrote in a date() formula works:

date(
    ifElse(
        and(
            equal(
                $pr_planning_required_c,
                "Yes"
            ),
            equal(
                $thirtyfive_payment_received_c,
                0
            )
        ),
        addDays(
            $pr_approved_date_c,
            30
        ),
        ifElse(
            and(
                equal(
                    $pr_planning_required_c,
                    "No"
                ),
                equal(
                    $thirtyfive_payment_received_c,
                    0
                )
            ),
            $pr_10_percentage_payment_date_c,
            ""
        )
    )
)

Chris

Hi Chris it seems to be working!

I have realised that i do not want these fields to apply to project type "Supply Only" - what is the best way for me to add a formula into this that is the most simple, yet effective? can you have a 3 way AND clause? i have had a go and it doesnt seem to like me again :(

Hi Penny Whitlock ,

Yes, you can have any number of conditions in your and() clause. Since I don't know the exact field name you are using for project type, I will use $project_type in the example:

date(
    ifElse(
        and(
            equal(
                $pr_planning_required_c,
                "Yes"
            ),
            equal(
                $thirtyfive_payment_received_c,
                0
            ),
            not(
                isInList(
                    $project_type,
                    createList(
                        "Supply Only"
                    )
                )
            )
        ),
        addDays(
            $pr_approved_date_c,
            30
        ),
        ifElse(
            and(
                equal(
                    $pr_planning_required_c,
                    "No"
                ),
                equal(
                    $thirtyfive_payment_received_c,
                    0
                ),
                not(
                    isInList(
                        $project_type,
                        createList(
                            "Supply Only"
                        )
                    )
                )
            ),
            $pr_10_percentage_payment_date_c,
            ""
        )
    )
)

Chris

Hi Penny Whitlock ,

Yes, you can have any number of conditions in your and() clause. Since I don't know the exact field name you are using for project type, I will use $project_type in the example:

date(
    ifElse(
        and(
            equal(
                $pr_planning_required_c,
                "Yes"
            ),
            equal(
                $thirtyfive_payment_received_c,
                0
            ),
            not(
                isInList(
                    $project_type,
                    createList(
                        "Supply Only"
                    )
                )
            )
        ),
        addDays(
            $pr_approved_date_c,
            30
        ),
        ifElse(
            and(
                equal(
                    $pr_planning_required_c,
                    "No"
                ),
                equal(
                    $thirtyfive_payment_received_c,
                    0
                ),
                not(
                    isInList(
                        $project_type,
                        createList(
                            "Supply Only"
                        )
                    )
                )
            ),
            $pr_10_percentage_payment_date_c,
            ""
        )
    )
)

Chris