Slice filter ignores rows that it should include

[SKETCHwade]

Hi everyone,

I have a formula for a slice below.
This formula is checking in the related table 'Tasks' .
It looks in column 'allocated to' which can be thought of as what category of work is required, for the word "Welding" (There are many slices searching for different terms) after that, it checks to see if "Welding" has been marked as completed and also wether the job has already been finished

Some tasks require a job to be completed first before they can then be done. This is handled by the "Allocated To2". For example welding cannot be completed before disassembly.

Overall I just want to see rows that require "Welding" yet to be completed in jobs that are still in progress. But only show "Welding" where it is ready to be completed. ("Allocated To" is the first job to completed then "Allocated To2" is the second job)

My issue is that using this formula, if any "Allocated To" is marked as completed the app ignores all other "Allocated To" and will only capture this example if "Welding" is in "Allocated To2"

How can I make the row filter only make these checks on one row at a time?

OR(
AND(
IN("Welding", [Related Tasks][Allocated To]),
NOT(IN(TRUE, [Related Tasks][Completed?])),
NOT(CONTAINS([Job Status],"Finished"))
),

AND(
IN("Welding", [Related Tasks][Allocated To2]),
NOT(IN(TRUE, [Related Tasks][Completed?2])),
IN(TRUE, [Related Tasks][Completed?])
)) 

[TeeSee1 · Accepted solution]

If I understand you correctly, try below. (it could be cleaned up a bit...)

OR(
 AND(
  [Job Status] <> "Finished",
  ISNOTBLANK(
   FILTER(
    "Tasks",
    AND(
	 [JOB id] = [_THISROW].[JOB id],
     [Allocated To] = "Welding",
     NOT([Completed?])
    )
   )
  )
 ),
 AND(
 [Job Status] = "Finished",
  ISNOTBLANK(
   FILTER(
    "Tasks",
    AND(
	 [JOB id] = [_THISROW].[JOB id],
     [Allocated To2] = "Welding",
     NOT([Completed?2])
    )
   )
  )
 )
)

[SKETCHwade]

Hi @TeeSee1 

Thank you for your help!

I had to make some minor adjustments, for Allocated To2 it needed to ensure the first one was completed and also both sections needed to ensure the job was in progress.

OR(
 AND(
  ISBLANK([Finish Date]),
  ISNOTBLANK(
   FILTER(
    "Tasks",
    AND(
	 		[JOB id] = [_THISROW].[JOB id],
     [Allocated To] = "Welding",
     NOT([Completed?])
    )
   )
  )
 ),
 AND(
 	ISBLANK([Finish Date]),
  ISNOTBLANK(
   FILTER(
    "Tasks",
    AND(
	 		[JOB id] = [_THISROW].[JOB id],
     [Allocated To2] = "Welding",
     NOT([Completed?2]),
     [Completed?]=TRUE
    )
   )
  )
 )
)

[TeeSee1]

Glad it was helpful.

Not that it makes any difference but [Completed?]=TRUE can just be [Completed?] if you want to be a minimalist 😁.

[SKETCHwade]

Very interesting, thank you @TeeSee1 

I never would've guessed you could do that haha