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.
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...
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.
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.
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.
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.
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.
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....
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.