Row Filter reference table

[SKETCHwade]

Hi All,

I have this Row Filter Expression so far:

AND(
IN("Honing", [Related Tasks][Allocated To]), **Check related tasks for tasks that have Honing as an allocated task
NOT(IN(TRUE, [Related Tasks][Completed?])),  **Make sure Honing is not complete yet
NOT([Job Status]="Finished")                 **Make sure the overall job is not yet completed (dont want to show old jobs)
)

 I want to add another requirement to this but it seems very complicated to me.
Here is some background info you will need to understand first:
[Pending] is a column within [Related Tasks] where its "Valid If" will look up all tasks with the same Job ID and return a list of all [Allocated to].
This might look like:
Honing
Welding
Assembly
You would then select what job needs to be done before honing can be completed.

I want to find all [Related Tasks] where [Allocated to] = "Honing" and where [Pending] is either blank or where it is not blank the job it names eg "Welding" look for all [related tasks] with [Allocated to] = Welding and see if [Completed?] = True

Let me know if I explained that last paragraph poorly haha. Thank you for reading this.

@TeeSee1  You seem to understand this stuff very well! haha

[SkrOYC]

---

@SKETCHwade wrote:

I want to find all [Related Tasks] where [Allocated to] = "Honing" and where [Pending] is either blank or where it is not blank the job it names eg "Welding" look for all [related tasks] with [Allocated to] = Welding and see if [Completed?] = True

---

SELECT() - AppSheet Help
IF() - AppSheet Help
AND() - AppSheet Help

[SKETCHwade]

Hi @SkrOYC 

Thank you for trying to guide me but also not providing the answer I agree this is a great way for people to learn!

My issue was not explained very well. This paragraph described my issue but it wasnt clear on the cause of the issue:

The issue is combining both of these formulas while ensuring im referencing the correct columns in the correct row. Here is what I would expect both halves to look like.

I want to find all [Related Tasks] where [Allocated to] = "Honing" and where [Pending] is either blank or where it is not blank

IF(AND(IN("Honing",[Related Tasks][Allocated To]),ISBLANK([Related Tasks][Pending]))TRUE,FALSE)


the job it names eg "Welding" look for all [related tasks] with [Allocated to] = Welding and see if [Completed?] = True

IF(ISNOTBLANK([Related Tasks][Pending],AND([related tasks][allocated to] = “Welding”,[related tasks][completed?]=TRUE))

 But now I must combine them. And they need to be referencing the same data. eg. is the red and blue here looking at the same row? Because I need them to.

IF(AND(IN("Honing",[Related Tasks][Allocated To]),ISBLANK([Related Tasks][Pending]))TRUE,FALSE)

Then in the other half I want to look at the rest of the [related tasks] rows:

IF(ISNOTBLANK([Related Tasks][Pending],AND([related tasks][pending] = “Honing”,[related tasks][completed?]=TRUE))

How do I know if each section of these formulas are talking about the same row?

These formulas are far from complete but are enough to get my point across I hope.