Importing Data... Help needed

[swalow4m]

Hi Dears

 

I am a dentist and I am wiring on the clinics database using filemaker pro advanced.

we used to use excel files for that, now i am facing a huge obstacle in importing those data.

 

the thing is that, I have 2 separate tables, one for patient data, the other for the visit by visit record. Now in the layout that we should be using, we will be adding a new record for each patient and within that i included a portal tool in witch i added the fields from the other table, because we will be adding new records of progress every time the patient comes.

the issue is that when i import the data from the excel file i am unable to add the daily records for each patient separately in side the portal tool. and when i import it in to the progress table it doesn't update the fields in the corresponding patient record!!

 

I am not sure if i explained it well, but i am sure that you can understand what i am talking about and that you can help me.

 

looking foreword to hearing from you

regards 

[LaRetta]

You need to perform two separate imports - one for the patient data while on layout based upon patients and one for the visits while on layout based upon visits (not the visits portal on Patients).  It would help to see your file (you can zip it and attach it).  Basically, both imports will need to hold the unique patient ID or name.  If you need to use patient name for this process, that is fine but once imported, you will want to switch to internal FM patient IDs.  Here are the basic steps:

 

1. Back up first before any process which manipulates your data.
2. in your Patient table, add a field called ID, number, which is auto-enter serial, increment by 1 starting at 1.
3. Import your Patient data into this table, including the patient name.
4. In your Visits table, add a field called ID which is auto-enter serial, increment by 1 starting at 1.
5. Also temporarily add a field for the patient name in visits (this will be removed later)
6. In visits, add a field called PatientID (number).  Do not establish an auto-enter on this field - you will insert the patient ID later.
7. Import your visit data including the patient name into this visits table.
8. In your graph, join the two tables by pointing at Patients::PatientName to Visits::PatientName and join using the =
9. Go to a layout based upon visits and show all visit records.
10. Place your cursor in the PatientID field in visits.
11. From your FM top menu, select Records > Replace Field Contents.  
12. Replace with calculated result.  In the dialog, select Patients from the upper popup and double-click the patient's new auto-enter serial ID.
13. Select OK  and then REPLACE.

At this point, you will want to go to your graph and change the relationship from patient name to be based as:

 

Patients::ID = Visits::PatientID

 

Now delete the patient name from the visits table.  Create a value list called Patients which is

1. File > Manage > Value Lists and New and specify 'use values from field'
2. In the upper left pop-up, select Patients table and below, select the ID.
3. In the upper right pop-up, also select the patient name.
4. Below, specify 'include all values' and only show values from second field.
5. Now go to your visits layout and find the PatientID field.  Select it and in Inspector, Data tab, open 'control stye' which currently shows 'edit' and select your new Patients value list.  
6. Also in Inspector, Data tab, go to the bottom, change format from 'custom' to 'as entered'.  This resolves a current bug where otherwise the pop-up would display numbers instead of the patient's name.
7. Use this popup to assign Patients to new visit records you will be making.

There are many nice folks here willing to help you if you get stuck.  You can zip and post your file.  Create an empty clone (File > Save A Copy As > Clone no records to keep your data private.  I hope I didn't miss anything - I just woke up and haven't had coffee yet - but that's why you should back up first. :-)

Oh!  And Welcome to FMForums!!!