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

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

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

Children
No Data