3 Replies Latest reply on Sep 3, 2014 7:08 AM by philmodjunk

    help with a conditional value list



      help with a conditional value list


      Hello Everyone

      I am new to the forum, am looking for some help trying to figure out how to approach a new layout for my database.

      Have a table with a lot of items already categorized, the item has a SKU, the brand, the name and a brief description. Each item has 3 categories, (3 levels). The item technical description are on a separate table, this table has the SKU, the description name and the description value.


      TABLE 1

      SKU: 001

      Brand: ISCAR

      Name: End mill tool holder

      Category_1: Cutting Tools

      Category_2: Milling

      Category_3: Tool Holder

      TABLE 2

      SKU: 001

      Description name: Tool Length

      Description value: 3"


      Description name: Spindle type

      Description value: BT40

      The categories have been put together in 3 tables, (Category 1, 2 and 3), each category have a Category ID and a Parent ID, (for example, Category name on 1st level has an ID, while Category name on 2nd level have the ID from the parent of level 1 and the ID from its own level). I did it this way because I considered it would be easier to include new categories and subcategories.

      What I want is to be able to fill a portal based on the items I have selecting the 3 category levels for each item, every single item has been categorized under the 3 level scheme, even if so far it seems that a 4th or 5th level is not needed it would be great to make the process "expandable".

      I have been trying every solution I have found on the forum and the internet under "conditional value list" and "dynamic value list" as well without any good result. Any feedback will be really appreciated.

        • 1. Re: help with a conditional value list

          What I want is to be able to fill a portal based on the items I have selecting the 3 category levels for each item,

          I can interpret that several ways.

          A portal from what table on a layout based on what table?

          Are you creating new records in the portal or displaying records from Table 1, or maybe Table 2?

          Assigning your records to categories looks to be a key source of trouble here, especially if you want to assign each record in a table to a fully flexible number of categories. Linking them to a categories table where assigning a record to a category is done by creating a new related record is the way to go for full flexibility as this makes adding more categories strictly a data entry operation rather than having to make design changes to your database in order to add more than three categories.

          So far from your description, I'm not fully sure that you actually have a conditional value list to set up here. It depends on what you want to do with that portal.

          I have been trying every solution I have found on the forum

          Here's a brand new one that I just recently created. You may or may not have seen it already: https://www.dropbox.com/s/8phiqrgpts4drre/Adventure%201%20CVLs.fmp12?dl=0

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: help with a conditional value list

            Thanks for your answer and thanks for sharing the file, I haven't seen it yet and I definitely will take a look at it.

            I may be completely wrong on the approach I am taking on this, (my knowledge on this is 100% self thought). Let me explain what I have and what I want. What I have is a long list of items, (around 14.000), each item have basically a SKU, a name, a supplier/maker and a list of characteristics, (like size, dimensions, weight, color, etc). Categories are created to simplify the grouping of the very large quantity of items since they are not all for the same application.

            I have arranged all this information under 3 main tables. 

            TABLE 1 - SKU, NAME, BRAND, CAT_1, CAT_2, CAT_3


            TABLE 3 - CAT_1, ID_1, CAT_2, ID_2, CAT_3, ID_3

            What I need is to be able to locate an item, or a group of items, based on a dynamic search. Rather than write the key aspects of the usual search and hit enter, I want a menu of options based on the categories and then the descriptions of the items I have on my database, doing the search this way will allow the user to find all the available options for an application he have in mind. User will also get suggestions based on related items according his search.

            I am not looking to add records on the layout, actually I haven't started to design the layout yet, eventually I will need to create a layout to add new records but thats fine, the user only have to consult the database and make a report of the items based on his search.

            The whole idea goes this way. User have the first menu, which is a value list based on fields from CAT_1, the second menu is loaded with a value list using the related fields from CAT_2 that match the parent ID selected on CAT_1, same goes with CAT_3, (I guess it shouldn't be a trouble to eventually add a CAT_4 or 5 since most likely it will be needed in the future).

            The layout where the user make his selections must have a portal that shows the list of items that match his criteria, the portal has to be updated while he narrow his search. Once the CAT menus are done the user should be able to narrow his search based on the descriptions criteria, lets say the last menu show all items that match CUTTING TOOLS - MILLING - TOOL HOLDERS, the list will show a bunch of items that not necessarily suit his needs, he have to be able to go down his search based on "Spindle Type", "Type of tool holder", "length", etc, all these values come from the description table. Depending on the end result of the Category search the result will vary. 

            Did I went the wrong way on this? 

            • 3. Re: help with a conditional value list

              To repeat, the approach that you are taking with categories lacks flexibility as adding additional categories requires making design changes to your database. There are other options that make this strictly a case of adding more records to a table rather than having to add a field and then modify multiple design features, scripts, etc to incorporate that new field into your system.

              It looks like you are creating a chain of Hierarchical Conditional Value Lists here and these are documented in two different configurations in the Adventures In FileMaking #1 file.

              Some other things that pop up questions in the back of my brain: Do you have exactly one record in Table 1 matching to exactly one record in Table 2? From what you have posted here, I don't see any advantage to dividing this data into two tables if the relationship is "one to one".

              Where you have a categories table with one field for each category, I'd use a table like this:

              _fkProductID, category

              And if an item is a member of 6 categories, You'd create 6 records all with the same value of _fkProductID matching them all to the same record in your Table1, table2 or a merged table that combines the data of Table1 and 2 in one table.