Jump to content

How to relate this tables?


loscamos

This topic is 3089 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

visit.png

patient.png

Link to comment
Share on other sites

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 by madman411
  • Like 2
Link to comment
Share on other sites

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.

Schermata 2015-09-17 alle 08.17.33.png

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 by loscamos
Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ]

 

 

 

Link to comment
Share on other sites

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)

Schermata_2015-09-18_alle_12.50.13.thumb

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

Schermata_2015-09-18_alle_12.50.21.thumb

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

Schermata_2015-09-18_alle_12.50.46.thumb

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

Schermata_2015-09-18_alle_12.50.55.thumb

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

This is how relations looks like

Schermata_2015-09-18_alle_12.51.18.thumb

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

Schermata_2015-09-18_alle_12.51.37.thumb

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

Database Pazienti 2.fmp12

Edited by loscamos
Link to comment
Share on other sites

This topic is 3089 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.