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

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

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

Children
  • 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