Help getting a calculated field to work please?

Hi,

I need to add a field that returns an indication to the user, if a refund could be due when a customer cancels their order.

I have built out the logic but it fails to save every time. Have I made a syntax error or done this in the wrong way for Sugar Logic?

(I have tested separately both the lead_time and delay_days formulas and they both work fine returning the correct numbers)

This is the criteria for whether a refund is given or not:

Original Leadtime (Months)	Original Leadtime (Days)	Delay Tolerance (Days)
Stock/ <1 mth	0-28	28
1-2mth	29-56	42
2-3mth	57-84	56
3-4mth	85-112	70
4-5mth	113-140	84
5-6mth	141-168	98
6+mth	169+	112

lead_time = abs(subtract(daysUntil(related($pr_selected_vehicle,"pr_anticipated_delivery_date_first_update")), daysUntil($pr_date_procurement_signed_off_order)));
delay_days = abs(subtract(daysUntil(related($pr_selected_vehicle,"pr_anticipated_delivery_date_first_update")), daysUntil(related($pr_selected_vehicle,"pr_anticipated_delivery_date_new"))));
delay_tolerance = if(
    lead_time <= 28, 28,
    if(
        lead_time <= 56, 42,
        if(
            lead_time <= 84, 56,
            if(
                lead_time <= 112, 70,
                if(
                    lead_time <= 140, 84,
                    if(
                        lead_time <= 168, 98,
                        112
                    )
                )
            )
        )
    )
);
if(
    delay_days > delay_tolerance,
    "Refund Possible",
    "Refund Unlikely"
);



This is the error:

lead_time=abs(subtract(daysUntil(related($pr_selected_vehicle,"pr_anticipated_delivery_date_first_update")),daysUntil($pr_date_procurement_signed_off_order))); delay_days=abs(subtract(daysUntil(related($pr_selected_vehicle,"pr_anticipated_delivery_date_first_update")),daysUntil(related($pr_selected_vehicle,"pr_anticipated_delivery_date_new")))); delay_tolerance=if( lead_time<=28,28, if( lead_time<=56,42, if( lead_time<=84,56, if( lead_time<=112,70, if( lead_time<=140,84, if( lead_time<=168,98, 112 ) ) ) ) ) ); if( delay_days>delay_tolerance, "Refund Possible", "Refund Unlikely" );: Syntax Error, no close parentheses found

I hope someone can help!

Regards

Adam

Parents

0 Adam Ososki 1 month ago

Oops

I realised I had the actual calculation wrong and fixed that - but when I loaded it into the system - it completely crashed Sandbox! Any ideas why that might happen? I've asked IT to raise a ticket with the developer but it's good understand what I have done wrong...

ifElse(
    greaterThan(
        abs(
            subtract(
                daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_new"))
            )
        ),
        ifElse(
            isWithinRange(
                abs(
                    subtract(
                        daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                        daysUntil($pr_date_procurement_signed_off_order)
                    )
                ),
                0,
                28
            ),
            28,
            ifElse(
                isWithinRange(
                    abs(
                        subtract(
                            daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                            daysUntil($pr_date_procurement_signed_off_order)
                        )
                    ),
                    29,
                    56
                ),
                42,
                ifElse(
                    isWithinRange(
                        abs(
                            subtract(
                                daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                                daysUntil($pr_date_procurement_signed_off_order)
                            )
                        ),
                        57,
                        84
                    ),
                    56,
                    ifElse(
                        isWithinRange(
                            abs(
                                subtract(
                                    daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                                    daysUntil($pr_date_procurement_signed_off_order)
                                )
                            ),
                            85,
                            112
                        ),
                        70,
                        ifElse(
                            isWithinRange(
                                abs(
                                    subtract(
                                        daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                                        daysUntil($pr_date_procurement_signed_off_order)
                                    )
                                ),
                                113,
                                140
                            ),
                            84,
                            ifElse(
                                isWithinRange(
                                    abs(
                                        subtract(
                                            daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                                            daysUntil($pr_date_procurement_signed_off_order)
                                        )
                                    ),
                                    141,
                                    168
                                ),
                                98,
                                112
                            )
                        )
                    )
                )
            )
        )
    ),
    "Refund Possible",
    "Refund Unlikely"
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Adam Ososki 1 month ago

Oops

I realised I had the actual calculation wrong and fixed that - but when I loaded it into the system - it completely crashed Sandbox! Any ideas why that might happen? I've asked IT to raise a ticket with the developer but it's good understand what I have done wrong...

ifElse(
    greaterThan(
        abs(
            subtract(
                daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_new"))
            )
        ),
        ifElse(
            isWithinRange(
                abs(
                    subtract(
                        daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                        daysUntil($pr_date_procurement_signed_off_order)
                    )
                ),
                0,
                28
            ),
            28,
            ifElse(
                isWithinRange(
                    abs(
                        subtract(
                            daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                            daysUntil($pr_date_procurement_signed_off_order)
                        )
                    ),
                    29,
                    56
                ),
                42,
                ifElse(
                    isWithinRange(
                        abs(
                            subtract(
                                daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                                daysUntil($pr_date_procurement_signed_off_order)
                            )
                        ),
                        57,
                        84
                    ),
                    56,
                    ifElse(
                        isWithinRange(
                            abs(
                                subtract(
                                    daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                                    daysUntil($pr_date_procurement_signed_off_order)
                                )
                            ),
                            85,
                            112
                        ),
                        70,
                        ifElse(
                            isWithinRange(
                                abs(
                                    subtract(
                                        daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                                        daysUntil($pr_date_procurement_signed_off_order)
                                    )
                                ),
                                113,
                                140
                            ),
                            84,
                            ifElse(
                                isWithinRange(
                                    abs(
                                        subtract(
                                            daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                                            daysUntil($pr_date_procurement_signed_off_order)
                                        )
                                    ),
                                    141,
                                    168
                                ),
                                98,
                                112
                            )
                        )
                    )
                )
            )
        )
    ),
    "Refund Possible",
    "Refund Unlikely"
)
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

0 Adam Ososki 1 month ago in reply to Adam Ososki

Seems to be solved - I stripped every space out of the formula and that fixed it - seems you have to be very careful where you copy and paste the formula from. I was using ChatGPT to help sanitise the code - and if you click the copy icon you get formatted text that kills the system, but if you highlight the text and paste it, it's just plain text with no spaces...
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Reject Answer

Cancel