Reporting from related tables

[Greg Hains]

Hi.

Im sure there is a simple solution for this, but I am unable to find the correct "section" in my reference material or terminology to get started with.

I have several tables - all related. The main table is one that contains a client ID and the related tables contain info such as progress notes and the like, and these can contain various amounts of information - from a couple of records (in the related table) single line to many records half a page of notes each.

My question is that I am unsure where to start with the layout of the reports when it comes to displaying this related data - without printing a whole page out per related entry.

Then, is it possible to have the report display entries from several related tables sorted by date order rather than date order of progres notes, then date order of {another related table} etc?.

Big ask I know - but if you can point me in the right direction with the right idea Im happy to research the rest.

Any help would be appreciated.

Cheers,

Greg

[Vaughan]

I create calculated fields in the main table that use the List() function to concatenate the related records into a field. It's not perfect and it has limitations, but it often is enough.

[Greg Hains]

Hi Vaughan,

Thanks for this, I'll give it a whirl and see how I go.

Cheers,

Greg

[LaRetta]

...from a couple of records (in the related table) single line to many records half a page of notes each.

That would be one ugly, unstored calculation in the main table; one that I wouldn't want in MY solution. If you use this approach, I would instead write the primary key to a single-record report table and create the calculation (and relationships) in this report table rather than bog my parent table with it. The record could be deleted when done.

Or you can gather into variables.

[Vaughan]

The calculated field technique is useful when there are more than one sets of related records that need to appear on the same layout.

[LaRetta]

Hi Vaughan!

I certainly understand what you are saying and I agree that smaller, simpler multiple relationships might work with calc in main table but not when the calculation would involve several relationships and multiple records with 'half-page text' fields. It would still use overhead particularly during schema changes and such. And it would only be used at the moment User wants a report so why burden the main file?

I suppose it's a matter of preference.

Edited September 3, 2010 by Guest
Fixed typo

[bruceR]

Use a virtual list and a collector script.

[LaRetta]

Personally I suggest a #2 gangling wrench set at three-quarter cusp.

[Vaughan]

... it would only be used at the moment User wants a report so why burden the main file?

Because it's a quick and simple solution, and it also makes it easy to export the related data from the main file in a way that the users may find intuitive.

[comment]

Yes, it is a simple solution. It is also limited to simple situations where you are dealing either with a single field or multiple fields with a constant height. If, however, you need a columnar layout with sliding, I don't see an alternative to importing the records from both child tables into a union table.

Im sure there is a simple solution for this

:P

[bruceR]

Except for the previously mentioned virtual list technique. No import required, completely flexible data aggregation, format however you want, sliding, etc.

Mentioned in many sessions in Devcon, see for instance COR001_4.fp7 demo file by Don Levan who extends it nicely.

Edited September 4, 2010 by Guest

[comment]

I don't see THAT much of a difference between the two, in the sense that you must have exactly one REAL record in the union table for each body occurrence. How you create/omit/import/delete those is not essential. The virtual list method, though harder to set up, has the advantage of enabling simultaneous reporting by different users.

[bruceR]

Typically a virtual list system has the utility records set up once, and you never delete records. If you find that you need to deal with a larger data set than previously set up, you do create additional records. The records are "light weight" because (in simplest form) they contain one stored field and the rest are unstored calcs.

[LaRetta]

YOu'all missed my point about the wrench ... why throw out phrases such as 'use virtual list and collector script' if you aren't going to provide a demo, a link to the concept or at least a sample script. You might as well be speaking in Greek to the person.

And pointing to a DevCon file is just as worthless and won't help the majority of people who might read this thread and who may want to use the information.

[comment]

Well, "I'all" didn't see the need to elaborate, because it's not something I would recommend in this case - at least not without hearing more details from the OP.

But anyone interested can search the forums and find several threads with demos included, for example:

http://fmforums.com/forum/showpost.php?post/360724/

OTOH, searching for "gangling wrench" finds this thread only...

[LaRetta]

Say what you will ... you will anyway ... but it is only polite to include a link if you are not going to explain a technique you recommend and it WAS recommended by someone on this thread. My example was ridiculous to make a point, something you seemed to miss.

[bruceR]

So you have concluded that direct, helpful, clear, respectful communication is likely to work better and we can expect different behavior in the future?

[LaRetta]

... and we can expect different behavior in the future?

From you? I sure hope so. As for me, I'm tired of being nailed every time I attempt to assist on this forum. I have not been rude to you or anyone; in fact, if I would have said that it was YOU instead of simply you'all, you'd have jumped on me for that as well and that is why I globally made the statement.

Pointing Greg (remember Greg?) to a DevCon file doesn't help AT ALL and how could it have? Simply, you didn't point to the DevCon file to help Greg - you said it to fluff your own feathers. And neither did your first response to Greg help of "Use a virtual list and a collector script" with no indication of what that meant. Instead of talking about how great you think you are, why not try helping the person who posts?

