Re: If all Sub Parts are Complete, List Complete o...

Jason_Reynolds · 07-11-2021 03:34 PM

Hi, I am trying to figure a way to do a text based sum up. I am trying to keep track of the parts I 3D Print for each order. One order can have many parts. Each Part has a enum Status. When all the Related Parts Status is Complete, I want a virtual column to return Complete on the Parent Order.

I have [Related Parts][Status] returning all of the related statusss correctly, but I am not sure how to do the rest. I am sure I am overthinking it. IF(AND()) dont seem to be working (I get a “Cannot compare List with Text” error), and I have also looked into Array formulas with no success. Any guidance would be greatly appreciated.

IF(AND([Related Parts][Status]= “Complete”, “Complete”, “Processing”))

Jason_Reynolds

I think I figured it out!

IF(
OR(
CONTAINS([Related Parts][Status], Print Queue),
CONTAINS([Related Parts][Status], Processing),
CONTAINS([Related Parts][Status], Printing),
CONTAINS([Related Parts][Status], Error),
CONTAINS([Related Parts][Status], Waiting on Customer)
),
“Processing”, “Completed”)

Steve

Try instead:

IF(IN(“Complete”, [Related Parts][Status]), “Complete”, “Processing”)

More efficient:

ISNOTBLANK(
  INTERSECT(
    [Related Parts][Status],
    LIST(
      "Error",
      "Print Queue",
      "Printing",
      "Processing",
      "Waiting on Customer"
    )
  )
)

Marc_Dillon

what about:

ISBLANK( [Related Parts][Status] - LIST( "Complete") )

…to check if “Complete” is the only status?

Or, in total:

IF(
  ISBLANK( [Related Parts][Status] - LIST( "Complete") ) ,
  "Complete" ,
  "Processing"
)

Steve

Good one!

MultiTech

This is what I’d do

This is an interesting use of list-math

Jason_Reynolds

Thank you all for your suggestions! One heck of a community!

I will try some of these out and report back. As of right now, it is working, but continuous improvement is a virtue!

Jason_Reynolds

Thank you all again. Went and revisited this and used @Marc_Dillon 's suggestion and added an extra step. Now if everything is in “Print Queue” status, then it is “Not Started” If, Everything is in “Complete” status, Complete. Everything else is Processing!

Works like a charm!

IF(
ISBLANK( [Related Parts][Status] - LIST( “Print Queue”) ) ,
“Not Started” ,
IF(
ISBLANK( [Related Parts][Status] - LIST( “Complete”) ) ,
“Complete” ,
“Processing”
)
)

If all Sub Parts are Complete, List Complete on Parent Object