[SOLVED] Lookup only certain linked items or only link items if they meet certain criteria

Ellen_Holloway · ‎Oct 11, 2018

Hello,

I have a base with two tables:

Projects: Lists current projects and the tasks assigned to them (linked)
Tasks: Lists all tasks with what project they are a part of (linked), their status and other notes and assignments on the tasks.

I would really like to have only the tasks that still need to be completed pull into the projects table, but when I use the link field type, it pulls all of the tasks of that project, when I use lookup, I can only pull in the various statuses of the tasks, I really just want to view the linked tasks that still need to be completed.

I figure something like the FIND function could help here, but I can’t find a description on how that function works, and when I tried a few things, I just got blank results.

Any suggestions would be appreciated!

Julian_Kirkness · ‎Oct 11, 2018

At the moment, the solutions available for this aren’t perfect - but what I generally is this:

In the tasks table, set up a formula field called something like Incomplete Task Name with a formula:

IF(Complete,BLANK(),Name) (this assume Complete is a Checkbox)

You can then use a Rollup field on Projects to show just the Incomplete tasks:

Ellen_Holloway · ‎Oct 11, 2018

Cool that gives me a list, but is there any way to do this and the records still be linked so that I could click on them and see who it is assigned to and what notes they have on it or attachements they’ve done?

drassi · ‎Oct 11, 2018

Ellen, there is a long-awaited feature in Beta that I believe will do what you’re looking for, check it out:

“Limit linked record selection to a view”

Ellen_Holloway · ‎Oct 11, 2018

Thank you so much! I will try that, for now the list I can create from @Julian_Kirkness will help.

Julian_Kirkness · ‎Oct 11, 2018

Unfortunately there isn’t a way to do that (have the reduced list clickable)…

What can make life easier, is to use expanded view which will show those tasks in a list one under the other - and if you order the fields in Tasks so that Complete is in the first few, then you will be able to see clearly.

As @drassi says there is a beta you can apply for which may help a LOT in this area - I’ve applied but don’t have it yet - what we don’t know at the moment is whether it will just stop you selecting things or whether you’ll be able to also stop them showing when a criteria is met. In most databases it would just stop you being able to select it which wouldn’t help here.

If you have a Pro account you can apply for Beta features here.