Row Filter reference two tables

[SKETCHwade]

Hello,

I have two tables. Jobs and Tasks.

Each Jobs can have many Tasks.

Here is the slice for the Jobs table I currently have:

ISBLANK([Finish Date])

IN(FILTER ("Tasks",
AND(
[Completed?]=TRUE,
OR([Completed?2]=TRUE, ISBLANK([Completed?2])))),
Jobs[Related Tasks])

I want to merge these two together. Finish date is a column in the Jobs table and the rest of the expression is within the Tasks table so I cannot do the check within that section.

I tried wrapping it all within an IF but that did not work because a row filter is looking for a yes no for each row. I also tried wrapping it in an AND but no dice either.

[dbaum]

A slice's row filter expression is evaluated in the context of each of the table's rows. Your FILTER expression isn't written from the context of a single Jobs row. Its reference to a Jobs table column is to the table's entire column across rows--i.e., not that column's value within each row. Maybe what you're seeking is one of the following.

AND(
ISBLANK([Finish Date]),

IN(FILTER ("Tasks",
AND(
[Completed?]=TRUE,
OR([Completed?2]=TRUE, ISBLANK([Completed?2])))),
[Related Tasks])
)

AND(
ISBLANK([Finish Date]), 

ISNOTBLANK(INTERSECT(
[Related Tasks], 
FILTER ("Tasks",
AND(
[Completed?]=TRUE,
OR([Completed?2]=TRUE, ISBLANK([Completed?2])))),
))
)

[SKETCHwade]

Thank You @dbaum  for assisting me.

I have tried both formulas. The first does not pickup any results.
This seems strange because it makes sense to me when I read it.

The second only picks up two results. For reference it should result in about 20.
I had not heard of intersect before so thats a good trick. Logic makes sense to me here too but somethings not right.

I am lost in terms of where to go from here as I do not see any issues. I do not know any way to fault find within an expression either.

[SKETCHwade]

Also another issue I am seeing is that 1 Job might have 2 tasks. 1 task complete and the 2nd is not. These formulas are accepting a single task as complete or blank as a pass where as it needs to be all tasks on that Job.

[dbaum]

Sounds like a mismatch between your actual data or table/column design and your expressions posted here.

FWIW, another technique is to compare counts of the total list to the list with excluded values removed. Here's a rough example:

AND(
  ISBLANK([Finish Date]), 
  ISNOTBLANK([Related Tasks]), 
  COUNT([Related Tasks]) = COUNT([Related Tasks][Completed?] - LIST(false)), 
  COUNT([Related Tasks]) = COUNT([Related Tasks][Completed?] - LIST(false) - LIST("")
)

edit: never mind. That won't work in this situation since subtracting from a list will also remove other duplicate values.

[SKETCHwade]

Ok, Thanks @dbaum for trying!

[SKETCHwade]

I am now thinking of making a virtual column in the Jobs table that will tell me if all of the tasks with that Job ID are complete.

I have started with getting all Tasks related to this Job:

FILTER("Tasks",
[Job ID] = [_THISROW].[Job ID]
)

 But I am unsure how to then filter that list of Key columns.
I have created a helper VC in Tasks called TaskReady that makes sure both tasks are completed.

So I need to get the above expression and make sure each Key has TaskReady as TRUE if any False then blank. Just not sure how to write it. Every way I think of which is within this filter would only be checking each Task individually.

[Suvrutt_Gurjar]

Could you update what are the conditions in the "Tasks" table to indicate that all the tasks complete?

[SKETCHwade]

This is the VC in the Tasks table

AND(
[Completed?]=TRUE,
OR(
[Completed?2]=TRUE,
ISBLANK([Allocated To2])
))

[SKETCHwade]

I do realise I could incorporate this so I dont have a VC but considering im struggling so much I figure it simplifies the expression for now.

[Suvrutt_Gurjar]

Thank you. You may wish to try a slice filter expression in the Jobs table of 

AND(ISBLANK([Finish Date]),

          COUNT([Related Tasks])= COUNT(SELECT( [Related Tasks][Task ID],                         AND( [Completed?]=TRUE, OR( [Completed?2]=TRUE, ISBLANK([Allocated To2])))))

        )
                                 

Please change the [Task ID] with the key column name of the Tasks Table                                                                                                      

[SKETCHwade]

Hey @Suvrutt_Gurjar Thank you!

This expression actually works the same way every other one I have tried does. Its checking the Tasks individually but I need it to check all of the Jobs Tasks and if they are all complete then its true if any are not complete then it should not show in this slice filter.

Does that make sense?

Testing this out I will have to have min on the count of 1 haha any job with no tasks at all are all getting displayed. Great idea though!

[Suvrutt_Gurjar · Accepted solution]

---

@SKETCHwade wrote:

if they are all complete then its true if any are not complete then it should not show in this slice filter.

---

Is it correct understanding when you mention all tasks for all jobs, you mean all related tasks of a job are complete, then that job should should show or not show in the filter.

---

@Suvrutt_Gurjar wrote:

COUNT([Related Tasks])= COUNT(SELECT( [Related Tasks][Task ID],                         AND( [Completed?]=TRUE, OR( [Completed?2]=TRUE, ISBLANK([Allocated To2])))))

---

This part should actually check if all the related tasks for a job are complete as per three conditions you gave. COUNT([Related Tasks]) gives count of all the related tasks and COUNT(SELECT( [Related Tasks][Task ID], AND( [Completed?]=TRUE, OR( [Completed?2]=TRUE, ISBLANK([Allocated To2]))))) filters in the completed tasks as per the given conditions. If both LHS and RHS of the expression are equal , it means all related tasks are complete.

Could you update what error or incorrect records , if at all ,you are getting with this expression>

---

@SKETCHwade wrote:

Testing this out I will have to have min on the count of 1 haha any job with no tasks at all are all getting displayed. Great idea though!

---

Oh yes, you can eliminate such rows with no related tasks by adding a condition as below

AND(ISBLANK([Finish Date]),

              ISNOTBLANK([Related Tasks]), 

          COUNT([Related Tasks])= COUNT(SELECT( [Related Tasks][Task ID],                         AND( [Completed?]=TRUE, OR( [Completed?2]=TRUE, ISBLANK([Allocated To2])))))

        )

[SKETCHwade]

You're right, I meant all tasks for this job not all jobs.

I didn't necessarily see errors, I was just seeing a huge list of jobs and the couple I went through had no tasks so had to sort that out first.

Thank you for your update I have incorporated it. It's now displaying no jobs but I'll have to incorporate some dummy data to see if it's working or not. This app is live so it is possible there is nothing matching the criteria.

I'll test it further in the morning. Thank you so much @Suvrutt_Gurjar you've been a life saver so many times! I hope google / appsheet look after you for keeping so many people moving on the platform!

[Suvrutt_Gurjar]

Thank you very much for your update and appreciation. 

A little background if I may.  🙂 Almost all of  us in this community volunteer ( except those with Google logo /staff tag in their profile avatar). Of course many of us (including I) also offer professional services of app development on the AppSheet platform. However in the community itself, the contribution is purely voluntary.  

When I started with AppSheet in 2016, initially I posted queries and others helped. So I ( I am sure many others) consider it as 'giving back" in whatever small way I (we) can. The community also helps me ( and all) in continuous learning because more the issues one works on/ reads about, the more one learns.