 Help between Notion users

It's free & easy

Receive points, vote and give the solution

1vote

# Managing If terms and does not contain formulas

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.

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.

1vote

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

Thanks Martin, it works! I figured I needed an error instruction as well so its a great

But I don't understand how the HasMinusSign let is detecting the negative values. So it is comparing itself to itself?

### Martin_SystemsHill commented Nov 15

Hey Alven,

what it does is that it is comparing a sum of all its elements to the first element. The issue all along was that the formula can see only the first element in the array that it is pulling if you do not sum it right..
This way if you sum it first it will calculate e.g. 18000 - 8000 and the result is going to be 10000 but if you just try to convert it to Number data type right away it is only going to see the first element giving you a result of 18000.