Importing email text without duplicaiting data

[Steveo the Devo]

Hi everyone,

First of all I must pass on my thanks to sbg2 who nutted out the majority of my queries regarding importing email text in this thread.

I am now able to import lots of repeated text entries via his script however I want to set up a script that does not import text data that already exists as a record (i.e. the info is repeated in the email text).

I have tried to do this using the find script step without any luck and was thinking there must be an easy way to query the database.

Please take a look at the file attached and I'm sure you'll understand what I mean. If you look at the example text import strings, you will see one patient's data is repeated. I don't want to create a duplicate record of this. The MRN (Medical Reference Number) is the only field that will need to be queried as it is always unique number.

Basically I want the database to know:

'If the MRN in the email text = an existing MRN in the database, don't continue the import script, move on to the next part of the email text'

[Edit: I have been unable to upload my zip file so please download it from here (right click 'save as' will probably work best).

Thanks in advance.

Steve

[Fenton]

Set the MRN into a global field first, commit records, check, via a self-relationship whether it already exists, then only create a new record if it doesn't. But increment the counter in either case.

Parse_v4.zip

[Steveo the Devo]

Absolutely brilliant! Thank you.

I must admit that getting my head around the self-join thing has been a little daunting for a newbie like me. I'm still not sure I understand it but I'll certainly use it (just hope I never have to debug the thing)!

I've just taken a trip to your website and availed myself of your tutorials - thanks for all the info, I have a lot to digest!

Edited June 26, 2006 by Guest

[Steveo the Devo]

How do I make this relational?

I have one more desperate plea for help in importing text. The issue I have is that this has to be a relational database rather than flat (as I first thought). The database is designed to track patient admissions in a hospital. One patient can have multiple admissions over the span of time (a lot of patients are discharged and then 'bounce back' within a few weeks). Hence, a relational design is warranted.

My database scheme is relatively simple:

1. Patient data table:

Medical Reference Number (MRN): Unique to each patient

Patient details: such as name, date of birth etc

These details are entered as a once off and a script already exists in the example file to import them from the email text (thanks to fenton and sbg2).

2. Patient admission table:

Admission date: This is a unique date for each patient depending when they were admitted

Other admission details: Doctors name (AMO - admitting medical officer), diet codes, presenting problem (DRG).

I have used MRN to join the tables however I assume that the answer will involve merging the MRN with admission date to produce a unique ID for each patient admission? I tried to duplicate the self-join example fenton supplied above in order to check if a specific admission for a patient (and relevant details) already existed in the database... but failed dismally

I want to import patient details AND admission data from the same email text file - the text file contains both patient data and admission data. I am having trouble writing a script that creates new admission details for patients that do (or don't) have existing details in the patient data file (ie the MRN already exists or they have had previous admission). Obviously, I don't want to duplicate admission or patient data when importing.

Any help you can provide is (as always) greatly appreciated. The linked file has example email text (including one patient with 2 different admissions) and the script supplied by sbg2 and fenton. The email parsing (text location) for all fields is included in a separate script. I'm not sure I have the relationships or fields set up appropriately for the end script result.

Pt_Data_v2.zip (9kb)

[Steveo the Devo]

I acknowledge this is probably a complex question and will involve a specific script designed for this particular application (rather than a generalised 'this is how you do it' answer).

This is probably the key script that will determine whether Filemaker can be used as a solution. So, I figure I might as well show my appreciation...

If you can help me out with this problem I will happily pay you (anyone) $40 US [via paypal - obviously you will need an account]. I hope this doesn't break the forum rules? I also hope it enough to entice someone!? I really am desperate to get a handle on this. PM me your account details or post a response - first solution = first paid.

Thanks once again.

[Fenton]

I'm a little too tired to take you up on your offer. But a short answer is that you can have compound relationships in FileMaker. You can add the date also. If you have a time, add that too (2 visits in 1 day possible?).

It is also possible, as you say, to combine the MRN with the date (time),* to create a "concatenated" key. It can be used in some situations where a compound relationship cannot; such as in Import, with the Matching records option.

But a compound relationship including the date, and a Loop to check each record, omitting duplicates before importing, would work.

About the other data, I don't know. If there's some in one, and some in another, the best you can do is fill in the empties; and possibly combine the rest. Is it possible that there would be 2 "duplicate" entries, but with some different data? Not a lot of fun.

*Pay attention to what you get; use the same formula on both sides. It is preferable to explicitly convert the date via GetAsText or, my preference, GetAsNumber. The original date must either be a FileMaker-acceptible date, or converted to one. Never make assumptions about "dates," especially those from other systems.

Edited June 30, 2006 by Guest

[David McQueen]

I am not on a machine where I can actually do anything right now ( PB 1400 won't do it). However:

1. Break this up into processing functions and database functions.Processing is your parsing. Database is then inserting parsed info into the appropriate tables.

2. Have a parsing table. It's only function in life is to hold the original information and accurately break it up into flat file fields for each email.

3. You have a unique identifier for the patient comming in the data if I understand your posts correctly. Set up a relationship between the parsing table and your client table. If that relationship is valid or if you have some sort of constant key in the client table so you can read if it is not empty, you will know you have a duplicate client and know not to move the client data.

4. I am not sure what is there for the individual admission data in your email text. If there is a unique identifier for each admission you can check in the same manner. If there is not, you must create one from the data you are presented. In either case, once you have that, you can check for duplicate admission records using another relation to the admissions table.

5. Moving the data then becomes fairly easy in each case. Using client as an example, pick up the Identifier ( copy or set a global) from the parsed information go to the client table and create a new record and populate the identifier field in the new record. Have a relationship created when this happens back to the parsing table. Go back to the parsing table and use set field commands to set the client data equal to the parsed data for each field.

6. Follow the same procedure in the admissions table to populate a new record there.

7. In both cases this only happens after it is determined that the record is not a duplicate.

8. The data in your parsing table remains as a record of original information received.

HTH

Dave McQueen