Formula field not populating on record creation

Hi All, 

  I have a custom field on my call and meeting module to count if the call or meeting is completed and is in this quarter. The formula is working when I am reviewing records and saving them. However, I am seeing that when my end users create new records that meet the criteria, the field is not populating on creation. I still have to go in and open the record, edit and save it for the logic to kick in. Now I know sugar doesn't just run updates inherently but shouldn't this work when new records are created? Is there a setting I am missing in the field that is causing this? Any insight would be greatly appreciated! 

Parents
  • Hi  ,

    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  ,

    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

Reply
  • Hi  ,

    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

Children