Formula issue

I have tried so many options - i did manage to get the formula to work initially when it was less complicated but have added in an additional section and cant figure out where i have gone wrong - if anyone could help that would be amazing!

The formula should read:

if Planning Required is Yes AND 35% received is 0, then add 30 days to the Approved Date,

if not and Planning Required is No AND 35% received is 0, the put the 10% received date,otherwise put "0"

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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),
)
"0",
)
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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),
)
"0",
)

thank you so much in advance for anyone that is able to help!

Parents

0 Chris Raffle 3 months ago
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:

Fullscreen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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"
),
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
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
Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year
0 Penny Whitlock 3 months ago in reply to Chris Raffle

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

0 Chris Raffle 3 months ago in reply to Penny Whitlock

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:

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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"
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

0 Penny Whitlock 3 months ago in reply to Chris Raffle

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 :(

0 Chris Raffle 3 months ago in reply to Penny Whitlock

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:

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
date(
    ifElse(
        and(
            equal(
                $pr_planning_required_c,
                "Yes"
            ),
            equal(
                $thirtyfive_payment_received_c,
                0
            ),
            not(
                isInList(
                    $project_type,
                    createList(
                        "Supply Only"
                    )
                )
            )
        ),
        addDays(
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

Reply

0 Chris Raffle 3 months ago in reply to Penny Whitlock

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:

Fullscreen

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
date(
    ifElse(
        and(
            equal(
                $pr_planning_required_c,
                "Yes"
            ),
            equal(
                $thirtyfive_payment_received_c,
                0
            ),
            not(
                isInList(
                    $project_type,
                    createList(
                        "Supply Only"
                    )
                )
            )
        ),
        addDays(
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

Co-Founder | SugarCRM's 2024 Global Outfitters Partner of the Year

Children

No Data