4 Replies Latest reply on Aug 2, 2013 8:35 AM by philmodjunk

    calc field based on child table



      calc field based on child table


           I'm going to describe what I need, then explain what I think needs to happen architecturally.

           I have a ASSET Table and a child GOAL Table.  There are many Goals per one Asset.  Each Goal has a status field, with many possible results.

           I want to have a "Status" field for each Asset that displays a calculated result based on the various statuses of child Goals.

           For example:  If any of the child Goals are "In Progress", I want the parent status to read "In Progress."  If any of the child Goals are "Pending AA" or "Pending BB" or "Pending CC," I want the parent status to read "Pending."


           If I put the calc field in the parent table, it will only look at the first related record, correct?  Therefore, I think I should create a 3rd table that will house the calc field. 

           I'm also looking to display the ASSETS table in list view if possible, and will need to generate reports based on Asset Status, so speed is an issue.  What is the most sound way to approach this problem?

           Please advise.  Thanks in advance.  :)

        • 1. Re: calc field based on child table

               Don't think you need the additional table. While most calculation expressions only access the first related record, certain functions can access all the related records:

               List ( Goals::Status ) will produce a return separated list of all the values of all the related fields

               Not IsEmpty ( FilterValues ( List ( Goals::Status ) ; "In Progress" ) )

               will be true if any of the related goals records has a status of "In Progress"

               and PatternCount ( List ( Goals::Status ) ; "Pending" )

               would return a nonzero result (True) if any of the related goals records as the word "status" somewhere in their status field.

               And ExecuteSQL() could also be used to query the Goals table to test for "in Progress" or "Pending"...

          • 2. Re: calc field based on child table


                 A similar method (again :-); much the same as Phil's, but does some of the "testing" within the table native to the data. This is useful if you have several different values in the entry field, which now want to be seen as the same value. The tested values should be return as indexed.* I'm assuming that the field Status has only 1 value in it, in its native table.


                 Calculation field, name "_cStatus", result text:


                 Case (

                 PatternCount ( Status; "Pending" ); "Pending";

                 PatternCount ( Status; "odd words" ) "odd";




                 That produces "Pending" for any value which has it; hopefully the word in not mixed up with something else that you DON'T want as "Pending". If you do not need any special tests, then the last line will just return the value.


                 "Case" returns the first one it matches, so, if necessary, put them in order required. Or, as Phil said, use "FilterValues," which is easier to deal with "false positive matches".


                 This method will add some data to the file, as it should be an "indexed" field (will happen automatically). 


            *Likely a ExecuteSQL() might be the fastest. I don't know (yet) about it.


                 P.S. I wrote much of these earlier, but was distracted by my two grandchildren (3 & 4), who took over both my computers, to play games.

            • 3. Re: calc field based on child table

                   Phil -- I got your method working.  Thank you.  

                   Fenton -- I don't understand the indexing you mention, since my understanding is that calc fields can't be stored.... perhaps because of the grandchildren comment, part of your answer is missing?  

              • 4. Re: calc field based on child table

                     All calculation fields are stored unless it is impossible for them to be stored or you specifically use storage options to specify that they not be stored.

                     Fenton is referring to a field in the child table that does some of the calculation needed. Since it is solely defined with references within the same table, it will be stored by default and this in turn can make for faster results than my original suggestion when dealing with large sets of related records.