tays01s

Summing

Discussion created by tays01s on Sep 24, 2017
Latest reply on Sep 25, 2017 by tays01s

See attached. The primary relationships are represented in the top row. Some of the other relationships are experimental. The bottom column of A_Person/Study/Date_n are used to show summaries in portals of Person/Study/Date on the Centre layout.

 

I am trying to sum Activities. These are from the Activity::Activity field. Eg. I have a Date::Strategy calc field using:

ExecuteSQL( "SELECT SUM(\"Time\") FROM Activity WHERE Activity = 'Strategy' AND \"_Date_uuID\" = ?";"";"";A_Centre::Date_n)

to sum the amount of time used for Strategy on a specific date. This appears in the Date_n::Strategy field within the Date_n portal on the Centre layout.

 

I should then like to sum activities for Dates within each Study and Studies for each Person, Persons for each Centre. I have tried using a simple 'Sum(field)' based upon relationships and well as SQL. I end up getting either '?' or double whatever Date is in focus. Obviously I'm missing something in what appears a simple exercise. Some pointers would be appreciated.

Outcomes