I'd merge those 4 tables (or table occurrences?) down to just two. Put experiment and control records in one table and your data from both control and experiment in the same table. Add a field in the combined experiment/control table that identifies each record as "patient" or "control".
That should result in a simpler structure for charting purposes as well as for other reports.
This is the problem when simplifying databases. In the original database the experiment links to a patient table which includes alot of information that we don't need for the controls. Sorry for not pointing this out.
Thank you for your reply. Will think about maybe trying to reduce the number of tables. Cytotoxicity and CytotoxicityExp are table occurrences.
Just because you have extra fields for the patient that aren't needed for the controls doesn't mean you can't put them all in the same table. You can just leave the extra fields blank for the control records.
The other option is to define a patient detail table for the extra fields and set up a one to one link to the other "experiment table". Control records would not have such a related record, but experiment records would.
Thank you for you reply.
Still have a hard time getting the charts to work I'm afraid.
Describe what you have so far. You should be able to create a chart like you describe on a layout based on your cytotoxicity table.
I manage to get the Patients data on the chart (CytotoxicityExp::Hundred). And I manage to get the first control on the chart (Cytotoxicity:Hundred), but I don't know how to get the rest of the controls on there. I've been playing around with the "totals"-function but with out luck (or skills...)
I managed to put together a chart with three number series, but setting it up was pretty ugly. Charting in filemaker is a new feature and everyone is still figuring out what it can and can't do.
Unless someone knows a way that I don't, getting parallel data series into a filemaker chart requires putting each point in a different field of the same record for each series.
You can possibly set up a special table for this with relational links to connect to the correct patient and control records. Here's a demo file that I used to figure this out: http://www.4shared.com/file/nhBnZL5x/ThreeSeriesLineChart.html
I have worked a lot with the new charts in FileMaker and my personal conclusion is unless you are trying to chart something very basic, you're *probably* not going to be able to get what you want. Certainly nowhere near what you can do with Excel :(
Most of the charts I have built into my various solutions now revolve around a Web Viewer / Google Charts combo. The API is fairly easy to learn thanks to their live chart playground, but it still takes some time to get a hold of. I have not personally worked with line charts, only stacked/clustered bars and pies so far, so I'm not sure if it will work for you. I don't think it will handle dynamic variable controls. Also keep in mind that the chart is really just a long URL you are sending to Google as an HTTP request, which are temporarily logged according to their FAQ. Depending on the information you are charting and its sensitivity and/or security concerns, this may not be an option for you at all.
Big thank to both of you for your helpful answers.
I don't have the possibility to look at your demo here from home, but will look at it monday. Again, appreciate your effort a lot.
For me, there are two problems: Seems like FileMaker doesn't support XY-scattered plots like the one in the Excelfile.
Second problem is that I don't know how to adress the data. The Experiment layout has a portal to control, so that each experiment can have multiple controls. However, I can only reach the first record of Controls. For example Cytotoxicity::Hundred returns the value from the first control, but how do I do to get the rest of them? I learnt databases with Access and there you could simply use SQL but in FileMaker?
Wimmachine; I've been thinking about Google Charts also, I think that might be a good option. If I select only to send the values, and no data that can reveal the identities of the patient or controls, I don't see a problem for this. Will go through this with my boss first. Google Chart support XY-plotting I suppose?
Yes GCharts support XY plotting. Here's a chart similar to what you posted, using modified values.
There are some limitations to GCharts as you can see, you cannot do vertical-aligned axis labels or rounded lines. I built this in only a few minutes using the new Wizard, and then took it to the Live Chart Playground to change the placement of the legend. Building this chart into a Web Viewer URL and tying the data to fields in your database is another matter, but once you get the chart set up how you like it isn't that bad.
Thank you Wimmachine.
I managed to get it to work with Google Charts and a script to create the URL out of the data in the database. Working good actually, quicker than I first thought. The downside is that you have to be connected to internet but it's not a big issue.
I appreciate your help and if you send me a message with your paypal name I'll gladly transfer the promised $5 :)
Haha no need for payment! This is a community after all, we're all here to help each other solve problems and learn new things along the way. Glad to hear the charts worked out for ya.