Charting Multi-Line Fields

[jlev]

Hello everyone,

I have a question about charting that I've been researching for several weeks but haven't been able to answer. I'll start by summarizing what I /have/ gotten to work, and then will ask about where I'd like to take things:

What /is/ working: I have a two tables in a database that tracks participants in research studys. The first table is called Study Summary, and it records all of the information about a particular research study. One of the fields in Study Summary is Eligibility Criteria, which is a multi-line field in which all of the criteria for study participants are listed. The second table is called Participants. Each record in the Participants table is a person who expressed interest in taking part in a study. Participants are related by ID number to the ID number of the Study in which they're interested.

Some participants don't match their study's eligibility criteria. For these cases, there's a field in the Participant table called "Why Ruled Out," with a drop-down list of the Study's eligibility criteria (the drop-down list uses a value list that takes values from the related Study's Eligibility Criteria field). Currently, one eligibility criterion can be chosen from the list for this field. The number of similar responses are counted using a calculation field in the Participant table where the calculation is "GetSummary (CountOfParticipantSerialIDs ; Why Ruled Out )." This allows there to be a button for each Study Summary record that Goes To Related (Participant) Records and charts them, with the calculation field mentioned above supplying the Y-values and "Why Ruled Out" supplying the X-values ("Show data points for groups of records when sorted" is checked). This works great.

What I /can't/ figure out, though, is how to make a chart if the drop-down "Why Ruled Out" field becomes a checkbox field, where multiple reasons could be chosen. Because each Study Summary's Eligibility Criteria has a different number of values, I need to figure out a way for FileMaker to recognize all of the seperate values of "Why Ruled Out," which would be a multi-line field, in a set of Participant records, collapse the values, and then count how many occurances of each value are found.

To frame it in an example, if a Study has three eligibility criteria, "A," "B," and "C," and there are three Participants, the first of whom was ruled out for A, the second of whom was ruled out for A and B, and the third of whom was ruled out for B and C, I need a way for FileMaker to chart A, B, and C as the X-values, and display y-values of 1 for A, 2 for B, and 1 for C. The closest I've gotten so far has been to get the chart to show the X-values as being A, AB, and BC, rather than A, B, and C.

If anyone would be willing to share any tips or point me in the direction of some relevant reading, I would be very grateful. I've spent three weeks searching FileMaker forums and help articles, but haven't been able to figure this out yet.

Thank you very much!

-J

FileMaker Pro 11, Windows XP

[comment]

The way you are set up now, this is not possible - and not because of any charting limitation. You also cannot produce a report showing the same numbers.

The real problem here is that you have a many-to-many relationship without a join table. Filemakers allows this - but there ARE limitations to this method.

You need another table where each reason is a separate record, related to the participant. Then produce the report/chart from this table.

[jlev]

Comment,

Thanks very much. That makes sense!

J