9 Replies Latest reply on Mar 2, 2012 1:56 PM by philmodjunk

    Giving numeric ID to imported excel file when repeated names on that database

    edgarroca

      Title

      Giving numeric ID to imported excel file when repeated names on that database

      Post

      Imported excell file into database but many fields have data of same patients and I would love to give a number to each and every single patient in that database.  Is there any way to do that..?

      Would appreciate your help.  I guess I need a mentor..big time!

        • 1. Re: Giving numeric ID to imported excel file when repeated names on that database
          philmodjunk

          This one can get really messy. The problem is that names aren't reliable for uniquely identifying people. People can have the same name, People change their names and when humans type in a name, it isn't always entered consistently.

          Take my first name. In some records you'll find my name entered as Phil. In others, you'll find Phillip.

          All of this will complicate identifying groups of records as belonging to the same patient or not. Is their any other field such as a driver's license or social security number present that you can use to find all records for the same patient? (even those aren't perfect.)

          Otherwise, your best efforts to assign ID numbers to each unique patient solely from this imported data may never happen perfectly.

          • 2. Re: Giving numeric ID to imported excel file when repeated names on that database
            edgarroca

            Actually yes, each patient has an unique number identifiers, which is very long and includes a letter on it.  We need just to add a number starting from 1 that tells us how many independent record we have....or do a count of how many Unique identifiers we have.  Now when I do a total count will tell me the total amount of encounters we have instead of the total amount of patients.  Help needed...

            • 3. Re: Giving numeric ID to imported excel file when repeated names on that database
              philmodjunk

              Ok, that makes life MUCH simpler.

              I'd define a table for patients with one record for each patient. Define an auto-entered serial number field in this table. Include the existing patient identifier field and use field options to specify Unique value, validate always on the validations tab.

              Now, import your data into this table with the auto-enter options enabled. FileMaker will import the data but filters out records with duplicate patient identifiers and will generate a serial number for each record successfully imported.

              If you need to link this new serial number back to the original data, Import the data into a second table where you have all the fields you need and no Unique Values setting to limit the input. Use a relationship based on the existiing patient identifier field to link these two tables.

              • 4. Re: Giving numeric ID to imported excel file when repeated names on that database
                edgarroca

                Well I created a field just like you said but is going to create a number to every single visit. Imagine thaat one patient has multiple visit, each visit is recognized as one record.  I one to count the amount of patients....not every single visit...do you get it?!?

                 

                check date   code  ID       Last Name   First Name  Date of Service    original paid.    adjust. paid     adjust. difference     Patient Count

                2/2/2011     FB     345       Doe             John            2/3/2011         23.34                   -22.00          1.34                       1

                3/4/2011     J1      444      Doe             John             3/3/2011           111                    -222           -111                        2

                4/3/2011     FB     578       Jay             Peter             4/4/2011           52                       54             -2                           3

                 

                 

                 

                As you can see, after I created the last field, patient count and imported my excel file it created a number(patient count) foe evry single visit and not for every single patient. That's what I need.

                • 5. Re: Giving numeric ID to imported excel file when repeated names on that database
                  philmodjunk

                  I ran a quick test after I saw your post. Yes, the auto-enter serial number is assigned before the duplicates are dropped out. That foils the auto-entered serial number if you want a sequence without gaps. (You don't need a field at all just to count the patients, BTW).

                  But you can use Replace field Contents to assign a serial number after the import to give each record a serial number and that should work for you if gaps in the serial number sequence are something you have to avoid.

                  • 6. Re: Giving numeric ID to imported excel file when repeated names on that database
                    edgarroca

                    I mean...could you do a remote desktop section with me...like Teamviewer..and show me what you mean. It would mean a lot for me.....thanks.  I live in Portland, Oregon.

                    • 7. Re: Giving numeric ID to imported excel file when repeated names on that database
                      philmodjunk

                      It's actually quite simple. You seem to have the import working to import one record for every patient. To assign serial numbers after import, do this:

                      Go to the layout for this table.

                      Put the cursor in the Serial number field

                      Select Replace Field Contents from the Records menu

                      Select the serial numbers option and click OK.

                      • 8. Re: Giving numeric ID to imported excel file when repeated names on that database
                        edgarroca

                        Actually if you see well there are several records per patient...if you see the example above...John Doe has two records........when I do the count it counts everything as independent record...and I need  just one record for John....

                        • 9. Re: Giving numeric ID to imported excel file when repeated names on that database
                          philmodjunk

                          There should only be one record for John in this table. The field validation I described earlier prevents the additional records for this patient from importing. If you import data that contains 5 records for "john", the first imports and the remaining 4 are blocked from importing.

                          This is a separate table from the table where you'd import all the data and is just used to generate one record per patient so that you can get you sequential serial numbers assigned.