Nested If/Then Quantry

thecomputermedic

I am working on find tasks that are of the Action plan or Key task type that have not been completed yet but have a due or publish date in the past; or have a due or publish date within the next 90 days regardless of status.

Put another way:

I have been building and testing but git stuck at:

Jason Albrecht

Hi @thecomputermedic

Would this work?

=IF(AND([Primary Lead]@row = [Modified By]@row, OR(Type@row = "Action Plan", Type@row = "Key Task"), OR(NETDAYS(TODAY(), [Publish Date]@row) <= 90, NETDAYS(TODAY(), [Due Date]@row) <= 90), OR(Status@row = "Not complete", AND(NETDAYS([Publish Date]@row, TODAY()) > 0, NETDAYS([Due Date]@row, TODAY()) > 0))), "True", "False")

thecomputermedic

@Jason Albrecht Thank you but I am still getting the "UNPARSEABLE" error. I tried to troubleshoot and I changed the status from being "Not Complete" to NOT(status="Complete") to no avail:

=IF(AND([Primary Lead]@row = [Modified By]@row, OR(Type@row = "Action Plan", Type@row = "Key Task"), OR(NETDAYS(TODAY(), [Publish Date]@row) <= 90, NETDAYS(TODAY(), [Due Date]@row) <= 90), OR(NOT(Status@row = "Complete"), AND(NETDAYS([Publish Date]@row, TODAY()) > 0, NETDAYS([Due Date]@row, TODAY()) > 0))), "True", "False")

Jason Albrecht

Hi @thecomputermedic

#UNPARSEABLE happens for many reasons, such as misspelling, wrong case for a column name, etc.

I would break the formula down into each component and check that it works.

e.g. does [Primary Lead]@row = [Modified By]@row work? If not, I'd fix it, try the full formula, and if still getting an error, move on to the next section (e.g. does Type@row = "Action Plan" work, etc.)

As we don't have a screenshot of your column names to check, it could be that some of these should be in uppercase.

thecomputermedic

Found the issue was with the due date data entered by the user was missing. I added an iferror to capture that.

The only part that is not working is the last criteria for "[Primary Lead] = "Current User" (and also [Secondary Lead] = "Current User")

I see that the first part compares the Primary Lead to the last user who Modified the row "[Primary Lead]@row = [Modified By]@row" is there a way to have this filter apply to the "Current User" in either the [Primary Lead] or the [Secondary Lead] column?

I tried using a filter in grid view with the settings of "Show rows that match All conditions" it fails as the Primary will not be the Secondary.

"Primary Lead' "Is One Of" "Current User"

"Secondary Lead" "Has Any of" "Current User"

"Active" "Is One Of" "Yes, Hold"

The "Active" column =IFERROR(IF(AND([Primary Lead]@row = [Modified By]@row, OR(Type@row = "Action Plan", Type@row = "Key Task"), OR(NETDAYS(TODAY(), [Publish Date]@row) <= 90, NETDAYS(TODAY(), [Due Date]@row) <= 90), OR(NOT(Status@row = "Complete"), AND(NETDAYS([Publish Date]@row, TODAY()) > 0, NETDAYS([Due Date]@row, TODAY()) > 0))), "Yes", "No"), "Hold")

Thank you for helping.

Jason Albrecht

Hi @thecomputermedic

I'm glad we've got this far and that you found the issue with the due date data missing.

You'll need to help me understand what you mean by "Current User" though, so that the community can help out.

In reference to this article on column types, the only system column suitable that I can see is the [Modified By], where, once the person who is currently accessing the sheet saves the sheet, their details will be shown in the column.

Thanks in advance for your patience with me as I seek some understanding. Talk to us a bit more about "Current User" and provide some screenshots of the grid view filter to which you referred. Is this a separate column to Primary Lead and Secondary Lead? Could you create a report of the sheet automatically filtered for each lead and shared with each lead, either directly or via a dashboard? What happens if two Primary Leads are accessing the sheet at the same time? Looking forward to helping further, once I understand the context a bit better.

thecomputermedic

So here are the fields we have been playing with. I realize that Smartsheet does not support "Current User" natively in a formula, only in a Report.

The report I tried had the settings of "Show rows that match All conditions":

"Primary Lead' "Is One Of" "Current User"

"Secondary Lead" "Has Any of" "Current User"

"Active" "Is One Of" "Yes, Hold"

However, to use a report I have to use only one boolean type at a time (all "OR" or all "AND") so that will make it hard to do as I need (Primary OR Secondary) AND Active

thecomputermedic

To close out this conversation, here is the formula we are using:

=IF(Status@row = "Canceled", "No", IF(ISBLANK([Due Date]@row), IF(AND(Status@row = "Complete", ISBLANK([Publish Date]@row)), "No", IF(AND(Status@row <> "Complete", ISBLANK([Publish Date]@row)), "Yes", IF(OR(Type@row = "Action Plan", Type@row = "Key Task"), IF(NETDAYS([Publish Date]@row, TODAY()) > 0, IF(Status@row = "Complete", "No", "Yes"), IF(NETDAYS(TODAY(), [Publish Date]@row) <= 90, "Yes", "No")), "No"))), IF(ISBLANK([Publish Date]@row), IF(OR(Type@row = "Action Plan", Type@row = "Key Task"), IF(NETDAYS([Due Date]@row, TODAY()) > 0, IF(Status@row = "Complete", "No", "Yes"), IF(NETDAYS(TODAY(), [Due Date]@row) <= 90, "Yes", "No")), "No"), IF(OR(Type@row = "Action Plan", Type@row = "Key Task"), IF(OR(NETDAYS([Publish Date]@row, TODAY()) > 0, NETDAYS([Due Date]@row, TODAY()) > 0), IF(Status@row = "Complete", "No", "Yes"), IF(OR(NETDAYS(TODAY(), [Publish Date]@row) <= 90, NETDAYS(TODAY(), [Due Date]@row) <= 90), "Yes", "No")), "Hold"))))

this gives only items that are Action Plan or Key Task and not cancelled; then, if past the dates and not marked complete; or within the next 90 days

Jason Albrecht

Thank you @thecomputermedic

Congratulations on completing a mammoth formula.

I'm curious about your quandary regarding Current users.

How did you go about solving that in the formula, or does the formula provide the data and the report filters for the appropriate user?

brettmil

I used the formula in a new column in a report I chose the fields that had a Yes in the new column and that is where I was able to set current user. I also have a filter in the sheet that is current user AND Yes.