Report using single text/multiple selection fields to summarize on

[MxStang]

Hi all,

I'm a novice+ FM 8.5 Advanced user and I just became a victim of the "your Filemaker prototype is actually more dependable than the custom application we run part of our department on" syndrome

So, long story short, I'm stuck with the following scenario:

I have a daily medical consult table constructed with the following relevant fields: patient ID, date, doctorID, sex, ailments and treatments. Both ailments and treatments are one text field each that can contain multiple values through the use of a checkbox list in the interface layout. Now I have to make a report like this:

Date range | sex | doctor ID | ailment1 |...| ailmentN | Treatment1 |...| TreatmentM | TotalAilments | TotalTreatments

Right now I do it by brute force, with 2 intermediate tables and a lot of record-by-record analisis but I'm pretty sure there is a simpler, faster, more elegant way and I'm sure this is the place to find it.

Could you guys help me out with suggestions/ideas/tips to solve this?

Thanks in advance,

Gabriel.

[Vaughan]

It's a case of "can't get there from here". A field should only contain one piece of information.

Resist the temptation do use repeating fields to solve the problem.

The multiple-selection checkbox fields should be portals displaying related records. The exact data design "depends" on whether, for instance, each ailment has one (and only one) treatment, or whether ailments and treatments need to be separate entities.

[Vaughan]

Upon re-thinking, if possible use one table for both aliments and treatments, and a field to identify the "type" (ailment or treatment) and another field to identify the "description" (name of ailment or treatment). This would make generating the report *really* easy, but it needs to be worked through and considered carefully.

[comment]

I don't think one table for both ailments and treatments would be necessary, or even desirable. Presumably, there can be many treatments for one ailment, and many ailments for one patient. So a simple report can be produced from the Treatments table, with sub-summaries by Patient and Ailment.

It can get more complicated if Ailments has another child table, e.g. Symptoms.

[MxStang]

Thanks for your replies, I knew this decision would come back to haunt me.. darn!

Comment, in this implementation there is no relationship between ailments/treatments, it's just to keep a record of them and generate the report so it's a much simpler design (but you are making me rethink it looking forward nonetheless...)

I've created a very simple example of how this part of the application works and I think you can see better why I was "seduced" to use this one-line/multiple values approach: in the same layout I can have a history of previous consults of the patient with each of their associated ailments/treatments using just one simple portal.

I was thinking I should revisit this in order to get ready for the MySQL integration that's coming in the next revision of FM anyway, so all of your input will help me greatly in avoiding hitting my head on a wall too hard :o

OneLineMultiValueExample.zip

[comment]

I still think that the more atomized the data is, the easier it is to produce a meaningful report. Keep in mind that Filemaker doesn't do cross-tab reports (at least not natively), so I'd suggest you think of this in terms of sub-summaries.

there is no relationship between ailments/treatments

Alas, that is often the case...