Jump to content
Sign in to follow this  
ddinisco

Summary field not calculating

Recommended Posts

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 by Guest

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

"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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.