The relationship should be linked this way:
Estimate::MaterialCategory = MaterialSelect::FK_materialCatID
The 2nd value list should then refer to materialCategory in both columns not just the first and the starting from field should be estimates, not material category.
Hmm Phil, thank you soo much for getting back to me quickly. Unlike you simple link to the knowledge base I am using a join table...hence where life is going tragically awry. Are you saying break my one to many relationship betwin estimate and matSelect in favor of what you have written>? K Thanks!
Oh and in addition Phil, do you know of an active filemaker irc channel?
All I am recommending is a relationship and value list definition that will work for you in terms of your value list's behavior. If changes to the relationship have undesirable effects on other parts of your database, then you would need to set up a separate relationship just for your value list.
Phil, dude you have answered many of my questions before and you are so super quick to respond, I love it. But these two responses are absolute trash. Thanks bud, way to be an awesome stewart for FileMaker
It's your database project, my suggested solution was made after opening and examining your uploaded file. I've set up many conditional value lists and helped many people get it working in their systems.
If the suggestions I made don't work for you, feel free to explain how/why it doesn't work and I'll take another swing at it to help you out.
I've been taking a closer look at your file and one aspect of your current data is confusing.
In Material categories, MaterialCatID contains a serial number as I would expect it to, but in Materials, FK_materialCatID contains text such as Paper, PET, SYN which corresponds to values in the text field, MaterialCategory. Thus, since your first value list enters the ID number MaterialCatID into Estimates, there's no matching value to make the relationship needed for the conditional value list to work.
If I change the first value list to simply enter MaterialCategory as column 1 into Estimates::MaterialCategory, then I can get my suggested solution to work.
However, in the long run, If I were you, I'd prefer to update the records in Materials to link to Material Categories by the serial number in place of the name so that I can use a relationship based on the Category serial numbers in place of the names. That change would make it possible to update category names without breaking the relationship needed for your conditional value list.
If you'd like, I can update a demo copy of your file with these corrections in place.
Phil, thank you for your help yesterday, sorry if I seemed rather brash. I realized the mistake with the Foreign Key ID being replaced with a text selection and fixed it (the key foreign key value isn't displayed on the test layout) however, things still aren't working and I am equally baffled. Here is an updated copy, and I do want the primary key / foreign key values to be what links the material from the matcategory.
You don't have any values in Material::_kf_materialCatID and these fields are set to look up MaterialCategory::MaterialCategory--which won't work here as the look up changes the value in this field and breaks the relationship. Once I disabled the look up and entered numbers of 10 or 11 into this field for every record in material, the conditional value list worked as you have defined it.
Material::MaterialCategoy, BTW, is not a field you actually need in your material table. The corresponding text field in MaterialCategory is sufficient.
Thanks Phil, I should have gone back through and turned off that specific lookup field. What would be the most efficient way to place those numbers into the fk field on record creation? I was mistaken in thinking that field automatically gets updated, but I guess I was wrong.
If it were automatcially updated, how would your system know to what category to assign it?
The basic two options are to either use a portal to materials on a materialCategory layout with the allow creation of records via this relationship enabled so that entering new record automatically assigns it to the category of the current MaterialCategory record or you format this field with a pop up menu or drop down list of categories so that the user assigns the category by selecting it from the value list.