Jump to content
Sign in to follow this  
stefangs

Dynamically updating a relationship for filtering

Recommended Posts

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 by stefangs

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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 by comment

Share this post


Link to post
Share on other sites

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 😉

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.