Groups | Search | Server Info | Login | Register

Groups > comp.databases.filemaker > #511

Re: How to build an array with data from a single table?

Show all headers | View raw

On Dec 7, 6:49 pm, Lynn Allen <l...@NOT-semiotics.com> wrote:
> On 2011-12-07 14:59:43 -0800, lansingoogle <l...@jillo.com> said:
>
>
> > I have a client that wants an excel-style report composed of data from
> > a single table.  In this case, the records contain "city" data and
> > creation dates, and they want a report that will have cities
> > populating the 'rows', and records-per-month populating the columns
> > (columns would be Jan, Feb, March, etc.).  So... San Francisco: 4
> > records in Jan, 6 records in Feb, 1 record in Mar etc.
>
> > My plan was to base the layout on the primary table (with a sub-
> > summary part sorted by city), but I'm stymied by how to get the
> > records-per-month data into the vertical columns in any kind of an
> > elegant manner (without too many subsidiary tables, added fields,
> > etc.).
>
> > I'm sure this is a common challenge, and that there's a 'best-
> > practice' way of achieving it.  My hope is that some kind expert out
> > there will see this and enlighten me!!
>
> > The client is using FM9, with FM 9 Server (all windows xp).
>
> > Many thanks in advance.
>
> I don't think you can use Virtual Lists in FM9, as the GetValue
> function didn't exist then. Do you think you can get them to upgrade,
> at least the client machines if not the server?  (if my recall is
> faulty and GetValue exists, go for it).
>
> Virtual lists make such a report relatively easy. You make records in a
> reporting table, each one with a serial number. A field for each column
> reads an indexed value from a variable with a calc such as
> GetValue($$column1array, Serial).
>
> Then you build the variables by looping through the records and setting
> the values you want into the lines of the variables. So if your column
> 1 is city names, then you'd loop through the records and the variable
> looks like this:
>
> Chicago
> Detroit
> Los Angeles
> Phoenix
>
> So the calc field in column1, record #2 reads "Detroit."
>
> Then you repeat this for each monthly value (column) you want. Column2,
> March 2011, for example might read:
>
> 134
> 148
>
> 789
>
> Am I making any sense? Virtual lists are a very powerful tool. I've
> done the above kind of cross-tab report getting summary data by doing
> finds using the values in the first variable, then setting the found
> set totals into the other variables as needed. Think of each variable
> as a column in an array.
>
> Because all this is taking place in the memory space of the file, it
> goes really really fast.  UNLESS you have very large data sets. At more
> than 2000 records, creating the arrays by setting them to themselves
> plus the new line of data starts to get slow.
>
> If you need subsummaries in the reporting table, create them there, to
> total the columns. They'll be fast too.
> --
> Lynn Allen
> --www.semiotics.com
> Member FBA
> FM 10 Certified Developer


Thanks, Lynn - you introduced me to a whole world of possibilities
here.  GetValue is indeed included in v.9, and I'm busily looking into
all the information I can find on Virtual Lists.  As a self-taught,
part-time FM developer, who doesn't participate in the big scary world
of major FM development, I was totally unaware of this technique, and
I've spent the last day trying to wrap my head around the
implications.  Actually, it captures the essence of why I enjoy
Filemaker, and the community it engenders: flexibility, innovation,
and a creativity that continually amazes me (not to mention a
generous, unselfish willingness on the part of the experts to share
their knowledge).

Again, Thanks.

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Find similar

Thread

How to build an array with data from a single table? lansingoogle <ls@jillo.com> - 2011-12-07 14:59 -0800
  Re: How to build an array with data from a single table? clkaufmann@gmx.ch (Christoph Kaufmann) - 2011-12-08 07:32 +0100
    Re: How to build an array with data from a single table? lansingoogle <ls@jillo.com> - 2011-12-09 16:29 -0800
  Re: How to build an array with data from a single table? lansingoogle <ls@jillo.com> - 2011-12-09 17:29 -0800

csiph-web