Question

Hey all,

I'm trying to build these instructions:

Cancelled if prop("Cancelled?") is True
Unpaid if prop("Payment Total - Original") is empty
Paid if prop("Payment Total") = prop("Total Order Value")
Partially Refunded if prop("Payment Total - Original") is greater than 0, less than prop("Total Order Value") and has “-”
Partially Paid if prop("Payment Total - Original") is greater than 0, less than prop("Total Order Value") and has no “-”
Fully Refunded if Sum of prop("Payment Total - Original") - prop("Payment Total") = prop("Payment Total")
Overpaid if prop("Payment Total") is greater than prop("Total Order Value")

ifs(prop("Cancelled?") == true, "Cancelled"
    ,
    prop("Payment Total - Original").empty(), "Unpaid"
    ,
    prop("Payment Total") == prop("Total Order Value"), "Paid"
    ,
    (prop("Payment Total - Original").format().toNumber() < prop("Total Order Value")) , if(prop("Payment Total - Original").contains("-"), "ERROR"
    , 
    "Partially Paid")
    )

This formula is what I have so far, but the last statement has me confused. The line that says "ERROR" tends to go blank. I would have intended for it to be the "Partially Refunded" statement, but only "Partially Paid" comes out when that statement comes out correctly.

I'm not sure why nothing seems to show up. This would have been a logical false, and both terms look to come out correctly. This got me stuck unable to do the other statements that I had, also because they had three requirements, and the formula I worked on didn't accept three statements using the and() function. It wouldn't even accept two, which is why I needed to put down another if statement on the last line.

Trying to make a "does not contain" function also added to this complexity. This brought me back to a true or false statement and the issue at hand.

Any ideas on how to fix this? I've trialed some other formula lines but at this point, my brain has gone foggy.

Comments

I ended up searching some AI models and got this new formula:

ifs(
prop("Cancelled?") == true,
"Cancelled",
prop("Payment Total - Original").empty(),
"Unpaid",
prop("Payment Total") == prop("Total Order Value"),
"Paid",
(prop("Payment Total - Original").format().toNumber() == 0) or (prop("Payment Total - Original").format().toNumber() > 0 and prop("Payment Total - Original").contains("-")),
"Fully Refunded",
(prop("Payment Total - Original").format().toNumber() > 0) and not prop("Payment Total - Original").contains("-"),
"Partially Paid",
(prop("Payment Total - Original").format().toNumber() > 0) and prop("Payment Total - Original").contains("-"),
"Partially Refunded"
)

My problem now is that a line that was supposed to be tagged as Partially Refunded is now showing as refunded. I don't think I can explain properly at the moment, but please check the link.

Answer 1

Hey Alven,

this one was a pickle. It took a while to figure out. There was a problem with the calculation of the "Payment Total - Original" because when this property contained more than one data entry in the array your formula was not allowing the rest of the tests to proceed.

Here is a formula I created that works.

Lets( 
     IsInRange, 
    prop("Payment Total - Original").sum().format().toNumber() > 0 and prop("Payment Total - Original").sum().format().toNumber() < prop("Total Order Value"),
     HasMinusSign,
    prop("Payment Total - Original").sum().format().toNumber()<prop("Payment Total - Original").format().toNumber(),

ifs(
    prop("Cancelled?") == true, 
    "Cancelled",
    prop("Payment Total - Original").empty(), 
    "Unpaid",
    prop("Payment Total") == prop("Total Order Value"), 
    "Paid",
    IsInRange and HasMinusSign, 
    "Partially Refunded",
    IsInRange and not HasMinusSign, 
    "Partially Paid",
    sum(prop("Payment Total - Original"), -prop("Payment Total")) == prop("Payment Total"), 
    "Fully Refunded",
    prop("Payment Total") > prop("Total Order Value"),
    "Overpaid"
)
)