Help

How to use IF statements with logical operators for inventory

Topic Labels: Formulas
Solved
Jump to Solution
950 1
cancel
Showing results for 
Search instead for 
Did you mean: 
J_E
4 - Data Explorer
4 - Data Explorer

Hello,

First challenge…I am trying to show when a number is input to the RECEIVED column, that it subtracts itself from the ORDERED column and puts the result in the BACKORDERED column (however if the RECEIVED column is blank, I need the BACKORDERED column to be ‘0’ or blank as well).

Second challenge…If the RECEIVED number equals the ORDERED column then the STATUS column should show ‘complete’. If the RECEIVED number is less than the ORDERED column, the STATUS column should show ‘in progress’.

If I was to say this in one sentence it would be “If received is not 0 or blank, subtract it from ordered and show result in backordered AND if received equals ordered change status to complete OR if received is less than ordered change status to in progress”

Any help would be much appreciated! :grinning:

Screen Shot 2021-08-13 at 11.59.35 AM

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @J_E! :grinning_face_with_big_eyes: First off, formulas cannot change the contents of other fields. A formula only creates output in its own field. You could turn {Status} into another formula, but there’s no way of coloring its contents aside from using colored emojis.

In that light, the formula for {Backordered} could be this:

IF(Received & "", Ordered - Received)

And a formula for {Status} could be this:

IF(Received & "", IF(Received = Ordered, "✅ Complete", "🟡 In Progress"))

Screen Shot 2021-08-13 at 10.22.58 AM

See Solution in Thread

1 Reply 1
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @J_E! :grinning_face_with_big_eyes: First off, formulas cannot change the contents of other fields. A formula only creates output in its own field. You could turn {Status} into another formula, but there’s no way of coloring its contents aside from using colored emojis.

In that light, the formula for {Backordered} could be this:

IF(Received & "", Ordered - Received)

And a formula for {Status} could be this:

IF(Received & "", IF(Received = Ordered, "✅ Complete", "🟡 In Progress"))

Screen Shot 2021-08-13 at 10.22.58 AM