Conditional Value List based on multiple tables

[mdavis6537]

Hi Guys,

 

I know there are many posts about conditional value lists, but I can quite seem to find one related to how I am setting mine up - maybe its because it doesnt make sense to do it this way.

 

See my screenshot attached, but basically I have 3 related tables - I am using this file as a basic demonstration of what I want to do:

 

Class > Category > Sub Category

 

I want to have drop downs that filter just like a normal conditional value list. Where I would like it to change from the standard setup though, is I would like the values for Class, Category, and Sub Category to be in their own tables. The reason for this is I already had these tables and they have a fairly significant amount of data in them. 

 

Lastly, I have my data table which is called "Line Item". Each line item I create will have a Class, Category, and Sub Category that I want to set with these drop downs.

 

I have been able to get my Class > Category to work correctly, but from there I cant get my sub categories to filter correctly. The Sub categories show from what seems to be, all the available options for the selected class, instead of the selected Category.  I see several of the other posts recommend putting all that data in one table. Is there any possible way to use my current setup, or do I need to just combine the 3 tables into one and re-enter the data?

 

Thanks!

[comment]

Where I would like it to change from the standard setup though, is I would like the values for Class, Category, and Sub Category to be in their own tables.

 

There's not much difference between the two. The TO's required are the same - it's just that with separate tables, you will have a different table behind each TO.

 

VL3SeparateTables.fp7.zip

[mdavis6537]

Ok thats good news that it can be done, thanks! I will download your sample file when I get home and take a look.

 

Thanks again for the reply.

[mdavis6537]

Wanted to follow up and say thanks again for linking the example - I was able to get mine all setup and working. One more question I am wondering if you can help with.

 

MANY of my "sub categories" need to be linked to multiple categories. 

 

For example, for class one, I need all the categories listed, and all the sub categories for those categories.

 

For class two, I need all the same categories and sub categories as class 1, plus a couple other ones.

 

ect, ect.

 

So the problem I am seeing is I have to duplicate my Categories and Sub Categories a ton, linking each one to all of the applicable categories before it.  I can manage that, its not the end of the world, but would there be a better way to structure my setup perhaps? It would be great if you could link a record to multiple Foreign Keys - for example I could link a Category to Class_ForeignKey 1, 2, and 3. But it doesnt seem that is possible.

 

Thanks again.

[comment]

MANY of my "sub categories" need to be linked to multiple categories.

 

I am not sure I understand the situation you describe. Could you provide a practical example?

 

 

I also wonder what's the point of providing a conditional value list if one selection does not reduce the choices for the next selection. That's assuming I understand correctly this part:

 

For example, for class one, I need all the categories listed, and all the sub categories for those categories.

[mdavis6537]

Its completely possible I am going about this the wrong way <-- filemaker noob.

 

The ultimate goal is to select from a list of dropdowns and build my data table of what work needs to be done on a job. The classes are not school classes, but types of damage. A class 1 damage might have a Category of Floor > Replace Floor. Class 2 might also have Floor > Replace Floor as option, but might also have another Category of Mold > Remove Mold that would not need to be shown if this was a class 1 job.

 

Class 1 has a bunch of Categories, and each Category has a sub category.

Class 2 has all the same categories, but then adds about 3 more Categories to its list with their own sub's.

Class 3 has again, all the same categories as 1, but adds more that dont need to be shown in 1 or 2.

 

The only thing I am trying to accomplish with the conditional value lists, is that when they select Class 2 instead of class 1, they see the additional choices listed that are not under Class 1.

 

I can make it work with the example you provided and it will work just fine. An individual wont be adding new Categories very often at all, so having to duplicate them isnt really that big of a deal, I just wanted to make sure there wasnt a better way that I should learn about.

 

 

You asked for a real life example, let me try and create one:

 

How about Schools with Classes, and State Tests:

 

So you have schools - Each school has several classes that a student can attend. Each Class has several tests that are completed for that class - these are the same tests, regardless of the school you go to.

 

School 1 has english, math, and science

School 2 also has english, math, and science, but they also have shop.

School 3 has english, math, science, shop, and cooking.

 

 

When you are selecting school 1, you dont want to show shop and cooking, because they are not offered at school 1. And regardless of whether you select School 1 - math, or School 2 - math, the State tests for math shown are the same.

 

I hope that makes some sense.

[comment]

Well, you are describing a many-to-many relationship between the categories tables. So regardless of the question of conditional value lists, you need to resolve this pone first.

 

Properly, you should have a join table between Classes and Categories, and another one between Categories and SubCategories. Possibly, you could manage by changing the foreign key fields (Categories::ClassID and SubCategories::CategoryID) into text fields and use checkbox sets to populate them with multiple parent ID values.

