3 Replies Latest reply on Apr 23, 2014 1:53 PM by philmodjunk

    Repeating an auto-entered serial number in records

    NsaA

      Title

      Repeating an auto-entered serial number in records

      Post

           I am creating a medical database. I have a Patients Table and a Visits Table. I will enter many visits on separate dates for each patient. The two tables are related by the Patient ID using an “equal to” function.

           The Visits table with several fields is my main layout and holds the 3-4 fields from the Patient Table too. Records are created by Visit, not by Patient. So every time I type in a Patient ID (lets say 121), in my main layout (Visits table), if a visit record has been previously entered for Patient 121 in the database, then the few remaining fields from the Patient Table like Date of Birth, Sex, Ethnicity fill up automatically. Then, I continue filling data unique to that visit like the date of visit, chief complaint, Name of attending, medication etc.

           1)       Based on previous advice on this forum (thanks Phil!), I want to create an auto-entered serial number for Patient ID (using filemaker pro 11) I already have a Visit ID being created by an auto-entered serial number, validated always, field never empty, always unique. If PatientID is auto –entered, I will have difficulties in my main layout. Eg- I may want to type 121 as an ID several times to enter a new visit record and to have certain fields fill automatically.

           2)       The Patients table (which exists but which I do not use) shows several blank records with only the Patient ID filled in. I assume this is because of the multiple records associated with one Patient ID in the main layout- Visits table. Is there any way around this? It does not really get in my way because when I convert my main layout (Visits table) to Excel, all the data appears properly. This is the ultimate purpose of my database- to use collected data it for statistical work in Excel.

        • 1. Re: Repeating an auto-entered serial number in records
          philmodjunk
               

                    I already have a Visit ID being created by an auto-entered serial number, validated always, field never empty, always unique. If PatientID is auto –entered, I will have difficulties in my main layout. Eg- I may want to type 121 as an ID several times to enter a new visit record and to have certain fields fill automatically.

               PatientID should be defined as an auto-entered serial number ONLY in the patients table. In the Visits table, it should be a number field with no auto-entered serial number setting. There are several options that can be used to link a new Visits record to an existing Patients record. All, in one way or another enter a matching ID number in the Visits::PatientID field to link it to the correct Patients record. The simplest to implement--though often not the best option once you have a large list of values, is a drop down list or popup menu of names and ID numbers with the PatientID specified as the first field of the value list.

               

                    The Patients table (which exists but which I do not use)

               Actually, you DO use the Patients table, though you may not be using the Patients layout.

               

                    I assume this is because of the multiple records associated with one Patient ID in the main layout-

               This is probably not the case.

               The trick is to set up your database so that you can easily determine whether the new Visit record is for a new or existing patient. When you wanted to type in the PatientID, was that a value from another system? or a Value you arbitrarily assigned each new patient?

          • 2. Re: Repeating an auto-entered serial number in records
            NsaA

                 Thanks Phil.

                 Actually I have an excel list with serial numbers and patient names. Unfortunately I cannot enter names in the database (privacy protocol). So I use that serial number as a PatientID. Lets say ID 1 for Patient A, ID 2 for Patient B etc. this list increases in size everyday. these patients are entered in my database. as the patients are entered, we also record in an excel sheet that Patient A's particular visit has been entered.

                 So when I have a new visit from a patient, I know the name of the patient and the serial number from the Excel file. If I type in 1, some data like Sex, Race will appear and if I type in 3...no data will appear. so I have to fill in sex, race etc.

                 On your advice, I considered having an automated Patient ID entered. This would mean that we record the automated ID next to the patient name in the Excel sheet. Do you think I can avoid creating an automated ID altogether and continue to use the serial number as was being done? I mean, know that you know the full background? Can you predict any problems I may face?

            • 3. Re: Repeating an auto-entered serial number in records
              philmodjunk

                   You can add a second field for this externally produced ID and use it to determine if a visit is from a patient that is already in your database or not. This allows you to stick with auto-entered serial numbers as your patient ID number and yet use the number from your spread sheet to either find an existing record in Patients or start a new record in Patients with that value.