Query properties of inlinked files

Help
1

What I’m trying to do

I’m trying to filter my query results by the properties of inlinked files.

My setup

In my setup I have both project and action files. Action files are linked to their respective project files via inline fields. Each project and action file also has it’s own status defined via an inline field.

Sample project file metadata

type:: #project 
project-status:: #ps/current

Sample action file metadata

type:: #action 
project:: [[P-Project name]]
action-status:: #as/now
people::

What I would like in the end is a list of projects who have no actions with the status #as/now. So in other terms projects that don’t have a next action defined.

Things I have tried

I have a query that looks for all the actions that have the status #as/now in all the projects of various statuses, so thought that this would be a good starting point:

TABLE WITHOUT ID file.link, project
FROM #as/now 
WHERE contains(project.project-status, "#ps/current") OR contains(project.project-status, "#ps/urgent") OR contains(project.project-status, "#ps/perpetual")
SORT choice(project.project-status = "#ps/urgent", 1, choice(project.project-status = "#ps/perpetual", 2, choice(project.project-status = "#ps/current", 3, 99 ))), project, file.link

and from this I got:

TABLE WITHOUT ID file.link
FROM #ps/current
WHERE file.inlinks.action-status != "#as/now"

but upon testing the results aren’t 100% what I’d expect so I refined it as follows:

TABLE WITHOUT ID file.link
FROM #ps/current
WHERE file.inlinks.type = "#action" AND file.inlinks.action-status != "#as/now" 
OR !file.inlinks

but the results are still off … I feel that I’m right on the tip of it but can’t seem to place the mistake

2

I tried the FILTER function for the first time and it seems a bit more promising:

TABLE WITHOUT ID file.link
FROM #ps/current
WHERE filter(file.inlinks, (s) => !contains(s.action-status, "#as/now"))

but what seems to be happening is that if a project has multiple actions linked and just one of the linked tasks have a status other than #as/now then the project gets listed

3

Here’s another attempt where I looked at using length() - again I got closer but still no cigar:

TABLE WITHOUT ID file.link
FROM #ps/current
WHERE !file.inlinks
OR length(file.inlinks.length) = 1 AND file.inlinks[0].action-status != "#as/now"
OR length(file.inlinks.length) > 1 AND %%don't know what to add here%%
4

I solved it with none()

TABLE WITHOUT ID file.link
FROM #ps/current
WHERE none(file.inlinks, (a) => a.action-status = "#as/now")
2 Likes
5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.