automatic calculations

[Atefe]

Hello FM Experts! 

I have started to develope a FM database that keeps the data of patients of a hospital and different kinds of tests that they should do regularly.
for easy understanding, I have attached some pictures. 
In one table should be defined for each test, which patients have been examined. (plz see patients and tests pic).
In another hand, different combinations of "done or not-done tests", make different type of Lists (list num 1, list num 2,.....). Therefore I should create a seperate table for keeping these infos.  (plz see test and lists pic). 
Finally I need a table ( layout) somehow as a combination of 2 previous tables.   (plz see connection pic). 
In this table for each patient,  should be automatically defined whether this patient has done these tests or not. (with help of patients and tests table)
After that according to the combination of the tests that this paitient has or has not done, should the list number automatically be identified .(with help of  test and lists table ).

Does anyone have a idea, how can I do these 2 automatic process with help of calculations? Thanks in advance!

PS. the real tests and lists and patients are so much more that what I have written as an example! :))
Best regards,
Atefe

[comment]

Do all patients have to do all tests? If not, you will need a table to keep track which tests each patient needs to do (and there would be a separate record for each patient/test) pair.

The other thing that's unclear is this:

30 minutes ago, Atefe said:

different kinds of tests that they should do regularly.

If they have to do them regularly, then how you can tell if they have done them or not? The word regularly implies a schedule - and that could get much more complicated than it already is.

[Atefe]

2 minutes ago, comment said:

Do all patients have to do all tests? If not, you will need a table to keep track which tests each patient needs to do (and there would be a separate record for each patient/test) pair.

The other thing that's unclear is this:

If they have to do them regularly, then how you can tell if they have done them or not? The word regularly implies a schedule - and that could get much more complicated than it already is.

Thank you comment for your answer,
Actually you are right! I think I should delete the word "regularly", it is confusing...just ignore that
No, patients do not have to do all tests. I understad what you said, but actually we have a excell sheet (the same as "patients and tests pic" I attached) and it shows for each test, which patients have been examined and I thought maybe it is possible to do somehow a "reverse search" and find that which tests have been done by each patient!

You mean it is not possible?

[comment]

It's not so much a question of what's possible, but rather what's feasible and what's advisable. Filemaker is not a spreadsheet - and if you approach it with a spreadsheet frame-of-mind, you will soon become frustrated.

From what you have told us, it seems you only need two tables: Patients and Tests, related by PatientID (in other words, a one-to-many relationship, whereby one patient has many tests). You will also need a value list of TestTypes (which technically should be another table - but since it doesn't seem like you have anything to record about a test type other than its name, a value list should do).

In the Tests table, other than PatientID and TestType, you will also need a field to record the fact that the test has actually been performed. This could be a date field, for example, or even a number field with 1 entered into it when a test is done (although a date field is preferable for keeping a trail).

That would be about all that's required in terms of data structure.

In terms of display, you can produce a report of the Tests table, summarized by TestType, to show a list similar to your 1st picture. A form view of the Patients table, with a portal to Tests, will look very similar to your 3rd picture.

Producing a tabular display like the one shown in your 2nd picture is not easy. In fact, it's not possible to have such display, unless the number of columns (i.e. test types) is fixed. However, you can very easily produce a report of tests, summarized by patient, showing the same information in vertical direction.

I am not sure what any of this has to do with "automatic calculations" - there's nothing to calculate here.

 

Edited May 15, 2016 by comment