Sytax formula for date (6 days before)

Hi All Slight smile

I have a scenario in which we would like an Action button to appear 6 days before a date field in a related module and after the date has passed

For example

The date is set to = 1/10/2023

We would like the action button to appear from - 25/09/2023 and after 1/10/23

Working with good old ChatGPT I have managed to create this formula, Sugar saves it, but it is not working

isBefore(related($Accounts, "delivery_date"), addDays(today(), -6))

Any help and suggestions would be greatly appreciated Slight smile

Thanks again

Parents
  • Hi  ,

    ChatGPT is quite a bit off base with what your requirements are. First, your requirements have an upper and lower bound and the ChatGPT only has one bound. Second, you want the button to appear within the 6 days before your date field's value, but the ChatGPT formula would only show the button if the current day was at least 6 days past your account date field. Here is the formula you should use to properly display the action button:

    and(
        isAfter(
            today(),
            addDays(
                related(
                    $accounts,
                    "delivery_date"
                ),
                -7
            )
        ),
        isBefore(
            today(),
            related(
                $accounts, 
                "delivery_date"
            )
        )
    )

    The isAfter function compares against a date 7 days in the past in order to display the button on the 6th day.

    isBefore does not require a similar offset (e.g. adding 1 day) as it validates to true if both compared dates are the same. I'm not sure if that is intended or not since the formula description for isBefore states it should only evaluate to true if the first date is less than the second date. I could not find any open bugs on this specific functionality with isBefore. Be aware it could change in the future if they update the formula's function to match the description as it is written.

    Chris

Reply
  • Hi  ,

    ChatGPT is quite a bit off base with what your requirements are. First, your requirements have an upper and lower bound and the ChatGPT only has one bound. Second, you want the button to appear within the 6 days before your date field's value, but the ChatGPT formula would only show the button if the current day was at least 6 days past your account date field. Here is the formula you should use to properly display the action button:

    and(
        isAfter(
            today(),
            addDays(
                related(
                    $accounts,
                    "delivery_date"
                ),
                -7
            )
        ),
        isBefore(
            today(),
            related(
                $accounts, 
                "delivery_date"
            )
        )
    )

    The isAfter function compares against a date 7 days in the past in order to display the button on the 6th day.

    isBefore does not require a similar offset (e.g. adding 1 day) as it validates to true if both compared dates are the same. I'm not sure if that is intended or not since the formula description for isBefore states it should only evaluate to true if the first date is less than the second date. I could not find any open bugs on this specific functionality with isBefore. Be aware it could change in the future if they update the formula's function to match the description as it is written.

    Chris

Children
No Data