Formula field not populating on record creation

Hi Zach Gonzalez ,

In order to best assist, please share the formula you are using to evaluate whether the call/meeting is completed in the current quarter.

Chris

Hi Chris,

  here is the formula I am using. I know in a perfect world i could use your upsert model (we are discussing getting this still internally), but I would like to understand the root of the issue. If I create a new call record then the formula works on save. However I am noticing for my end users, that is not always the case, its capturing maybe half the time but if you go back and edit/save then the formula populates.

ifElse(and(equal($status,"Held"),equal($direction,"Outbound"),isAfter($date_start,09/30/2024)),1,0)

Hi Zach Gonzalez ,

I am confident the issue is with the explicit date value you have in your formula. Since Sugar allows users to specify their own date format, users who do not use the mm/dd/yyyy format would not be able to validate this formula on save. I am assuming you do use that date format which would explain why the calculation triggers when you resave. To mitigate this issue, try the following formula:

ifElse(
    and(
        equal(
            $status,
            "Held"
        ),
        equal(
            $direction,
            "Outbound"
        ),
        isAfter(
            $date_start,
            date(
                string(
                    09/30/2024
                )
            )
        )
    ),
    1,
    0
)

Using the date & string formulas will ensure it converts the date to the user's format and be able to successfully validate.

With that being said, I suggest a different approach for validating whether the call is in the current quarter. With your current formula, you would need to continuously go back and adjust the formula to account for each quarter. You can use monthofyear() and year() formulas to evaluate whether the call is taking place in the current quarter in a formula like the following:

ifElse(
    and(
        equal(
            $status,
            "Held"
        ),
        equal(
            $direction,
            "Outbound"
        ),
        equal(
            year(
                $date_start
            ),
            year(
                today()
            )
        )
    ),
    ifElse(
        or(
            and(
                not(
                    greaterThan(
                        monthofyear(
                            $date_start
                        ),
                        3
                    )
                ),
                not(
                    greaterThan(
                        monthofyear(
                            today()
                        ),
                        3
                    )
                )
            ),
            and(
                not(
                    greaterThan(
                        monthofyear(
                            $date_start
                        ),
                        6
                    )
                ),
                not(
                    greaterThan(
                        monthofyear(
                            today()
                        ),
                        6
                    )
                ),
                greaterThan(
                    monthofyear(
                        $date_start
                    ),
                    3
                ),
                greaterThan(
                    monthofyear(
                        today()
                    ),
                    3
                )
            ),
            and(
                not(
                    greaterThan(
                        monthofyear(
                            $date_start
                        ),
                        9
                    )
                ),
                not(
                    greaterThan(
                        monthofyear(
                            today()
                        ),
                        9
                    )
                ),
                greaterThan(
                    monthofyear(
                        $date_start
                    ),
                    6
                ),
                greaterThan(
                    monthofyear(
                        today()
                    ),
                    6
                )
            ),
            and(
                greaterThan(
                    monthofyear(
                        $date_start
                    ),
                    9
                ),
                greaterThan(
                    monthofyear(
                        today()
                    ),
                    9
                )
            )
        ),
        1,
        0
    ),
    0
)

I hope this helps!

Chris

Hi Zach Gonzalez ,

I am confident the issue is with the explicit date value you have in your formula. Since Sugar allows users to specify their own date format, users who do not use the mm/dd/yyyy format would not be able to validate this formula on save. I am assuming you do use that date format which would explain why the calculation triggers when you resave. To mitigate this issue, try the following formula:

ifElse(
    and(
        equal(
            $status,
            "Held"
        ),
        equal(
            $direction,
            "Outbound"
        ),
        isAfter(
            $date_start,
            date(
                string(
                    09/30/2024
                )
            )
        )
    ),
    1,
    0
)

Using the date & string formulas will ensure it converts the date to the user's format and be able to successfully validate.

With that being said, I suggest a different approach for validating whether the call is in the current quarter. With your current formula, you would need to continuously go back and adjust the formula to account for each quarter. You can use monthofyear() and year() formulas to evaluate whether the call is taking place in the current quarter in a formula like the following:

ifElse(
    and(
        equal(
            $status,
            "Held"
        ),
        equal(
            $direction,
            "Outbound"
        ),
        equal(
            year(
                $date_start
            ),
            year(
                today()
            )
        )
    ),
    ifElse(
        or(
            and(
                not(
                    greaterThan(
                        monthofyear(
                            $date_start
                        ),
                        3
                    )
                ),
                not(
                    greaterThan(
                        monthofyear(
                            today()
                        ),
                        3
                    )
                )
            ),
            and(
                not(
                    greaterThan(
                        monthofyear(
                            $date_start
                        ),
                        6
                    )
                ),
                not(
                    greaterThan(
                        monthofyear(
                            today()
                        ),
                        6
                    )
                ),
                greaterThan(
                    monthofyear(
                        $date_start
                    ),
                    3
                ),
                greaterThan(
                    monthofyear(
                        today()
                    ),
                    3
                )
            ),
            and(
                not(
                    greaterThan(
                        monthofyear(
                            $date_start
                        ),
                        9
                    )
                ),
                not(
                    greaterThan(
                        monthofyear(
                            today()
                        ),
                        9
                    )
                ),
                greaterThan(
                    monthofyear(
                        $date_start
                    ),
                    6
                ),
                greaterThan(
                    monthofyear(
                        today()
                    ),
                    6
                )
            ),
            and(
                greaterThan(
                    monthofyear(
                        $date_start
                    ),
                    9
                ),
                greaterThan(
                    monthofyear(
                        today()
                    ),
                    9
                )
            )
        ),
        1,
        0
    ),
    0
)

I hope this helps!

Chris

Thank you Chris, i will give this a shot and let you know if it works!

HI Chris,

   I tried the first formula you provided and It seems to have stopped all anticipated function. Values started coming back as blank. When i changed back to my original formula and recalculated values, It returned the anticipated results. Back to the drawing boards for me!

Hi Zach,

Is there a reason the second formula I provided is not suitable for your needs? 

For the first formula, you could try my suggested formula with 2024-09-30 instead of 09/30/2024, but it's not something I recommend due to the ongoing maintenance.

Chris

Hi Chris, sorry to leave you hanging. Our sugar instance went down yesterday so I got a little distracted. I will give the second formula a try today and let you know!