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

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

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

Children
No Data