Dynamically updating a relationship for filtering

[stefangs]

I have a primary table 'Projects' connected to a child 'Tasks' table in a portal. In the child records, there is a check box labeled 'done' to mark off completed steps. In the Projects table, I also have a check box labeled 'Show to do'. When clicked, the idea is to update the portal with only those records that don't have the 'done' box checked. Unchecking would show all tasks again.

I have experimented with all kinds of calculated key fields, but the way I see it, I cannot generate the proper key on the child side without looking at the status of the parent side. At that point, however, I can't index the field any longer and so it doesn't work. Any ideas?

Thanks!

Edited November 20, 2018 by stefangs

[comment]

It would be easy to filter the relationship so that it only shows tasks that have a value in the Done field. But it is not possible to establish a relationship based on an empty field - so once you add the Done field as a match field, you won't be able to make it show all tasks.

You can either make the Done field toggle between two values (say 0 and 1) or move to portal filtering, which is much less restricted - while also slower and not suitable for a large set of related records. But how many tasks can a project have?

[stefangs]

A project would hardly ever have more than 50 steps, usually more like 30.

I was actually trying to use the 'done' indirectly for the relationship like this:

Projects table: If (filter_todo = 1; ID_project & "|" & 1; ID_project)

Tasks table: If (done = 1; ID_project; ID_project & "|" & 1)

The idea is that if the check box is unchecked, then the match fields would simply contain a duplicate of the primary key.

I will have a go with portal filtering next.

Thanks!

[comment]

I think you may be overcomplicating this.

If you had a calculation field in Tasks along the lines of =

If  ( Done = 1 ; 1 ; 0 )

then you could use a global field in the Projects table as its match field to show only completed tasks when the global contains 1, and all tasks when the global contains 1¶0 (i.e. a return-separated list of both values).

And the calculation field isn't really necessary, since you can make the Done field auto-enter 0 when unchecked. Otherwise I would have simplified its formula to =

GetAsBoolean ( Done )

But with < 50 tasks, I would prefer to solve this at layout level instead of modifying the schema.

 

 

Edited November 21, 2018 by comment

[stefangs]

Thanks again - you're completely correct. Overcomplicating things is actually an area I excel in!

I will try both of your suggestions, but I also think that a filtered portal will be a good thing, since I eventually want to also filter by a user field (who is assigned to the task) as well.

Didn't know about the GetAsBoolean function. I used to have a script attached to boolean check boxes like

SetField (checkbox, not checkbox)

Yeah, overcomplicating 😉

[comment]

1 hour ago, stefangs said:

SetField (checkbox, not checkbox)

That is a good method, actually. And if you use it, you don't need to make the field a checkbox. In fact, you could even remove it from the layout and use some conditionally formatted object/s instead.

[stefangs]

That sounds like what Matt Petrowsky might be up to in this video:

https://www.filemakermagazine.com/videos/creative-single-checkboxes

Mind you, I haven't watched the subscribers part of it.