I've got a table in which I'd like to extract summary data for use in
a text string. The table has about 100,000 records and I sort it by
different criteria depending on what summaries I want to see -- area,
population, interval, etc. On the layout, I use sub-summary parts and
summary fields to see this data, but I'd like to plot the information
on a chart (not FM11's chart feature), which requires constructing a
string with concatenated data. For example, I might construct a bar
chart comparing total population by state, so that the x-axis would
show Alabama, Alaska, Arizona, etc., while the y-axis showed (in
millions) 4.7, .7, 6.6, etc.

If I had one record for each state, I'd just sort alphabetically and
loop through each one, adding the state name to a global text field
for my x-axis and the population number to a global text field for my
y-axis, with each data point separated by a delimiter. But how do I
do the same with sorted summary information? Do I have to loop
through all 100,000 records, comparing state names and adding
population counts? Seems like there should be a faster method, given
that the summary info is sitting right there in the sub-summary parts
already calculated. I know I could do it by creating table
occurrences and aggregates, but since there are so many different ways
I'll be sorting the data, I'm hoping to avoid mass TO creation. (I
should note that the above example using state populations is a
simplification of the actual dataset.)

Any thoughts?

Thanks for your help.

Best,
-Jahn

On 2010-09-05 15:44:49 -0700, jahn <jahnbigbooty (AT) yahoo (DOT) com> said:

GetSummary() is the function you want. You specify the data field and
the field to sort by, called the break field, and it will get the
summary data for you.

See the Help for the complete syntax.

Often, using GetSummary is much faster than sorting and displaying a
summary field on a layout. For this reason, some developers use global
fields to display summary reports, setting them in a script with the
data rather than waiting for large record counts to sort.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

Hi, Lynn -

Thanks for the quick response. I should have mentioned that I have
the GetSummary fields already defined in the table, but I didn't think
it was germane because I'm not sure how I would transfer this info to
the concatenated string. If I have 100,000 records with 50 different
states, the population estimates will be there in a field defined as
GetSummary(Population; State), but there will still be about 2,000
extraneous records for every 1 record that I want the data from. Do I
have to do something to isolate 50 distinct records based on the value
in State and then loop through to extract data to my globals? I feel
like there must be a quicker way to do it. What do you think?

Thanks again for your help.

Best,
-J.