JarlSilvén

Circumvent limitation on value list with related fields

Discussion created by JarlSilvén on Jun 26, 2016

Let Cooperation be a table that describes the cooperation between different objects of the Producer table. First, some example Producer elements:

 

ProducerIDNameInfo
1Appleokay
2Microsoftmeh
3Linuxyay
4Linosoft
5

 

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.

 

CooperationIDProducerID
42
43
51
52

 

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.

 

ProducerID
NameInfo
4Linosoftmeh, yay
5Apple / Microsoftokay, 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

Apple / Microsoft

Microsoft

Linosoft

Linux

 

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:

 

  1. How do you write a script which refreshes a stored field manually?
  2. Is there another, better, circumvention with the current database layout?
  3. 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

Outcomes