ddinisco Posted February 8, 2009 Share Posted February 8, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
bcooney Posted February 8, 2009 Share Posted February 8, 2009 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? Link to comment Share on other sites More sharing options...
ddinisco Posted February 8, 2009 Author Share Posted February 8, 2009 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 Link to comment Share on other sites More sharing options...
bcooney Posted February 8, 2009 Share Posted February 8, 2009 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 Link to comment Share on other sites More sharing options...
ddinisco Posted February 9, 2009 Author Share Posted February 9, 2009 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. Link to comment Share on other sites More sharing options...
Søren Dyhr Posted February 9, 2009 Share Posted February 9, 2009 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 Link to comment Share on other sites More sharing options...
comment Posted February 9, 2009 Share Posted February 9, 2009 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 Link to comment Share on other sites More sharing options...
Søren Dyhr Posted February 9, 2009 Share Posted February 9, 2009 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 Link to comment Share on other sites More sharing options...
comment Posted February 9, 2009 Share Posted February 9, 2009 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. Link to comment Share on other sites More sharing options...
comment Posted February 9, 2009 Share Posted February 9, 2009 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. Link to comment Share on other sites More sharing options...
Søren Dyhr Posted February 9, 2009 Share Posted February 9, 2009 Niether do I, except it seems a requirement! TypeA 15 TypeB 05 TypeC 00 TypeD 01 --sd Link to comment Share on other sites More sharing options...
comment Posted February 9, 2009 Share Posted February 9, 2009 And what's wrong with that? Link to comment Share on other sites More sharing options...
Søren Dyhr Posted February 9, 2009 Share Posted February 9, 2009 00 I do not think you by conditional formatting can fit in something if a field is empty - or can you? --sd Link to comment Share on other sites More sharing options...
comment Posted February 9, 2009 Share Posted February 9, 2009 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. Link to comment Share on other sites More sharing options...
Søren Dyhr Posted February 9, 2009 Share Posted February 9, 2009 Remove all the checks in you template as watch the number of empty lines ... just a tiny detail really! --sd Link to comment Share on other sites More sharing options...
comment Posted February 9, 2009 Share Posted February 9, 2009 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. Link to comment Share on other sites More sharing options...
Søren Dyhr Posted February 9, 2009 Share Posted February 9, 2009 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 Link to comment Share on other sites More sharing options...
comment Posted February 9, 2009 Share Posted February 9, 2009 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. Link to comment Share on other sites More sharing options...
bcooney Posted February 9, 2009 Share Posted February 9, 2009 "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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 5553 days old. Please don't post here. Open a new topic instead.
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 accountSign in
Already have an account? Sign in here.
Sign In Now