Skip to main content

Hi,

i am trying to make an overview of articles that are made up of other articles.

I am showing different levels of the article when searched for an articlenumber.

This all works.

But when i search for an article, i get these results:

 

I would like, to only see 1 article when the article number is the same. i tried this with doing a distinct, but this is not possible in the prefilter as far as i know.

Is there a way to do a distinct in a prefilter? or is there another way i can get this working.

 

Here also is the code that i use in my prefilters, we got 8 levels, all 8 levels got their own prefilter.

t1.article_number_2 = (  SELECT TOP 1 pt.searched_article_number  FROM prefilter_table pt  WHERE pt.usr_id = dbo.tsf_user()  ORDER BY pt.search_id DESC)OR t1.article_number_3 = (  SELECT TOP 1 pt.searched_article_number  FROM prefilter_table pt  WHERE pt.usr_id = dbo.tsf_user()  ORDER BY pt.search_id DESC)OR t1.article_number_4 = (  SELECT TOP 1 pt.searched_article_number  FROM prefilter_table pt  WHERE pt.usr_id = dbo.tsf_user()  ORDER BY pt.search_id DESC)

 

Thx!

Dylan

Hi Dylan,

I'm having difficulty the chosen solution. As in your case articles can be made up for other articles, that indicates it is a Tree structure. There must be a way to determine the correct order by calculating which article has no parent articles and all articles the article is made of.

With this, you should be able to resolve this overview into one screen, by creating a View that uses a WITH statement to create the tree structure, for example:

;with article_tree as 
(
select
    a.article_id,
    a.article_name,
    null as parent_article_id
from article a
where not exists (select 1
                  from article_made_of t2
                  where t2.parent_article_id = a.article_id)

union all

select 
    a.article_id,
    a.article_name,
    t.article_id as parent_article_id
from article_tree t
join article_made_of t2
  on t2.parent_article_id = a.article_id
join article a
  on a.article_id = t2.article_id
)
select 
    a.article_id,
    a.article_name,
    a.parent_article_id
from article_tree a

Could you elaborate on the chosen solution to why 8 levels of screens are created?

Terms & ConditionsCookie settings