Hello, I have a slice, and need to limit the ...

[Joe_Seiler]

Hello, I have a slice, and need to limit the rows in it shown by a value in another table.

Table 1:

Job Assignments

Table 2:

Job Updates

A virtual column in Job Assignments links to a table called Job Updates by a ref, and a bunch of de-refs, which carries over a unique key

I need a slice which only shows rows from table 1 if a column in table 2 shows or doesn’t show a certain value.

Basically, in Job Updates, when [Job Complete?] is marked as “TRUE”, I need it to no longer show the row in the slice of job assignments

Thanks in advance for any help

[RezaRaoofi]

Sorry I am a bit confused; which table has the Ref column in it? Or in other words, can you clarify if for each Job Assignment row you could have more than one Job Updates, or it is the other way around?

[Joe_Seiler]

Sorry for the confusion… 1 job assignment row, 1+ job updates row(s)

[RezaRaoofi]

So, the [Job Complete?] column is in Job Updates table then? If yes, then something like this in the Slice condition should filter out completed jobs:

IN([JobID], SELECT( [RelatedJobUpdates][JobID], [Complete?]=false))

[Joe_Seiler]

Thanks Reza, This seems to work great…almost

I went with; IN([Job #], SELECT([Job Updates][Job #],ISBLANK([Job Complete?])))

Since the “Parent Record” exists in the table that the slice is drawn from (Job Assignments), I tested the outcome by changing the ISBLANK to ISNOTBLANK, and it worked great.

What I’m finding though is if I use the ISBLANK, or rather [ ] = FALSE, I am left with nothing in the slice, because not all Job Assignment (slice) records exist yet in the Job Updates table.

So basically, I’m limiting the slice to only show records that exist in Job Assignments by records that show complete / not complete in Job Updates, but mostly all Jobs in Job Assignments will not have a record in Job Updates until it’s complete (it’s the final step).

What I need, and I can’t really think of anything here, is to be able to;

Show all jobs from the Job Assignments table (limited to slice view), except jobs that are also in the Job Updates table that show complete.

[RezaRaoofi]

Ah sorry, I forgot about Job Assignment rows that don’t have Updates yet. I think this one should work:

NOT( IN([JobID], SELECT( [RelatedJobUpdates][JobID], [Complete?]=true)))

[Joe_Seiler]

Yes, that works perfect!

Thank you for your help!

I also limited it to user email.

For anyone that needs this is the future, here’s an example;

=AND(USEREMAIL() = [column holding user’s email], NOT(IN([unique identifier column tying the 1st table to the 2nd table i.e Job #], SELECT([2nd table name][unique identifier column tying the 1st table to the 2nd table i.e Job #], [column with argument i.e Job Complete?] = true))))

[RezaRaoofi]

You’re welcome Joe!

I think you can refine your data structure though. The [Job Complete?] column seems to belong to Job Assignment table; if your business logic allowed that user changes that on Job Assignment table, then your Slice’s condition would be much simpler, and you would just filter out [Complete]=false rows.

[Joe_Seiler]

I thought about that, but the reason I went with the [Job Complete?] column in the Job Updates table is because I do my if my back end billing, etc from the google sheet directly.

I was looking for a different view (different table to bill from)…not to mention, this same logic applies to a few different categories of jobs, each with the same setup…then all comes together in another table in the sheet that queries all jobs across all types of each category, showing a complete list of all completed jobs.

Then, a UX in the app is based on that sheet for the guys to see their upcoming payroll

[Joe_Seiler]

Perhaps too complex, but seems to work for people that “break the rule” and do direct back end office stuff directly from the google sheet, and not from the web GUI of the app