One-to-many link

It seems this may not be a robust as I’d hoped.

  • I have set up project and task tables.
  • I would like many tasks to one project.
  • I went to tasks table, created link to projects with “allow linking to multiple records” disabled.

This works great for assigning a task to a project.

If I now go to the projects table I can still assign an already assigned task to a different project and this then carries back through to ‘pollute’ the task can only be assigned to one project constraint I set up.

Seems the many to one is not truly in the database but just in the selection phase of the app.

Worst case I could hide the link field in the Projects table and replace it with a lookup showing the same thing - this will force users to initate the link in the tasks table where the front end entry is enforcing the one-to-many while still allowing view of tasks.

Is this just a limitation or is there a way I can work around this?


You can work around it by using the “Limit record selection to a view” feature.

Create a view in the Tasks table called “Available Tasks”, and filter that view to show only tasks where the Project field is blank.

In your Projects field, tell the Tasks field to limit selection of Tasks to the “Available Tasks” view. Now, when initiating the link from the Projects table, only Tasks without a Project currently assigned will show up in the list, or a new Task can be created.

I agree though, that the failure to enforce one-to-many validation from the “one” side is a strange oversight.


Better work around than I had planned - nice one.

A shame that I can’t create a task from inside a project but at least your way I can assign a task without breaking the one-to-many.


Edit: I take that back - your suggestions still allows for adding a new task. Perfect!

1 Like