0 Replies Latest reply on Jun 26, 2016 2:21 PM by JarlSilvén

    Circumvent limitation on value list with related fields

    JarlSilvén

      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