How to relate this tables?

[loscamos]

Hello, I started using Filemaker some days ago and I can't find a solution for what I what to do. I have to tables. One with patients and one for visits. As you can see from the images the patient table has some button (visit 1-2-3-4). What I would like to do is this: I create a record with patient 001, then i click on visit 1 and this create a record on table visit that must be related to patient 1. The same thing for patient 2. When I click on visit 1 on patient 2 I want a record on visit table related to patient 2. Also the button has to show the record so when I click on visit 1 I can add all the information. Can someone please help me out. I'm studying on a course but I can't find a solution. Thanks

[madman411]

You need to have an ID for each patient record and each visit record created must be related by that same ID (patient::id = visit::patient_id).

To script your buttons to create a new related visit record you would need to set a variable to the patient ID, freeze the window, go to the visit layout, create a new record, set the patient_id field to the variable and return to original layout.

Why are you limiting the system to only track up to nine visits? I think a better solution would be to have visits displayed in a portal and just use one button to add a visit - an unlimited amount of visits. You can then edit the visit record within the portal or use a pop-over.

Edited September 17, 2015 by madman411

[loscamos]

The reason why I want button related to visit is because I need to migrate an old database made by someone else. 

The old databse was built like that:

Record 1:

All patient information (Name, Birth date, Address, etc...)
All visits (Visit 1 exam 1, Visit 1 exam 2, Visit 1 exam 3  - Visit 2 exam 1, Visit 2 exam 2, Visit 2 exam 3, Visit 3 exam 1, Visit 3 exam 2, Visit 3 exam 3, etc...)

Record 1:

All patient information (Name, Birth date, Address, etc...)
All visits (Visit 1 exam 1, Visit 1 exam 2, Visit 1 exam 3  - Visit 2 exam 1, Visit 2 exam 2, Visit 2 exam 3, Visit 3 exam 1, Visit 3 exam 2, Visit 3 exam 3, etc...)

This is how the old database look.

My new database is based on 2 tables. Patient and visit. Patient table has all the patient information and visit table has all the exam.

The problem is that I can not import the old database data because in the import window I have to relate the old field to the new field. So I only would be able to relate the first visit to the new field not all the other visits because I have them separated in different records.

-- FIELD IMPORT WINDOW --

Visit 1 exam 1 (old database)  --> Visit 1 exam 1 (in new patient table)

Visit 1 exam 2 (old database)  --> Visit 1 exam 2 (in new patient table)

Visit 2 exam 1 --> I do not have a visit 2 exam 1 field in my new table so I would have to import all the visit 1 record and than import again all the visit 2 record assigning them as I did for visit 1. The problem is that the database has 8000 patients and 10 visits each. This way the database will be 80.000 visits. And another problem would be how to relate them to each patient.

I hope I explained myself clearly. Thanks

Edited September 17, 2015 by loscamos

[comment]

This is how the old database look.

That's not helpful at all: I cannot see anything.

 

The problem is that I can not import the old database data because in the import window I have to relate the old field to the new field. So I only would be able to relate the first visit to the new field not all the other visits because I have them separated in different records.

-- FIELD IMPORT WINDOW --

Visit 1 exam 1 (old database)  --> Visit 1 exam 1 (in new patient table)

Visit 1 exam 2 (old database)  --> Visit 1 exam 2 (in new patient table)

Visit 2 exam 1 --> I do not have a visit 2 exam 1 field in my new table so I would have to import all the visit 1 record and than import again all the visit 2 record assigning them as I did for visit 1. The problem is that the database has 8000 patients and 10 visits each. This way the database will be 80.000 visits.

I believe you need to import the visits 10 times (each time importing the fields from Visit N) - and you need to import them into the Visits table, not into the Patients table. And one of the fields imported needs to be a unique PatientID.

 

[loscamos]

Ok. I will import my data 10 times but I got another question. I'm going to create a button one for new visit and one for Manage visits.

Patient 1 will have New visit and Manage visit

When I click to new visit I want to create a record in visit table related to the patient 1

When I click to Manage visit I want to see al the visits related to patient 1

i have no idea on how to do this. Could you please help me? I need a script but I don't know how to write it.

[Steve Martino]

Here's a simple DB to look at, maybe it will offer some insight

 

 

 

PatientVisit.zip

Edited September 17, 2015 by Steve Martino
attached file incorrectly

[comment]

When I click to Manage visit I want to see al the visits related to patient 1

That should be very easy, using the Go to Related Record[] script step.

 

When I click to new visit I want to create a record in visit table related to the patient 1

Try a script along the lines of:

Set Variable [ $patientID; Value:Patients::PatientID ]
Go to Layout [ “Visits” ]
New Record/Request
Set Field [ Visits::PatientID; $patientID ]

 

 

 

[loscamos]

Thanks everyone for the help. I'm getting closer but I still need some help. (In the bracket you can read the english translation)

This is how the actual database looks like_

Pazienti (patients)

At the top you can see
Nuovo Paziente (new patient)
Nuova visita (new visit)
Gestisci visite (manage visits)

This is how Visits (visite) looks:

You can see

ID Visita (Visit ID) which is the Visite:_pkID
ID Paziente (Patient ID) which should take the data from Pazienti:_pkID and it is the Pazienti:_fkID

When I click on Nuovo Paziente (new patient) in patient table this show up. An ID is generated and a field take the name and the surname from the Nome and Cognome field.
The ID in the patient table is _pkID

When I click on Nuova visita (new visit) this show up

Only the date show up but no Pazienti:_pkID and no Visite:_pkID

This is how relations looks like

And this is the script in Nuova visita button (New visit)

Attached you can find the database too if you want to have a look

Database Pazienti 2.fmp12

Edited September 18, 2015 by loscamos

[comment]

You need to set the foreign key field to the variable, not the primary key field to the field in Patients (which is not available until you establish the relationship by populating the foreign key field).

Edited September 18, 2015 by comment

[loscamos]

I'm sorry but you mean on the script related to Nuova visita (add a new visit)? How do I do this?

Edited September 18, 2015 by loscamos

[comment]

Your script shows:

Set Field [ Visits::_pkID; Patients::_pkID ]

This is wrong in both the field being set and the value.  It needs to be:

Set Field [ Visits::_fkID; $pkID ]

 

[loscamos]

Got it. Now it seems to work! Thanks. 

The only thing that does not work right now is manage visit. The script is Go to related record [From table: "Visit" ; with the current format :<Current format>]

Did I write it wrong?

[comment]

with the current format :<Current format>]

I think you mean layout, not format. You must select a layout of the Visits table. You cannot show records from Visits using the current layout, which is a layout of Patients.

[loscamos]

I guessed the translation but I get this

[comment]

You must select a layout of the Visits table. You cannot show records from Visits using the current layout, which is a layout of Patients.

[Steve Martino]

Did you look at my sample file.  It has the simplified version of what you need.  The script per Comment, the relationship, the layouts.

[bruceR]

Here is a modified version with the suggested changes.

Database PazientiMOD.fmp12

Edited September 18, 2015 by BruceR

[loscamos]

Thanks everyone for the help! You are great. @Steve Martino yes I took a loot at your database and I took some from it but I wasn't able to get the manage visit. But it was helpful. @comment Thanks for your patience and sorry but for me was the first time using filemaker. @BruceR You have all my love! The history field is awesome. Thanks