While running some simple code to Total a few columns, unexpectedly some of the summary records did not have a total, just a blank cell or space. I doubled the code in my procedure, or duplicated the code so that fields would be totaled twice, and the totals appeared as expected. Has anyone else had a problem with fleeting totals?
From time to time I have run into this problem where a summary record does not display the total after running the Total statement. I can illustrate this problem. Perhaps there is someone out there who can download the zipped file and see the problem that I am seeing. More often than not, I am the cause of a problem, but so far I have not figured out what I am doing wrong with this one. Thank you in advance for any help. File: Dropbox - File Deleted
I downloaded your database and ran the procedure that had groupup and total statements in it. It generated the correct summary records every time as you can see in the screenshot below. Are there any extra steps necessary to reproduce this problem?
Jim, the problem is the missing number 60 in the first summary in the field Units.
James, I noticed all your number fields are set up as floating numbers. When I change the non-decimal fields to integer, the summaries seem to work and show all sums.
Thanks guys for reviewing this. This is important to since we perform calculations on the totals on Summary Level 1. My usual workaround is to run the Total statement twice on each totaled column. That seems to clear things up. It would be nice not to have to do that.
Aha, James put the description of the problem into the name of the procedure! That’s a new one, I didn’t notice it there.
That didn’t work for me - there was just a different summary record that had a missing value.
There seems to be some kind of corruption in this database. I was able to duplicate this problem with a very minimal program.
startdatabasechange "ALLRECORDS"
field UnitType
groupup
field Units
Total
(Note: I added the startdatabasechange line so that I could Undo after running the code, so I could quickly try it over and over. It’s not needed though.)
I made a brand new database with the same fields by hand, and typed in the data (only the two fields in question). It worked without a hitch. So I think there is something wrong with the structure of your database. I would suggest exporting the database to a text file, then create a new database by importing the text. If you have a lot of procedures and/or forms you can use the View Organizer to drag them over relatively quickly.
I don’t think there is anything wrong with the structure of the database as this can be replicated in a brand new database. Still having some issues here with random missing totals in Summary Records. I have had success by following these two rules: Rule #1 as suggested by KJM. Make sure that the summary record fields are non-decimal integer fields. Rule #2 is to never perform calculations on summary records. First delete all detail data, and then perform any necessary calculations on what where summary records. I have floating point fields with two digits to the right of the decimal point that depend on calculations of the summary totals. I can get the desired results by following the above two rules.
If you can tell me the steps that will replicate this problem in a brand new database, I’m all ears. I was not able to replicate the problem in a new database – even when used exported data from the database you sent me to populate a new database.
I have come across the same problem.
I have a programme asking 3 columns to be totalled after grouping them by a different column, and nothing happens, then just remain blank. If I do the programming using the ‘record’ function, it creates the same code that I entered manually. It works while I am doing the recording, but the code does nothing. If I pause the routine, and manually total the columns, that happens perfectly. I’ve tried changing the names of the columns, which were initially numbers, to text names, and that hasn’t helped. I tried exporting the database as text and then creating a new database, and the code performs exactly the same way (doesn’t do the totals). I don’t exactly understand the conversation above about this problem - I’m working with numbers, so I can’t really change to integers. I’ve tried writing total twice in the code, and that doesn’t help.
field «2»
groupup
field «tempcons»
total
total
field «temphosp»
total
total
field «temptele»
total
total
The worst of it is that I was doing this thinking that there must be a more elegant way of getting the same result, but that this was easy and functional. I didn’t think I should disturb you all about how to approach it when there is so much happening with X.2. I’m importing csv data to analyse and transfer figures to another database. Although I can recognise summary lines in the data, panorama doesn’t because it hasn’t created them. I’m trying to do some totals within totals, and transfer the results to the summary line. To do this, I’ve created 3x3 temporary fields. 3 of the fields are to nominate the records to be included in each of the sub analyses, 3 are to transfer the data for sub analysis, which I then total (or not, which is my problem), before collapsing to this summary level, morphing these results into the 3rd set of fields, expanding to raw data, propagating them up, so that the numbers appear in the actual (but unrecognised by panorama) summary records, ready to be exported to the other database. Not very elegant, I know, but should have worked if only I could get the information to total. I’ve done this sort of thing with arraytotals before, but because I’m working with several groups of data (multiple clients have data that needs totalling), I don’t know how to do it.
Happy to hear any suggestions you have, but in the meantime letting you all know that there must be a problem with the total code somewhere, because obviously a few of us have stumbled upon it for some reason…
Have you already tried the “Summary & Analyse” tool from the toolbar? It is perfect for grouping, summarizing, and creating cross tabs.
The code that you have posted is not going to be doing what you might think it should do.
There is no need to use the Total command twice in a row without moving to a different field. The 2nd Total will just repeat again what the first Total had just done without any change to the database.
I would generally suggest that field names not be a number. This is not a rule but a generic guide to clarity and to stay away from problems.
When you say that there are Summary lines in the data but that Panorama hasn’t created them, this sounds like a recipe for disaster. It is best to only refer to records that Panorama has created as Summary records as this will be the expectation of other coders. Summary records are very special records that are temporarily created in response to commands such as Total, Average, etc. If you have what you know to be records that have within the totals, averages, etc, of other records in the database, I would suggest that you step back a bit and think of a bit of redesign of the db. It will cause you much grief to have records of this type mixed in with other records that are not ‘summary’ records.
Thankyou. The double totals was an effort to put into practice what the discussion prior to my post had suggested might help the problem. Neither total does anything!
I agree with you entirely about the difficulty of non summary ’summary’ records. However, this database is just a transitional processing tool for data exported from another completely unrelated programme, so that is what I am stuck with. Once I have formulated the data, I transfer the results into a normal panorama database. The transitional data is discarded, ready for the next week.
Don & Michele Dunlop
dmdunlop@gmail.com
It is not clear to me if you were able to group your data successfully or not (to create summaries). If not, have a look in PanoramaX > Preferences… > General and check “Allow grouping on numeric fields”.
Yes. I have grouped the data. The ’total’ function is just not performing properly in the programme.
I have experienced the same issue. I have a procedure that creates a series of reports by using a loop. Each time through the loop, records are selected, two fields are totaled, and a report is sent to a file use PrintoPDF. Invariably, some of the reports will be missing the Total row.
I just tried repeating the Field [fieldname] Total statements, and the total row was present in all 20 reports. Hopefully, that will work every time.
I’ve also experienced missing totals. When using a loop to total a series of > 30 numeric fields to see whether they contained values, one total was usually missed – it was as though the loop would sometimes repeat before a total was completed.
So I switched to using aggregate( in the loop, which was always reliable and much faster.
Good idea on using the aggregate function to get the totals. That’s what I am going to do.