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
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 2 - SKU, DESC_NAME, DESC_VALUE
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?
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:
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.