Details about this thread
  • State Suggested Answer
  • +1 person also asked this people also asked this
  • Replies 9 replies
  • Answers 1 answer
  • Subscribers 305 subscribers
  • Views 470 views
  • Users 0 members are here
Related content from around SugarClub

Help getting a calculated field to work please?

Adam Ososki

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

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

  • 0 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...

Reply Children
No Data