Summary field not calculating

[ddinisco]

I have a summary field that is summing a field called 'count.'

The 'count' field is counting the number of records in a different table based on a relationship. Thus the values in the 'count' field changes based on the criteria set. The 'count' field updates properly, but the summary field is not updating when the criteria is changed.

The summary field is located in a TGS part.

Any help would be much appreciated as to what I may be doing wrong.

Thanks

Edited February 9, 2009 by Guest

[bcooney]

Could you explain what you're trying to achieve in more detail? Please use your data in the example. I'm not following why you'd sum a count. What tables are involved?

[ddinisco]

Sure will try.

I have two tables...Jobs and Type. In the Job table I have a Type field that is a pulldown list based on the records in the Type table.

In the report I want to show all of the Types of jobs along with how many jobs contain that type (within a certain date range). I want to show all of the types of jobs in the report even if there are 0 jobs with that type.

The values from the type are correct, but the summary of type is not.

Sample may look like this..

TypeA 15

TypeB 05

TypeC 00

TypeD 01

Total 21

[bcooney]

I come up with this (see attached). Notice I build the report from the JobType table, since you require all types, even if there are no jobs of that type.

JobType.fp7.zip

[ddinisco]

Thanks. What I have discovered since we last spoke is that it does seem to calculate correctly, but only the first time.

The count field is based on user filter/relationship using 3 other fields, dateStart, dateEnd and jobStatus. The first time entering the solution (all fields empty) and running the script works fine. However once any one of the filter fields are modified...say changing the jobStatus (while looking at the report), the count fields update properly, but not the total (summary field).

I guess that is what the mystery is now.

Thanks again for you help.

[Søren Dyhr]

I come up with this (see attached). Notice I build the report from the JobType table, since you require all types, even if there are no jobs of that type.

Which must be wrong, since Count( over a relation doesn't respect the found set ... try to omit a record and the report is unaltered!!

I would very much like to see a non scripted way to accomplish it, but can at this moment not come up with anything better than the way I do it in the attached template. I do however have a hunch telling me I'm forgetting something??

--sd

JobTypeSD.zip

[comment]

I can't see anything wrong in using a relationship to determine what's included in the report, instead of a find.

the count fields update properly, but not the total (summary field).

In versions earlier than 9, there's a problem with refreshing summaries that depend on aggregated join results. A partial solution is to reference the filtering fields in the aggregating calculation (see the attached for an example). The other part is that you must commit the record after modifying the filtering values.

FilterReport.fp7.zip

[Søren Dyhr]

I can't see anything wrong in using a relationship to determine what's included in the report, instead of a find.

Neither can I but Barbara did not include filtering over the relation... did she mean to??

But why then use a summary field at all? One answer to this would be that aggregate functions doesn't scale too well ... and establishment of a found set should then include some safeguards against too large measures. Does the combo buy a little time or rendering speed?

. The other part is that you must commit the record after modifying the filtering values.

Would that be sufficient? Flush cache to disk is it more likely which means a button provided script line at least.

I want to show all of the types of jobs in the report even if there are 0 jobs with that type.

This means that the "Do not evaluate...." should be lifted in your cCountChildren field.

--sd

[comment]

Perhaps you missed this important point:

I want to show [color:red]all of the types of jobs in the report [color:red]even if there are 0 jobs with that type.

Would that be sufficient?

Yes.

[comment]

You have edited your message while I was replying. The quotes in red are in reply to your question why a combination of aggregating calcs and a summary field is being used. There are other ways to produce a union report, but this is the easiest one, I think.

"Do not evaluate...." should be lifted in your cCountChildren field.

I see no reason to do so.

[Søren Dyhr]

Niether do I, except it seems a requirement!

TypeA 15

TypeB 05

TypeC 00

TypeD 01

--sd

[comment]

And what's wrong with that?

[Søren Dyhr]

00

I do not think you by conditional formatting can fit in something if a field is empty - or can you?

--sd

[comment]

I have no idea what you are talking about. The file I have posted does NOT show double zeros. It DOES show a zero when there are no related records that meet the filter criteria. I believe that is the correct result, but if you don't like it, you can format the field as 'Do not display number if zero'. I really don't see the purpose of this nitpicking.

[Søren Dyhr]

Remove all the checks in you template as watch the number of empty lines ... just a tiny detail really!

--sd

[comment]

It makes absolutely no sense to remove all checks and expect a meaningful result. Keep in mind that this is a demo - obviously, in a real implementation the filtering field should not be allowed to be empty, because then there is no relationship, and the fields are - correctly - showing no result.

[Søren Dyhr]

True but neither does it make sense to expect all categories to have matches - hence the need to make the summary from a relation away from the most atomic part of the data.

Why is it not worth to lift the checkbox "Do not evaluate..." for the calc'field - will the layout rendering get slower when these situations arises?

--sd

[comment]

neither does it make sense to expect all categories to have matches

If a category does not have a match, then the result returned will be "0" - whether the field is set to evaluate always or not. I have already said this two or three posts ago. Now, if you'll excuse me, I think this has gone on long enough.

[bcooney]

"Neither can I but Barbara did not include filtering over the relation... did she mean to??"

-No, she did not. I wanted to start without the date filters first, and build from there to establish common ground.