Need to Get a Status Column to Change Based on Formula

Platform discussions
, ,
1

I understand that formulas won’t work with automations in stock modnay.com. Advanced Formula app, while would likely work, is just too expensive for us at this point. So, I’m hoping someone can help me come up with a creative way to do what I’m trying to do.

If you refer to the below image, in the formula column, it is resulting in YES if Move In Count and Move Out Count are different. When that happens, I need the PARENT item status to change to discrepancy. I created a subitem status for ‘Discrepancy’ thinking if I could get it straight within the subitems, it would be easy to update the parent. But maybe there is a way to just change the parent directly? Secondarily, if there is a discrepancy, and the parent status is changed to ‘Discrepancy’, but then the counts are changed so there is no longer a discrepancy, I’d like the status to change back to neutral.

I hope this is making sense… I’ve never used the word discrepancy so much in my life…

image
image1162×452 40.1 KB

2

Hey @jmifsud - does it have to be a Status column (ie: you are running automations on it) or could it just be Text with conditional coloring to make it have a red background?

My thinking to get around this, add the Move in Count and Move Out Count as summaries on the Parent item. Then have a formula that does the check on the sum of the subitems, and use conditional coloring:

image
image1061×640 36.3 KB

image
image
image742×248 14.7 KB

Hope this helps!
Mark

3

Hi Joseph,

There is no way to do what you want natively. So the answer would be to use the Advanced Formula Booster indeed.

The only workaround I can think of would be:

  • to change your formula at the sub-item level, so that it reads:
    IF({Move In Count}={Move Out Count},0,1)
  • to add a summary at the item level, you would get 0 if all items match.
  • then to use the Conditional coloring to display the item’s summary cell in a special color if not at 0.

I was not able to get the conditional coloring to work on the Summary cell. So I created a formula cell that copies the Summary cell :see_no_evil:, and the conditional formatting seems to work on that formula cell…

It doesn’t change the item’s Status field, but at least you have a visual cue.

Hope it helps