Home Forums Discussions General Discussion Importing into Partially Populated AC Database

We are currently migrating to AmazingCharts from a paper environment. Patient demographic information is currently stored in another vendor's PM database. We have partially loaded the AmazingCharts database manually, and are continuing to add new and existing patients into the new system as they are seen for the "first time" using AmazingCharts. Once I figure out how to create a comma-delimited file from the output file from the old system, I would like to import the remainder of the "non-existing" demographic information into AmazingCharts. Would the following work?

1) Import comma-delimited file data into AmazingCharts, where the comma-delimited file data may include some records that have already been manually input into AmazingCharts. I'm assuming the demographic record data table key includes some combination of the patient name and DOB (+/- SSN, etc.). If I try to import a record in which a key already exists, will that record be dropped, will another "duplicate" record be added with a newly assigned patient ID, or will the import function stop at that point (until all conflicts are resolved)?

2) If #1 (above) cannot be done, can or should I merge the existing AmazingCharts data into Excel with the other vendor's exported data, delete any common records and then import the remainder of the "new" records into AmazingCharts.

In any case, I plan on saving the MDB file prior to any import attempt. I'm guessing that AmazingCharts uses the assigned patient ID to link between the demographic and chart databases. If so, I cannot just export the demographics MDB contents, (save the MDB file as a backup name), delete the original MDB file (data) and then import a combination of the AmazingCharts demo data/vendor data into an "empty" AmazingCharts demographic database file, right? If I did this, the patient ID (autonumber) would no longer be consistent between the newly assigned patient ID's and the pre-existing chart database entries.

I hope my request for information is not too complicated to understand. If you have a documented step-by-step process for importing into a partially populated AmazingCharts database, I would appreciate a response, an e-mail or URL address to a location that describes that process.

Thanks,
John

we worked at this for a few weeks before finally converting. I have a very strong computer sciences background, and there were a lot of problems. I am not blaming AC for this. It is a common problem in changing data from one format to another when they have not been specifically designed and fully tested to work together, and with all the data cleaning filters built in to trap hidden formatting and control chararcters, etc.

You will get duplicate records if you load patients a second time, and if you have the same ssn, dob and name in each, it will cause all sorts of problems later on.

We did not start using AC until we loaded the bulk of our active patients in a few batches, and then practice ran the system for a few days after each addition to the patient database to look for data/glitches. Some of the data is not editable once loaded in AC so it had to be right the first time. We also used a compatable spreadsheet format for transitioning the data into AC, so that the fields could be manually reviewed, sorted on different fields and checked many times over. At the time we had about 3500 charts to import and despite all the checking and testing, we did save many many hours of time from manual entry. We managed to get in about 3200-3300 of the charts this way, but could not get rid of unknown errors in the data of the remaining ones. These have been added manually as they came up again, after creating the working ac database as above.

With your set up as described, probably the best way is to use the Excel spreadsheet approach and delete any duplicate records in Excel before attempting an upload into ac. We did this all a while ago, but I recall that the excel file needed to be saved as a single page, csv or excel 4 type format, and needed a blank line at the top of the spreadsheet in order to get the first real record to be included. I dont know if subsequent versions of AC have done anything to the import data function from that time, but the descriptions for how to do it have stayed basically the same on a quick look now.

If your staff and support personnel are not up to the task, it would probably be worth the expense to have the AC staff provide the service to your office directly. THe web site says they offer these services at least.


good luck!

Thank you for your reply. I too have an extensive CS background, an would think that AC personnel should be able to at least provide us with a key structure for the table(s) to which we are importing. The database should ensure that multiple records with identical keys are not created. If multiple records with the same patient name, DOB, etc. are allowed to be created, I question as to whether or not appropriate key structures and/or indices are being utilized within the AC database. I have noticed that when I type in a patient's last name, or especially first name, on the main AC screen, filtering takes some time to complete. I wonder if the last name and/or first name fields are set up as indices, as opposed to non-indexed data fields. Now, we are working on a networked version of AC, so that may be contributing to the lag, but this filtering operation seems to take a significantly longer time to perform than other DB operations. We likely have fewer than 1000 patient records in our database, and I am concerned as to how much slower similar operations will take when further records are added.

I have worked on a few projects where data conversion were major parts of the overall project. I understand the difficultly of moving from one database to another; yet, importing data is critical when transitioning to a new database. I would hope that AC would have a process in place to allow at least a pseudo-clean transition. I'm sure they have it. I guess I'm just not clear as to why it is provided as an additional service (at an additional cost) when I would guess that most of those getting ready to use AC have at least some form of practice management software that's capable of exporting demographic data into an output file.

In any case, I appreciate your feedback. I'll try to keep you posted on how we transition.

Identical keys do not occur, but you can have identical persons on all paramenters. the ac added record code is not editable. the unique person is based on date of birth, first name and last name, and then a chart number is assigned. Many people in one's practice could have duplication of these three fields, and then the only way to know you are charting into the right one is to open the demo record and look.

Depending on what PM you have, you might look into the X-link interface. AC3 has just added this feature and is claiming to be able to interface to several popular PMs via the x-link middleware process. This is supposed to enable demographics, billing, etc to flow back and forth.

THe network (hardwired and 100 mhz+) does not seem to have much effect on loading records when there are a few or several thousand. Slower networks will show the effect a bit more, but not daunting. Remote attempts, unless you use some version of remote terminal services, will drive you to excessive swearing and smashing things. and you will have plenty of time to do damage.

AC runs on an MS access db, and there is not 'true' field or record locking, nor client/server relational/transaction-based db. I think (but not certain at all) pointers and indexes to the different databases are loaded across the network into the client pc running ac locally, and then individual records are pulled in to work on as you use them. more than one user can pull in the same record and whoever updates something last gets the 'gold', other than the encounter notes. In clinical charting, 'passing' the file one one step in the process to the next via the email feature is needed to not close a contact record and seal its fate.

If I understand you correctly, the key is essentially a combination of the first/last name, DOB and assigned chart number (or maybe even just the newly assigned chart number (autonumber)). I understand how, depending on the size of your practice, there is a chance that you may come across two people with the same first and last names and DOB's, yet I would think that more information (e.g., SSN) could be included in a unique key structure, or index, that would guarantee uniqueness so that 2 separate records will not be created for a single patient. In any case, the system is built the way it's built. I can still work with it, without major headaches.

Thanks for the insight into the X-link interface. This may be worthwhile investigating.

I understand that the application is not a transaction-based nor a true client/server application. Maybe the developers will consider making a true client/server-based version (e.g., using SQL Server), which should reduce significant network overhead and decrease response times.

I'm not trying to be hyper-critical of the product. Generally, I really like its functionality and ease of use, especially at the very reasonable price. I guess I just would like to have better response times when it comes to certain operations (e.g., patient list, transitioning from message view to schedule view).

Thanks again.

I exported the data to a comma delimited file, sent it to AC and they imported it for me. After this is done, whenever you open the chart for the first time you have to open the demographics page and click the save button.

We imported all the data from PMS- as you see in demographics page, there are some key elements which are a must. AC website says they will help you in this (My feeling is that ther is not a separate charge).
But when we imported I believe there were some patients who did not have an assigned gender/sex.
So yesterday when I went to illustration- it would not load the pictures for this lady. Well of course, because there was no gender assigned to that patient. Once I said it was a female patient, it loaded the illustrations. Just make sure you have gender assigned to patient in demographics.

Regards.