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.
eSQL always seems to be happier when all the parameters are added at the end of the function like this.
Moving all ExecuteSQL( "SELECT SUM(\"Time\") FROM Activity WHERE Activity = ? AND \"_Date_uuID\" = ?";"";""; "Strategy"; A_Centre::Date_n)
Do you have FMPA? Using ExecuteSQL relies a little on context since you are using a parameter from a related table.
You might try debugging by using a Let ( ) statement and global variables to see what the parameters actually are when it evaluates while on a record. This also makes ti easy to quickly make changes and see the results. What is the TO for the layout where you are evaluating this?
Let(
[
$$date_n = A_Centre::Date_n;
$$activity = "Strategy";
$$result = ExecuteSQL( "SELECT SUM(\"Time\") FROM Activity WHERE Activity = ? AND \"_Date_uuID\" = ?";"";""; $$activity; $$date_n)
];
$$result
)