UPDATE: Now how about we get back to our true purpose for being here ... helping people who post? Remember them?

Edited September 4, 2010 by Guest
Added update

[Greg Hains]

Hi.

First of all I do want to thank all of you for your input. I didnt mean to stir anything up so please put down anything in your hand that may be sharp - befoire somebody gets hurt.

Vaughan's solution is a workaround to something I find surprising is not built into Filemaker, but I will work with what I've got - and check out that example file.

Many thanks to all who challenged the method and also contributed.

Cheers,

Greg

[Ocean West]

:backtotopic:

lets keep it on point folks - if you have some insight as to a technique that will provide assistance

go ahead and provide it be verbose and descriptive and provide references / examples / links.

There are many ways to accomplish a task and everyones mileage may vary.

Above all else - remember to treat each other respectfully, and don't be so quick to hit the submit button - season your words so that they are palatable to everyone.

[Greg Hains]

Hi.

Its me again - the accidental pot stirrer.

Further to the above job, I used the suggested terminology of "virtual list and collector script" being a global text field, and a script that puts all occurences of another field into this one field. The method in principle works fine, but now I come back to part of my original problem...

Sometimes this combined field maybe a few lines long and sometimes several pages. If I format the layout to be long enough to cater for the largest text content then it fits, but short reports waste several pages, and conversely, if the field size on the report layout is too short it doesnt fit.

Can I configure attributes of this report to be as large as the actual contents of the field at print/preview time?

Is this what "sliding" is all about?

Cheers,

Greg

Edited September 5, 2010 by Guest

[comment]

What you describe doesn't sound like the virtual list technique - more like what Vaughan suggested. In any case, are you familiar with sliding?

http://www.filemaker.com/11help/html/create_layout.9.62.html#1030089

[bruceR]

Hi.

Its me again - the accidental pot stirrer.

Further to the above job, I used the suggested terminology of "virtual list and collector script" being a global text field, and a script that puts all occurences of another field into this one field. The method in principle works fine, but now I come back to part of my original problem...

Sometimes this combined field maybe a few lines long and sometimes several pages. If I format the layout to be long enough to cater for the largest text content then it fits, but short reports waste several pages, and conversely, if the field size on the report layout is too short it doesnt fit.

Can I configure attributes of this report to be as large as the actual contents of the field at print/preview time?

Is this what "sliding" is all about?

Cheers,

Greg

That is not the virtual list method.

You used the terminology but did not actually use the technique.

There was a link provided that pointed to an example file, I suggest you look at that. I will also provide another example.

You are describing a problem the virtual list *solves*.

I agree that it needs better explanation, so let's start.

Basic concept

You create a utility table with two fields. A number field, let's call it N. And a calc field.

Let's call the calc field "DisplayVariable" and set the calc field formula to:

GetValue( $$array; N)

The calc should be set to "unstored", type text.

You create a set of records in this table. Say 100.

You populate N with number values 1-100.

Now you run a script that collects data and throws it into the global variable $$array.

Now a list layout based on this table will show paragraphs 1-N of the data that you have placed in $$array.

If you create a list view with field N not on the layout, and set up the DisplayVariable field to slide, then the page breaks work well and your page count is not limited by standard layout limits.

More in the next response, but see the additional example provided here.

basicvlist.zip

[bruceR]

Virtual List Continued.

When you start adapting this technique to your purposes, you generally find a maximum set of utility records, you create that many, you number them. You never need to delete them.

As indicated in the example scripts, if your collector script set $$array to data containing 57 values, then you enter find mode and do a find for N <=57. Again - see the example scripts.

I commonly also add a global field to hold the collected data, and set it to an auto-enter calc defined like this:

Let( $$array = self; self )

Now you have the advantage of being able to see the data - and change it if you want; because you can look at and edit the global field. The auto-enter calc automatically updates the variable.

[bruceR]

Regarding the value of mentioning other users of a technique, this is in fact directly relevant, and done in consideration of the person who has come here with questions.

When considering learning something new, there are several things to think about. These things include actually learning the technique; but also wondering beforehand whether it is likely to be worth the effort. Does it appear to solve my problem? Does it have any traction and credibility in the user and developer community?

[fmbiz.net]

Hi Greg,

I am not going to jump into the middle of the discussion that you have going on here, but I would like to address one point about your most recent question.

Since you are using FileMaker 11, try putting the output of your combined "List" into a global field ($$) and Insert it directly on the body of your layout. Experiment with this and it should solve your problem of undetermined page lengths.

Sliding options work well on Field objects on the layout but it cannot always get the behavior and control you need, especially when you need the text area to grow larger than originally defined.

Best Regards,

Charlie - FMBiz.net

[bruceR]

Wrong terminology. $$ is not a global field. Perhaps you mean a merge variable, where you put some value into $$var and then put <<$$var>> onto the layout. But this does nothing to solve the large text block page break problem.