i'm fairly new to filemaker but i know a bit about programming. currently i'm working on an application containing 3 tables [patient, test, results] and are related as such

[patient <- patientid -> test, test <- testid -> results].

tests and results tables are in a single portal while patient tb is on the main layout. everything works fine when i create a new record. as i enter the info into the patient table it created a unique id for it. then when i go to the tests tb and start to enter data, [in portal on the same layout] it takes the patient-id from patient table and adds it to the test table and generates its own unique test id as the same time. so far so good.

now this is where i need your expert help. in test table i have a field about testypes [showing on portal as a listbox for the user to select the testtype -> clinic visit, ECG, Echo, Hearing etc]. what i want is when user selects any test-option other than clinic visit from the listbox, it [through calculation field] create a new record into the results table, add test-id to it along with test-type and a pre-calculated result-due-date. so with one or nested calculations i want to create and populate a record into results table containing testid, test-type and expected-result-due-date.

so can anyone help me how to do it or direct me towards a reference material to get around this little problem of mine.

for now it works only if i enter data into record-table manually through the portal [same portal with test-table]. it will take the test-id and test-type after creating a new record. but i want it to be done as soon as the user selects a test-type from the listbox which belong to tests table.

thanx in advance for your help.

I don't quite follow your explanation. What is the difference between testtype and testid? IOW, what does a records in the test table represent?

testtype = text field and will contain one of the selected values from a listbox. values are clinic visit, ECG, Echo, Hearing etc

test-id = auto-generated by the system, unique and indexed to identify a unique test record related to a patient.

patient table is linked to test-table through patient-id and test-table is linked to results-table through test-id.

record in test-table contains

test-id [auto-generated],

patient-id [pk of patient table, fk in test-tb],

test-type [echo/hearing/ECO/GP3][text] ,

test-book-date [date],

test-related-comments [text].

Edited May 2, 200817 yr by Guest

I still don't get what the test table represents in real life. Does one test have multiple results for a single patient?

yes a patient can have multiple tests and similary for each test there will be a result aswell [one test cannot have more than one result and for a new test of same patient we'll create a new test record accordingly] and that will be stored in result-table. this is where i'm having problem. i want to add some info from tests-table into results table but b4 that i want the calculation function/field to create a new record in results-table, and add test-id, test-type-selected and test-result-due date all through calculation-field linked to test-type which is part of test-table.

Edited May 2, 200817 yr by Guest

Calculations cannot create records, so it cannot work quite the way you have described.

However, I think your structure can be simplified, and this will solve your problem as well. I see no reason to have separate tables for tests and results. Your Results table should be linked directly to the Patients table (by PatientID), and have roughly these fields:

ResultID (auto-entered serial)

PatientID

Date

TestType (select from a value list)

Result

more details

Alternatively, you could add a table for TestTypes, and link it to Results by TestTypeID (this would replace the TestType field in Results). You should do it this way if the list of test types can be modified by users, or if there are more details to be recorded about each test type.

Either way, you can create a new record in Results by selecting the TestType (or TestTypeID) directly in the last portal row (the portal being from Patients into Results).

thanx for your input and help. much appreciated.