Let Cooperation be a table that describes the cooperation between different objects of the Producer table. First, some example Producer elements:
The first 3 producers are normal producers. But the last two are cooperations, where 4 has been given a name, and 5 lacks a name for now.
Next, we make example elements in the Cooperation table. It says which producers participate in particular cooperations.
This will form a new relationship back to the Producer table, let's call it Producer2, which will allow the Producers 4 and 5, who lack their own information, to gather information from their participant producers.
Also, if a Producer lacks a name, it may concatenate its name from its participants, if it has any.
|5||Apple / Microsoft||okay, meh|
Here the problem arises! The Apple / Microsoft name uses data from a "related table", even though it's the same table. This doesn't have any consequences except for that to make a layout with a convenient value list, containing all producers, will be impossible to index as calculations with related fields cannot be stored.
The end result, a sorted value list, would look something like this:
Apple / Microsoft
So, how to circumvent filemaker's block? I've tried using ExecuteSQL to fetch the name, which allows it to be stored but Filemaker does not recognize when a field is changed so the value list stays the same.
The solution would maybe be to write a script refreshes the value list, and is called whenever the user EXIT the Producer defining layout. As such, it would not cause much lag, as the database isn't very big.
Depending on what approach is best, I have the following questions:
- How do you write a script which refreshes a stored field manually?
- Is there another, better, circumvention with the current database layout?
- Is there a better database design, which would allow the value list to work properly but still be versatile with cooperations?
Long read, but hopefully I made myself clear