5 Replies Latest reply on Sep 24, 2013 3:41 PM by ch0c0halic

    Manually creating relationships with calculations.


      Hello FileMakers.


      Please mind that this is my first post on this forum and I am not familiar with the culture of this forum.




      So, to the problem I have.


      I have a bunch of data points that I need correlated.

      Since I am not at liberty to disclose the details of the objects, I will use an example.

      I have many types of "Bacteria" for analysis. The table has "Growth Rate" of "Bacteria" over "Time (Hours)"

      To make the example easier, all bacteria have been analized over the same period of time and there are no missing data points, therefore we can exclude error checking and special cases.


      The picture below represents what I have currently.

      I know this graph is wrong but this is what I can design to the best of my knowledge.

      The "z_Correlation_n" is supposed to be a calculation where it's supposed to loo something along the line of this:

      SUM ( ( OneRecordOfGrowthRateOfBacteriaA - AverageGrowthRateOfBacteriaA ) * ( OneRecordOfGrowthRateOfBacteriaB - AverageGrowthRateOfBacteriaB ) ) / SQRT ( SUM ( (OneRecordOfGrowthRateOfBacteriaA - AverageGrowthRateOfBacteriaA ) ^ 2 ) * SUM ( ( OneRecordOfGrowthRateOfBacteriaB - AverageGrowthRateOfBacteriaB ) ^ 2 ) ) NOTE: each record is incremented with time.


      To be honest the math isn't really important. I ask anybody, "How do design the relationships to calculate with self relating data?"


      If there are other things you would like to point out, please do so. Being criticized opens the path to improvements.


      Screen Shot 2013-09-23 at 14.18.09 .png

        • 1. Re: Manually creating relationships with calculations.

          To make a calculation with self relating data, create another table occurrence of the same table.  You'll have to give it a different name and set up the relationship between the table occurrences.  It works fine even though it is the same table. 


          One thing many of us having been trying since FM12 is to minimize the number of table occurrences since sometimes our graphs get so big.  So if I need a relationship for only one calculation and will never need that relationship again, then I use ExecuteSQL and a join statement to get me the same results without creating another table occurrence.  However, if you plan to make regular use of their relationship back to itself, go with the additional table occurrence. 

          1 of 1 people found this helpful
          • 2. Re: Manually creating relationships with calculations.

            This is a simple example of a dynamically controlled relationship, accumulating original data and using them in altered form to query the same table.

            • 3. Re: Manually creating relationships with calculations.

              Thank you very much for the reply and the attachment. The attachment was definitely worth more than a 1000 words.


              Is it also possible to use this concept to skip certain data points but not skewup the calculation?

              • 4. Re: Manually creating relationships with calculations.

                Here is an extension to the concept: excluding specific datapoints manually. Another would be to include only datapoints within specified ranges etc.


                As Taylor stated: you can either create utility relationships to collect these data in your front end, or (in FM12) use ExecuteSQL in order to keep your Relationship Graph uncluttered.

                • 5. Re: Manually creating relationships with calculations.

                  I'll use your example to highlight a very real problem with how this works.


                  The field "OneRecordOfGrowthRateOfBacteriaA" is used twice. Each usage requires FMP to gather the data required to produce the result.


                  The field "AverageGrowthRateOfBacteriaA" (by its name) is an aggregate. Its used twice so multiply the single gather time by 2.


                  As you can see each usage results in a gather time to get the data.


                  However if this is done in a Let() function the data gathering can be reduced to a single time per value.


                  Let ( [

                  OROGROBA = OneRecordOfGrowthRateOfBacteriaA ;

                  AGROBA = AverageGrowthRateOfBacteriaA ;

                  OROGROBB = OneRecordOfGrowthRateOfBacteriaB ;

                  AGROBB = AverageGrowthRateOfBacteriaB ;

                  //Additional optimizations

                  O_A = OROGROBA - AGROBA ;

                  O_B = OROGROBB - AGROBB


                  ] ;


                  SUM ( ( O_A ) * ( O_B ) ) / SQRT ( SUM ( (O_A ) ^ 2 ) * SUM ( ( O_B ) ^ 2 ) )




                  If the "AverageGrowthRateOfBacteriaA" field is itself a gathering calculation it should probably be inserted in full inside this calculation instead of as a separate field. The reason is dependencies. When gathering data FMP must resolve dependencies and each level requires more gathering of data. Sometimes multiple times. Unless all fields are local its better to resolve all values within a single calculation than it is to use fields to get related sub-values.