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

    ChatGPT suggested it found the issue - but it's corrected script below didn't make any difference to the error message at all.

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

  • 0 in reply to Adam Ososki

    I also tried directly nesting the If statements: (also errored with same message)

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

  • 0 in reply to Adam Ososki

    I have replace If with Ifelse statements - but still getting the same error.

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

  • 0 Sugar Employee Badge in reply to Adam Ososki

    Hey Adam,

    In the above formula, there are several places where you are using ">" or "<=" to make comparisons between numbers. SugarLogic currently is not able to parse that type of comparison syntax. Instead, you will need to make some changes to use built-in functions like "greaterThan" and "lessThan".

    To give a example of one part, here is a before/after of potential changes you could make:

    // Before
ifElse(
    abs(
        subtract(
            daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
            daysUntil($pr_date_procurement_signed_off_order)
        )   
    ) <= 168,
    98,
    112
)
    // After
ifElse(
    lessThan(
        abs(
            subtract(
                daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
                daysUntil($pr_date_procurement_signed_off_order)
            )
        ),
        169
    ),
    98,
    112
)

    I would suggest starting with replacing all of the ">" and "<=" in this fashion, and trying again - if it still does not work, we can go from there

  • 0 in reply to Eric Wahlquist

    That's amazing - thank you - I will start this now!!!

  • 0 in reply to Eric Wahlquist

    Weirdly it does allow greaterThan - but there's no lessThan and nothing that equates to lessThanorequal (which I worked around by +1 on those numbers)

    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(
    lessThan(
    abs(
    subtract(
    daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
    daysUntil($pr_date_procurement_signed_off_order)
    )
    ),
    29
    ),
    28,
    ifElse(
    lessThan(
    abs(
    subtract(
    daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
    daysUntil($pr_date_procurement_signed_off_order)
    )
    ),
    57
    ),
    42,
    ifElse(
    lessThan(
    abs(
    subtract(
    daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
    daysUntil($pr_date_procurement_signed_off_order)
    )
    ),
    85
    ),
    56,
    ifElse(
    lessThan(
    abs(
    subtract(
    daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
    daysUntil($pr_date_procurement_signed_off_order)
    )
    ),
    113
    ),
    70,
    ifElse(
    lessThan(
    abs(
    subtract(
    daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
    daysUntil($pr_date_procurement_signed_off_order)
    )
    ),
    141
    ),
    84,
    ifElse(
    lessThan(
    abs(
    subtract(
    daysUntil(related($pr_selected_vehicle, "pr_anticipated_delivery_date_first_update")),
    daysUntil($pr_date_procurement_signed_off_order)
    )
    ),
    169
    ),
    98,
    112
    )
    )
    )
    )
    )
    )
    ),
    "Refund Possible",
    "Refund Unlikely"
    )

  • 0

    This is NOT solved lol - I ended up reading every formula option 1 by one until i came up with this isWithinRange 

    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

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