Analysis Layout for all Records in Table

[bcolburn]

I have a main table (Patients) with many records that I want to represent and quantify data from in an analysis layout using interactive charts

(method here: http://filemakertoday.com/filemaker-resources/filemaker-techniques-examples/item/216-creating-dynamic-summary-charts-filemaker-11)

What is the best way to do this? I was planning on creating a new table (Study_Analysis) and using a Cartesian join between the two primary keys. Is this appropriate? This seems like a simple question, but my only other idea was to add my new fields to Patients and display those records on the analysis layout, which might mean running calculations on all Patient records when I enter the layout (I think...?). Can anyone offer some guidance?

[comment]

I think it's best to chart (or summarize - same thing, really) the data you have where you have it.

my only other idea was to add my new fields to Patients

What new fields do you require?

[bcolburn]

I think I understand your logic, but if I were to place database-wide analyses on the patient data layout, wouldn't I be generating an identical set of graphs/charts/etc. on each of my ump-teen records?

My new fields would be global fields to determine graph/chart output based on user interactivity. For example, pick the demographics of interest, pick the case types of interest, etc. and pop out a chart via the method I linked to above). Other than that, I was planning on adding a global count field for each record to create interactive counts for the portals I create (to show how many records are populating the graphs/charts users select for). To summarize. You use global fields to set parameters and a chart displays the data while a portal shows and summarizes the matching records.

I was also planning on creating one or two additional portals for administrative purposes, but those would only require a global search and global count field each to work as I would like.

[comment]

if I were to place database-wide analyses on the patient data layout, wouldn't I be generating an identical set of graphs/charts/etc. on each of my ump-teen records?

Only if you place the chart in the body part AND use a layout in List view.

My new fields would be global fields

Global fields can be in any table (unless you want to use them for a relationship).

I was planning on adding a global count field for each record

There is no such thing as a global field for each record (and a "global count field" is also something I am not familiar with).

[bcolburn]

Gotcha. The first bit was what I needed to know. The global fields are indeed designed to be used for relationships. Also, I just meant placing it in the portal row for each record - I know how the global field will work. Thanks for your help!

[bcolburn]

Sorry - I just wanna check in on this. I realize it won't be populating a graph visually for every record, but if I display database summary data on the patient layout, it will still have to populate that information every time I swich patients, no? I was kind of thinking of a completely separate layout that I could go to only when necessary in order to prevent calculations/filtering/etc from slowing down the load time per page. I only need access to the summary information once a week, but I use the patient layouts multiple times daily. Isn't putting the summary data on that layout just unnecessarily burdening the system?

[comment]

You should definitely have dedicated layouts for your reports. Any unstored calculation or summary field placed on a layout needs to be re-calculated on each window refresh.

Let me just stress the point that a table can have any number of layouts.

[bcolburn]

Right. But if I create a separate layout for my report that draws from the patients table, won't it have to create just as many records as I have Patients, all showing identical data? I was under the impression that this was not the most parsimonious solution and that somehow I should be able to summarize the database in a layout that only needs 1 record, since it only has one unique data set to display?

I realize that just because I have 2,000 Patients doesn't mean I have to show all of those records at once, but it seems strange that I should display my data in a way that requires more than one record. This was where I initially got the idea for the Cartesian join with a table that only has a single record and is joined to the Patients pk.

[comment]

if I create a separate layout for my report that draws from the patients table, won't it have to create just as many records as I have Patients

No - you already have 2,000 (or any other number of) records in that table. Now you only need to find the ones you want to include in your report/chart.

I should be able to summarize the database in a layout that only needs 1 record, since it only has one unique data set to display?

Well, that's just it: a data set is made out of many records. Although it is possible to use a related set, the straightforward reporting method is to summarize the found set.

[bcolburn]

I'm confused. If I use a layout and show records from Patients, there will be 2,000 (or however many records I have in that table) records in the report layout too. If it's the same table, it doesn't matter if it's a different layout, I thought. It should still have the same number of records.

And I am including all of the records... that's my question. I'm trying to summarize demographic information for ALL records on file.

Is it possible to see an example file?

[comment]

Here's an idea: create a new layout of the Patients table, and put only a summary field - say Count of [PatientID] - on that layout. Set the layout to Form view.

Now show all records and switch to the new layout.

[bcolburn]

I hate you. And also love you. Thanks. I am sometimes reticent to try every solution offered since not every one works out, but your point is well taken. Thanks

However, I do have a question. If I am on a layout of Patients and want to create a portal for viewing Patient records, how can I do that? Create a second TO and relate the two pk's?

[comment]

But there is one more point for you to take... .

Add two new parts to your layout:

• a sub-summary when sorted by patient's gender (assuming you have such field);

• a trailing grand sub-summary

Place an instance of the summary field in each one of these parts, and delete the body part. Now switch the layout to List view and sort the records by gender.

[bcolburn]

I have a global field where you select the demographic characteristic (e.g. sex, race) you want to evaluate, chart, and display in a portal that is filtered by relationship to the interactive global field. I was planning on placing the count field in a copy of that portal only as big as the field itself in order to create an interactive count. If I placed this construct in each of the sub-summaries and added a script so that when I select a demographic, it also sorts the records by that demographic, I would then be able to further dissect the initial interactive count, no? So if I select "sex", the script sorts records by sex and the sub-summary report for sex is displayed with the portal count field dissecting portal records by male and female?

If this is true, I would need a separate sub-summary for each demographic trait, no? If I am sorting by multiple characteristics (e.g. demographics as well as assigned study case type) is there a way to put a sub-summary in a sub-summary?

[comment]

So if I select "sex", the script sorts records by sex and the sub-summary report for sex is displayed with the portal count field dissecting portal records by male and female?

You are confusing me: I am talking about summarizing the found set - there are no portals here.

You need a sub-summary part for each "level", and the order matters - for example:

Male: 55

• Russian 25

• Italian 10

• Greek 20

Female: 45

• Russian 20

• Italian 15

• Greek 10

versus:

Russian: 45

• Male 25

• Female 20

Italian: 25

• Male 10

• Female 15

Greek: 30

• Male 20

• Female 10

[bcolburn]

I understand. Is there an equally convenient trick for getting percentages on each of these levels without referencing the specific demographic characteristics in a unique field?

I may have found my own answer: a summary field in each sub-summary part like this?

FractionofTotal (g.Count_PatientID) when sorted by (g.Select_DemographicCriteria)

[comment]

There is a summary field of type Fraction of Total - if that's what you mean.