Summing

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

Like • Show 0 Likes0
Comment • 5

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.

Attachments

Screen Shot 2017-09-24 at 09.34.58.png73.4 KB

Outcomes

bigtom Sep 24, 2017 5:30 PM

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

)

Actions

bigtom @ bigtom on Sep 24, 2017 2:20 PM

Did not mention it but the suggestion is using the data viewer in FMPA to see what is happening. If you do not have FMPA this kind of debugging can be done using merge variables on a debug layout and just takes a little longer to do.

Actions

beverly

@ bigtom on Sep 26, 2017 5:39 AM

@bigtom said in whole or in part:

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)

Maybe. Making the parameters instead of trying to concatenate (the "&" in calculation dialog) is a good way to:

make it less "brittle" because a change in the table/field name(s) that might appear in the parameter will automatically be changed
will automatically quote properly (single-quotes required around text, no quotes in numbers)
tends to accept dates, numbers, time and pass the correct format for SQL
while not so much a factor in eSQL, because you cannot change schema. But there is can be way(s) to mangle SQL dbs if you don't pass parameters and verify their validity. So it's a great habit into which we should go!

xkcd: Exploits of a Mom

Beverly

Actions

bigtom @ beverly on Sep 24, 2017 5:31 PM

I corrected a small error I noticed in my Let ( ) example. Please make note of it.

Actions

tays01s @ bigtom on Sep 25, 2017 1:32 PM

1. FMPA: Yes, got 14.

2. Parameters: So for eSQL habitually put them all at the end?

3. TO: The calc was from the context of 'A_Date'.

4. Calc script: It works fine.

However, I may have been unclear, but my previous calc did work. It was when I then tried to sum the results of this for the parent table. Eg. Once I've summed Time used for Strategy in Activity for a particular Date, I want to sum time used for strategy for all the dates in a particular study based on the Date::_Study_uuID.

Actions

5 Replies

bigtom Sep 24, 2017 5:30 PM
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
)
Like • Show 1 Like1
Actions
bigtom @ bigtom on Sep 24, 2017 2:20 PM
Did not mention it but the suggestion is using the data viewer in FMPA to see what is happening. If you do not have FMPA this kind of debugging can be done using merge variables on a debug layout and just takes a little longer to do.
Like • Show 0 Likes0
Actions
beverly @ bigtom on Sep 26, 2017 5:39 AM
@bigtom said in whole or in part:
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)

Maybe. Making the parameters instead of trying to concatenate (the "&" in calculation dialog) is a good way to:
- make it less "brittle" because a change in the table/field name(s) that might appear in the parameter will automatically be changed
- will automatically quote properly (single-quotes required around text, no quotes in numbers)
- tends to accept dates, numbers, time and pass the correct format for SQL
- while not so much a factor in eSQL, because you cannot change schema. But there is can be way(s) to mangle SQL dbs if you don't pass parameters and verify their validity. So it's a great habit into which we should go!
xkcd: Exploits of a Mom

Beverly
Like • Show 0 Likes0
Actions
bigtom @ beverly on Sep 24, 2017 5:31 PM
I corrected a small error I noticed in my Let ( ) example. Please make note of it.
Like • Show 1 Like1
Actions
tays01s @ bigtom on Sep 25, 2017 1:32 PM
1. FMPA: Yes, got 14.
2. Parameters: So for eSQL habitually put them all at the end?
3. TO: The calc was from the context of 'A_Date'.
4. Calc script: It works fine.
However, I may have been unclear, but my previous calc did work. It was when I then tried to sum the results of this for the parent table. Eg. Once I've summed Time used for Strategy in Activity for a particular Date, I want to sum time used for strategy for all the dates in a particular study based on the Date::_Study_uuID.
Like • Show 0 Likes0
Actions

Summing

Attachments

Outcomes

Related Content

Recommended Content