[mdavis6537]

Comment - thanks again for all your help. As an update - I was able to get this up and going using your suggestion of a text field.

 

For any future people reading this, what I did was change the Primary Key and Foreign key fields in the Category and Class tables to text fields. What this allowed me to do, was set the Class Foreign Keys to multiple values seperated by a return (enter key). Now a category can be attributed to several different class Primary Keys, without having to have any duplicate records.

 

I am sure its not always a best practise to use text fields for primary and foreign keys because of human error, etc, but in this case it worked perfectly and allows me to not have to duplicate any entries, while allowing my records to be associated with more than one foreign key.

 

Comment - thanks again for all your help.

[dmontano]

When creating conditional value lists - similar to the file provided by Comment above where the values for each drop-down are stored in separate tables — is it a requirement to store the foreign keys in the OBJECT table?

 

It seems to me that the only purpose of creating the foreign key fields of "continent" and "country" in the OBJECT table is to facilitate the feature of providing a conditional value list.

 

A few questions:

1) Is it possible to provide the conditional value list feature with global fields instead? Are there any downsides to doing so in a multi-user environment, or any other situation?

2) Since the relationships between all of the tables (CONTINENT < COUNTRY < CITY < OBJECT) already exists prior to creating the conditional value list, it makes me wonder if there is not a way to provide this feature without the additional TOs and foreign key fields inside of the OBJECT table to provide this functionality. Is it possible instead to build these conditional value lists where FileMaker is using the initial table relationships?

 

I am asking because I need the conditional value list feature and having to create additional TOs and foreign key fields just for this feature seems like there has to be a more "native" way. I guess my concern is that foreign key fields and their values are being stored in the OBJECT table and that seems redundant.

 

Ultimately, it would be comforting to know what the "best practice" is for building a conditional value list when multiple tables are involved, so I can stop wondering if I should be doing it a different way.

 

Thanks in advance.

[comment]

is it a requirement to store the foreign keys in the OBJECT table?

No. Only the last relationship (between Objects and Cities in the above example) is a core relationship. The other relationships are auxiliary relationships, designed only to facilitate the choice of the CityID. If a choice isn't being made, the other foreign keys are unnecessary, even redundant.

 

1) Is it possible to provide the conditional value list feature with global fields instead? Are there any downsides to doing so in a multi-user environment, or any other situation?

 

Yes, it's possible. The downside is (or can be) a confusing (i.e. conflicting) display. This can be solved by hiding the globals when a choice is not being made, or switching them by a script triggered OnRecordLoad.

 

2) Since the relationships between all of the tables (CONTINENT < COUNTRY < CITY < OBJECT) already exists prior to creating the conditional value list, it makes me wonder if there is not a way to provide this feature without the additional TOs and foreign key fields inside of the OBJECT table to provide this functionality.

No. There may be a way to place the additional TOs and foreign key fields elsewhere - but it is not possible to use the existing relationships to provide fundamentally different related sets. However, there are other methods of selection, that do not require conditional value lists (or value lists at all).

[dmontano]

Thanks Comment,

 

I will build conditional value lists similar to the example file with fields in the OBJECT table to facilitate the "auxiliary" relationships. As for the globals route - I will toss that out based on your comment. And the "native" relationship magic I was hoping was hiding somewhere in the value list options is now off my wondering mind.

 

However, your last statement has peaked my interest:

 

No. There may be a way to place the additional TOs and foreign key fields elsewhere - but it is not possible to use the existing relationships to provide fundamentally different related sets. However, there are other methods of selection, that do not require conditional value lists (or value lists at all).

 

Would you elaborate on this a bit?

[comment]

For example, you could present the user with a list view of Continents to select from. Clicking on any one Continent would take the user to a list view of related Countries (using GTRR over the existing, core relationship!). Similarly, after selecting a Country, the user would be taken to a list of the related Cities - again, using the "normal", existing relationship. Only selecting a City would actually store the selected City's ID in the current Object record. You would probably want to pop up a new window for making these selections.

 

 

Note that in a recursive structure of categories (in this example, using a single table of Locations), you could test for the existence of related records and automatically end the chain of selections once you get to a leaf node. So the user might make 5 selections in order to assign an object to America/North America/USA/Ohio/Cleveland, but only 3 to get to Europe/Germany/Berlin - all using the same mechanism.

 

 

See also:

http://fmforums.com/forum/topic/72391-getting-more-out-of-filtered-portals-6-hierarchical-portal/

[dmontano]

Hi Comment,

Sorry for the delay in saying thanks for your contribution. I like the idea of the GTRR. What I like most about it is it seems that it does not matter whether your relations are in a recursive single entity type table or separate entity type tables.

Thanks