Prefilters on a detail page with the need for an ID from the parent page

Question

Good day,On a screen full of data we have a screen for assigning detail data to the said parent data. The parent  data is assigned to projects. And so is the detail data. The detail data can also be unassigned (so not assigned to a project).I was trying to make a prefilter so only the project with unassigned detail data and the detail data assigned to the current project the user works with is shown. This proved to be difficult. As the detail data does not know about the parent data’s unique ID and project ID. And when I give the data via the reference, only the detail data assigned to the project shows up. This is not the wished outcome.How can I make it so that the detail data assigned to a project is shown only with the project it's assigned with, and when the detail data is unassigned to a project, it is also shown (unasigned detail data needs to be shown to all projects)?So it comes down to the following: I want to show the detail data to a specific project if the detail data is assigned to it, or when the detail data is completely unassigned. And giving the strict relation to to the parent data removes the option of detail data being unassigned, that is not the wished outcome. How could this be achieved?

Erwin Ekkel · Accepted Answer

Easiest way I can think of achieving this is by creating a view of the detail. Then add a join column in the view. In the view it self add a join on the main table and in case the reference column is empty then just self join.

select row_number()over(order by a.id,a.main_id) as row_id
,a.id
,a.main_id
,b.main_id as join_id
from detail as a
join main as b
on b.main_id = (case when a.main_id is null then b.main_id else a.main_id end)

The b.main_id column will be used for the reference join. So when you have 3 main rows (main_id: 2,3 and 4) but only 1 detail id the view results would look like this:

row_id	id	main_id	join_id
1	22	NULL	2
2	22	NULL	3
3	22	NULL	4
4	22	2	2

Sign up

Login to the Thinkwise Software Community

Scanning file for viruses.

This file cannot be downloaded