Lookup partial match....Dumb newbie needs help..

[Rtinat]

I am new to FM and any help is appreciated.

I am importing csv data. One of the fields (CPT CODE) imported is a 5 digit medical code ie. 71010, 70450,70360 etc. I have a separate file with about 500 codes(PROCEDURE DESC) that describes the procedure for each code. The records contain the code and description as follows:

"70450 Catscan of Head"

"71010 Xray of chest"

etc.

When new records are imported, only the 5 digit code is entered into the (CPT Code) field. What I want is to have FM do a lookup of each 5 digit CPT Code and have it match the appropriate description for that code and enter the result into a field (PROCEDURE DESC). How do I do this? Thanks

[cjaeger]

you will have to split the imported field into 2

code= Left(importcode,5)

code description = Middle(importcode,7, 9999999)

[Fitch]

You need to break up your PROCEDURE DESC field into two fields: CPT CODE, and PROCEDURE. You could do that with a script, but since this is probably a one-time deal, I'd Replace the CPT CODE field you just created with this calc:

LeftWords(PROCEDURE DESC, 1)

Then Replace the PROCEDURE field with:

Substitute (PROCEDURE DESC, CPT CODE & " ", "")

Now make a PROCEDURE field in the file you import into, and in the Options, tell it to Lookup based on a Relationship to the codes file, which is CPT CODE:; CPT CODE.

Hope that made sense.