create data table for lab values

[swf]

I am trying to create a laboratory table.

I have two databases.

One is patients including patient name, patientid etc.

The second is individual lab values. Each entry has patientid, date, test and result.

Example: Patient

John Smith id=100

Labs:

id-100 8/9/05 sodium 140

id-100 8/9/05 potassium 4

id-100 8/8/05 sodium 138

id 100 8/8/05 chloride 105

They are linked by id#

What I want to do is to make a table for each patient with columns of test and rows of dates

so the top would have sodium, potassium, chloride, etc

The rows would be today, today-1, today-2, etc

Ideally it would go back as far as you could scroll down.

Would appreciate suggestions. Thanks

[T-Square]

So you're saying you want the chemical as the column heading? If so, you've got a complicated problem.

If you could get by with a listing of all the tests sorted in some way, then it's a little easier. You create a portal that is based on a relationship from the patient to testresults table. The relationship can be set up to show all tests in reverse chronological order (by Date Descending), or sorted first by test type and then by date (By Type By date descending). Then put your data fields in the portal. This would result in:

Patient 100

----------------------------------

Test Date Result

----------------------------------

Chloride 08/08/05 105

Potassium 08/09/05 4

Sodium 08/09/05 140

Sodium 08/08/05 138

-- Not quite what you wanted, but maybe okay?

David

[swf]

I have been investigating further. What I want to do is a cross tab table and not easily accomplished in filemaker. Your suggestion for a portal is likely a better idea. I have successfully set up a portal show each patients lab values sorted by descending date. Now if I could add a filter to limit to one lab test or one group that would probably solve the problem for now.

I could categorize sodium, potassium as chemistry and hemoglobin, white count as blood count.

Then the filter would allow me to choose blood count or chemistry or all values. any ideas?