You might want to start looking into valuelist filled with SQL.
You attach a script trigger to the onObjectEnter and a parameter you set the ValueList (if you set it in the script, it's too late for the field to use :-))
You base the value list on a global.
It's one of those FileMaker tricks you will learn to make FileMaker do stuff it normally can't do.
I also suggest submitting a feature request. SQL based valuelist...
Thank you for your answer!
This seems to be in the direction of what I need, but...
What I want to achieve is this:
One of my subcategories has about 60 products. Then there is the list with failure keys that depends on the product for most subcategories. Except for this one and another one. I want to save some time by not having to type every product with its ten characteristics in the table.
Also because I am creating this database for someone else and I don't want them to have to go to the trouble of checking all 60 products if they want to add a characteristic to that subcategory.
With the value list based on SQL you still have to make a table with all the combinations, don't I?
I'm not sure what "failure keys" mean, but you can use 2 value lists apply to 2 copy of a field, then set "Hide object when" expression for "condition" and place them same position.
Well you need to get the failure keys from somewhere... If you look into the SQL value lists, you don't really have to use SQL to fill the list. The SQL result is put in a global, that global is used as a basis for the list.
So you can just say LET ($$z_valuelist = list ("key1" ; "key2" etc )
Where are the failure keys and subcategories now? If they are in a table, you can just query that...
Could you expand a bit what do you mean by "Failure Keys" ? I imagine you could have a drill down scheme with a script trigger that identify the Failure Keys
The Failure Keys are basically ways in which a product can fail. Every product has different ways to fail and sometimes the options depend on the category (for example a table can fail in the same ways independent of its size) and sometimes they are dependent on the product itself. The user has to be able to select multiple keys.
I am very sorry but I still don't understand what fails and what action happens if the product fails.
The database is meant to register when a certain product fails. Therefore the name of the product has to be included. To facilitate the selection of the product I have made a conditional value list. The way it failed (or the reason for the failure) has to be included as well. To be able to perform find functions in a usefull way the user cannot invent their own failure keys but they have to be selected (otherwise you get variations on the same words and you wont be able to perform useful find operations).
So the thing that happens if a product fails is that the failure is put into the database.
I would add a table with category / failure_key. And add an interface so you can maintain the failure keys. Seems there will be new failure keys in the future.
I also make things for repair companies and there we name it "known_issues" :-)
Each know_issues is specific to a phone brand/model. Of course there are issues that occur in all, but I just duplicated the issue in the known issues table. The know_issues are "validated" by the part suplliers/manufacturers so the do not change that much.
The advantage of the separate table is, that I don't have to maintain it. Someone else with no programming skills does :-)
If you do use the value lists based on globals you can build that list in two or more parts ....
when using SQL for example you can do something like.
SELECT failure_keys FROM failure_key_table WHERE failure_key_table.category = ? ; product::category
SELECT failure_keys FROM failure_key_table WHERE failure_key_table.category = 'GENERIC'