1 Reply Latest reply on Sep 11, 2014 8:36 AM by philmodjunk




      Selecting & deselecting records included in a calculation_2


      I think the original threat must have disappeared in the pile.

      I have 4 tables: Person < Analyses < Dates< Activities. There are related thus:

      Person::ID to Analyses::person

      Analyses::ID to Dates::analyses

      Dates::ID to Activities::DatesID


      Activities 2 (TO of Activities) is related using Person::Dates_n to Activities::DatesID

      In activities I've got a cumulative field for each activity, that gives me the time expended on a particular activity on a particular day.

      I have a 0 or 1 toggle fields in Analyses and Dates. I included in the 'selective cumulative activity' field the calculation: Analyses_toggle(0 or 1) * Dates_toggle(0 or 1) * cumulative activity.

      2 questions:

      1. When I present cumulative values on the Person layout I can use the cumulative field to get a value for particular date (from Activities 2) or the total for a person (from Activities), but I've tried creating a field to pick up value created in the above calculation (tried: sum() and max()) but neither works. How do I get this value onto the Person layout?

      2. The 2 toggle fields both, correctly, change the calculated values in Activities. However, when I try to present the value on the Person layout, I find that toggling Analyses immediately changes the value, but toggling Dates doesn't change the value until the Analyses toggle have been modified. NB. I've included a 'Commit records' in the toggle script. How do I get immediate updates to the calculation?


      Did you define "selective cumulative activity" as a number field with an auto-entered calculation or as a field of type calculation?

      Is "cumulative activity" a summary field?

      In what table did you define "selective cumulative activity"?


      1. "selective cumulative activity" is a calc field, not an auto-entered calculation

      2. "cumulative activity" is a calculation field of 2 summary fields: Summary_reverse-Summary_running total [it is done this way because my records are listed in reverse order.

      3. "selective cumulative activity" is on Activities and I've already been wondering if that is the wrong place.

        • 1. Re: Selecting & deselecting records included in a calculation_2

          Hint: you can always "bump" your thread back to the top of Recent Items by posting a comment to your own thread as long as the thread is less than 3 months old.

          You are also welcome to Private message me with a link to your thread and ask me for a response to it.

          Relationships as I sketch them out in this forum:


          If I understand your calculation correctly, it reads:

          Analyses_toggle * Dates::Dates_toggle * Activities::cumulative activity.

          And it's defined in Dates and evaluates from the "Context" of Dates....

          The problem is that Cumulative activity comes from a related table on the "many" side of a one to many relationship. While there are many records in Activities that might be linked to a given record in Dates, only the data from the "first" related record will supply the value that you see in cumulative activity. If this field were a summary field, you'd get an aggregate value based on all related records in Activities, but since it's a calculation field, you only get the data from the first related record. That "first" related record will be the oldest related record unless you have specified a sort order as part of the relationship that specifies otherwise.

          1) sum or max should work. If they don't take a look at your relationship and the match values. What value or values are you putting into Dates_n? are they matching to the correct set of records. (Note that this matches strictly by date, not by any other criteria. You may want to temporarily put a portal to Activities_2 on your Person layout to see what records are currently linking. An unsorted unfiltered portal to Activities_2 should show you what values will be used to compute a Max or Sum when you use those functions in a calculation in Person.

          2) Try Refresh window, Try Refresh Window [Flush cached join results]