Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2013
    Posts
    226

    Unanswered: Category and subcategory selection

    Hi

    I was wondering if anyone could suggest the best approach for allowing the user to select a category. There will be some 7 levels in the hierarchy and perhaps a total of 8000 categories available to pick from.

    Ideally the user selects first category and then the available sub categories are shown then there may be further subcategories etc.

    I've seen the special list based on a list but that only appears to work correctly with a single level. You can't it seems base a list on a list based on a list and so forth. And I'm not sure you could create this setup via an import.

    The other option would be to hold this data in a form or forms and perhaps process using scripting.

    It would be useful to know how others have approached this.

    Thanks

    Matty

  2. #2
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    7 levels and 8000 categories. Wow.. that's pretty detailed.

    Sadly, using the default function “Drop Down List” will not do you at all right now. It does not function correctly with anything more than 2 levels deep. There's a very odd bug that doesn't allow you to edit the entire list when you get to level 3.

    A workaround is to enable the “allow list edit” function so you can populate the fields when the form editor is closed. That said, while this workaround works, it's probably not that safe to be using something that's bugged.

    Maybe it will be fixed in v10, but if not, a bug report will need to be filled for that one.

    So, right now your kind of caught. You can't use the best method which is the default one, so you would have to look at using an alternative function like a hidden folder of records that are used as your list.

    My 2 cents.
    Best to wait until v10 is out, and then file a bug report if it's still broken.

    Regards
    James

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Would it be possible to select the parent level on one form, then close that form and open a new form to select the sub-level? You could repeat this process with as many forms as you have levels.

    I have zero experience with Brilliant Database, but this concept is used in many environments to work around implementation limitations like this.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2013
    Posts
    226
    Hi James,

    Thanks for this. I have just checked v10RC1 and the same bug is present so I have sent Mikhail an e-mail about it.

    Matty

  5. #5
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Have not actually tested but would it be possible to create a form with with 8000 records and 7 fields (at minium) to hold your category items. Then run a query based on previous selection (variable from selection). This should create a record set filtered by last selections. Drop lists can then be based on a query for each list. You would need to format the record set to next wanted field ( do not want record no but the actual value). A lot of work but with 8000 combinations which ever way is dealing with a large set of values. It might be possible to automate some of the record creation with scripts initially.

  6. #6
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Hi Pat.

    Would it be possible to select the parent level on one form, then close that form and open a new form to select the sub-level? You could repeat this process with as many forms as you have levels.
    Yes, good idea.
    That would work for sure, but the next form would not open automatically. The operator would have to click a button for each one of the 7 levels Matty needs.

    Regards
    James

  7. #7
    Join Date
    Apr 2013
    Posts
    226
    Hi

    Quote Originally Posted by tamcind View Post
    Have not actually tested but would it be possible to create a form with with 8000 records and 7 fields (at minium) to hold your category items. Then run a query based on previous selection (variable from selection). This should create a record set filtered by last selections. Drop lists can then be based on a query for each list. You would need to format the record set to next wanted field ( do not want record no but the actual value). A lot of work but with 8000 combinations which ever way is dealing with a large set of values. It might be possible to automate some of the record creation with scripts initially.
    Thanks. I'd thought about doing it all in one big form like that but I'm not sure how it would be implemented in Brilliant Database as you would need just to return the unique values in the parent level each time - a distinct query in SQL - but I'm not sure how that is done in BD - still learning how things are done in it. Anyone know if this is possible?

    Matty

  8. #8
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    You can tick the "unique " box in query editor for a particular field name.

  9. #9
    Join Date
    Apr 2013
    Posts
    226
    I've finally returned to this and am just doing simple test. I have a form with similar to:

    Level1 Level2 CatID
    UK England 01
    UK Wales 02
    USA Texas 04
    USA California 06

    I have created two drop down lists for each level that are populated based on a query of records. This works fine with an on edit script to clear and refresh level 2 when level 1 is changed.

    Great, but what I am actually ultimately interested in is the unique final code in the third field once the user has selected level 2 (or for that matter level 1 if no level two exists) but I'm a bit stumped how to get that into another field on my form. All I have on the form are values - for example California in the level 2 drop drown list, but that isn't linked at all to its associated CatID. I guess I could have another dropdown list and just treat that as a dummy level 3, but don't really want that appearing in a list box.

    I hope that makes sense.

    Thanks!

    Matty

  10. #10
    Join Date
    Sep 2011
    Location
    Australia
    Posts
    264
    Provided Answers: 2
    Hi Matty
    If you don't get a solution, might it be possible to upload a sample file of your current state with a few text labels explaining what you need near fields. Are the id codes just a reference for the state names in your example - in which case lookups might work?
    Regards
    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •