Post reply

Drill down Lookups

  • October 30, 2007 at 1:36 pm

    #92777

    I have to create something that I am calling Drill down lookups (because I can't think of a better term). I need to design something that will allow my users to drill down from one lookup to another in order to get more specific with each selection. The best way for me to describe this is with an example.

    My users will be presented with a dropdown list that contains the following values:

    Paper

    Cardboard

    Vinyl

    Foil

    Depending on which item the user selected they will be presented by another drop down list. For instance when the User clicks Paper he/she will be presented with another dropdown List that contains the following values: (If they had clicked Foil instead of Paper they would be presented with a different list of values).

    Sheeted 1

    Sheeted 2

    Sheeted 3

    Depending on which item the user selected they will be presented by another drop down list. For instance when User clicks Sheeted 1 he/she will be presented with another dropdown List that contains the following values:

    White

    Red

    Yellow

    Before I begin designing the tables to support this I thought that I would see if anyone has any good ideas on how to do this. Any help would be appreciated.

    Thanks

  • November 6, 2007 at 2:02 pm

    #748857

    When I designed the drilled down table, it contains level1 info, then level 2....

    In your case

    Table Material_DrillDown

    Material VARCHAR

    Item VARCHAR

    Table Item_DrillDown

    Material VARCHAR

    Item VARCHAR

    Item_Info1 VARCHAR

    Item_Info2 VARCHAR

    Material = Paper, Cardboard, Vinyl

    Item = for paper, it will be sheet1, sheet2, sheet3... for Cardboard, it can be something else.

    So the records in the table will look something liked this

    Paper Sheet1

    Paper Sheet2'

    Paper Sheet3

    Cardboard Sheet1

    Cardboard Sheet 2

    Item_DrillDown - the PK will be Material + Item (Paper + Sheet1)

    The records in this table will be

    Paper Sheet1 Red

    Paper Sheet1 Blue

    Paper Sheet1 Green

    Paper Sheet2 Red

    Paper Sheet2 purple

    my 2 cents

  • November 7, 2007 at 6:16 am

    #749062

    First I would like to thank you for getting back to me. I was about to give up on this post.

    While I like your approach I have a question. If my boss decides to that he wants to drill down further then your example would I have to add more fields to the Item_DrillDown table (Like if he wanted the value "Red" to be sub divided as "Light Red", "Dark Red...)? If so, is there a way that I can avoid adding new fields to the table each time he wants to drill down further?

    Thanks

  • November 7, 2007 at 6:41 am

    #749077

    Personally, I would go for a parent/child design:

    declare @parentchild table

    ( child varchar(25)

    , parent varchar(25)

    )

    -- Top level

    insert @parentchild select 'paper', null

    insert @parentchild select 'cardboard', null

    insert @parentchild select 'vinyl', null

    insert @parentchild select 'foil', null

    -- Next level

    insert @parentchild select 'Sheeted 1', 'paper'

    insert @parentchild select 'Sheeted 2', 'paper'

    insert @parentchild select 'Sheeted 3', 'paper'

    -- Next level

    insert @parentchild select 'White', 'Sheeted 1'

    insert @parentchild select 'Red', 'Sheeted 1'

    insert @parentchild select 'Yellow', 'Sheeted 1'

    -- Select all rows

    select *

    from @parentchild

    -- Get the top level

    select child

    from @parentchild

    where parent is null

    -- Get values for peper

    select child

    from @parentchild

    where parent = 'paper'

    -- Get values for sheeted 1

    select child

    from @parentchild

    where parent = 'Sheeted 1'

    This has no limited on the number of levels.

    One thing to be aware of is if two different parents can have both have children with the same description - it would lead to spurious results. You could avoid this by creating a unique contraint on the child column.

    Jez.

  • November 7, 2007 at 7:11 am

    #749088

    Jez, that is a good idea. If you put a surrogate key in the table, then it will eliminate the possibility if two parents have the same child.

    CREATE TABLE Parent_Child (

    PCid INT IDENTITY(1,1) Primary Key,

    Child VARCHAR,

    Parent VARCHAR)

    PCid Child Parent

    1 Paper NULL

    2 Cardboard NULL

    3 Vinyl NULL

    4 Sheet1 Paper

    5 Sheet2 Paper

    6 Sheet3 Paper

    7 Red Sheet1

    8 Blue Sheet1

    9 Red Sheet2

    10 Blue Sheet2

  • November 7, 2007 at 9:55 am

    #749159

    Thanks to the both of you. I came up with a similar approach on my own, but I like yours better.

    Thanks again.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply