Sounds like a job for Virtual Lists - http://www.mightydata.com/blog/taming-the-virtual-list-part-i/
...and thanks again to Bruce Robertson for the technique...
"Did you ever notice that the first piece of luggage on the carousel never belongs to anyone?" --- Erma Bombeck
I'm curious. I'm reasonably familiar with using the Virtual List technique for crosstabs and typical list view reports, but ... can you elaborate a bit on how you would use it for a case like this, where you are merging data from multiple different tables with a variable number of rows into a single list? Thanks.
I must admit it was an idea that occured to me, without testing or validation, as I use variables in arrays and GetValue() and ValueCount() so much that it seemed like a logical fit.
I imagine I'd jump in and find I need a ringmaster of some sort to tame the variables but it sure "seems" feasible.
I realize now this creates an obligation of sorts to prove it. I'll be "right back".
"A successful man is one who makes more money than his wife can spend. A successful woman is one who can find such a man." --- Lana Turner
Hi Stephen and Mike,
Thank you so much for your responses.
I've had a quick skim through your link to Virtual Lists, and it kind of makes a bit of sense, but I'm afraid that is beyond my understanding at present. I was rather hoping that there would be an easy answer.
Any further 'detailed' help would be much appreciated.
If your tables are heirarchically arranged, and appropriately linked, you should be able to use a sub-sum report. I generally setup the report using a layout for one of the TOs of a table at the lower end of the heirarchy (so to speak). For example, I work with a system that has four tables, Clients, Calls, Topics, and Referrals. I keep foreign keys for each of the higher level tables in subsequent tables (e.g. Referrals have foreign keys for Client, Call, and Topic). I can subsum from Referrals and get a report that would look something like this:
You can subsum from higher levels using calculation fields to aggregate/total data from a lower level if that is what you want. That might look something like this (from a Topic TO layout):
Total Referrals = 3
Total Referrals =2
Hopefully this helps. If your tables aren't associated as described you will need something a little more involved and input from someone with a little more knowledge about pulling and aggregating data than myself.
You don't say how often this report needs to be complied or if one version of the reported data will suit everyone who will read it. You do indicate that your understanding and presumably your skills are still developing.
You might consider using a separate report file which would be a "catch and release" tool used only for constructing reports. That way you can leave your main system unburdened by extra calculations, relationships and scripts. You would do the appropriate searches in the pertinent tables and import then into the report file.
It has been my experience that any useful report will spawn a request for yet another useful report.
Mike: that is the original purpose and example of the technique.
The basic idea is that you have a collector script that accumulates the return-delimited data into a variable or global field.
The script has to be designed to collect the data in the correct order; or to parse various chunks of collected data into a final list in the correct order.
This report will need to be compiled many times, and separated by each sector.
I am relatively new, having taken over the running, maintaining and imminent upgrading of a rather messy unstructured database. I've been adding further little database files on an adhoc basis, and am now contemplating giving it a major overhaul. Many users are on FileMaker 9 but some have got FileMaker 11. We can't move up to 12 because some of the macs can't run that. We have about 100 users.
I've been using Crystal Reports from our accounting software, and getting quite pro on that.
Thanks. So, if I understand correctly, you end up with what - an array of the data you want in something like this form?
Team Member 2
Team Membert n
Area of opportunity 1
Area of opportunity 2
Area of opportunity n
Strategic Vision 1
Strategic Vision 2
Strategic Vision n
Month|Targeted events|Seminars & Conferences|Marketing Materials
Do you then have, say, 4 fields on your layout (because that's the maximum number of columns) and parse out the rows from there? Or am I missing the idea?