Need help cleaning up messy data

[RWX]

Hello everyone.  I'm trying to migrate contact data from a really messy custom Goldmine solution (nightmare) to a new FileMaker system.  The data is pretty messed up.  Very little consistency.  Multiple fields storing the same type of information.  Sometimes it's in one field, sometimes another.  Sometimes there's a single value in a field I need to process, sometimes multiple values.  Sometimes I need to evaluate several fields before deciding what to do.

 

I'm migrating about 7,000 organization/company records and about 40,000 related contact records.  I'm doing my best to whittle down the number of records that will need to be reviewed manually after import.  We are, moving forward, going to enforce strict record typing.  Each organization record has a primary record type, i.e.: Manufacturer or Educational.  Some primary types have a secondary type, i.e.: Educational has High School, Middle School, etc.  All of these types are based on value lists.  The user cannot enter their own types anymore.  Why they were allowed to in the first place is beyond me, but I didn't write that system...

 

So here's my question.  I need to look at several fields and pick out multiple values and apply them to my new type field.  Using Educational as an example, I might see the following in the old system's KEY3 field:

 

HS, MS, ES

 

In order to clean up and import this data into my new system, I imported the old Goldmine data (tab-delimited export) and allowed FileMaker to create new tables.  I have created relationships between these tables as needed and added some calculated fields to generate clean data for import to the new system (via a series of scripts that will evaluate the old data, copy what is needed, create records in the new system's tables, and insert the copied data into the correct places).

 

One of these fields is the cOrganizationSubType field.  It looks at places where old type data might be stored (fields KEY1, KEY2, KEY3, KEY4), evaluates those fields via a lengthy Case ( ) statement, and generates a new SINGLE sub-type that will be imported.  I have no problem generating one sub-type, but how to do I evaluate and generate multiple values (separated by carriage return)?  Logically I understand what needs to happen.  I need to test each condition and append, but I've never done this before and I don't know where to start.

 

I need to look at KEY3 and if I find HS, add High School AND if I find MS, add Middle School AND if I find ES, add Elementary School.  Seems like some kind of recursive calculation within the Case ( ) statement?  In most cases, there's only one type or sub-type to worry about.  Schools are challenging because one school can be all of the above, or simply a single sub-type.

 

As always, all advice and references are greatly appreciated.  Thank you!

[comment]

I might see the following in the old system's KEY3 field:

 

HS, MS, ES

but how to do I evaluate and generate multiple values (separated by carriage return)?

I need to look at KEY3 and if I find HS, add High School AND if I find MS, add Middle School AND if I find ES, add Elementary School.

 

Specifically in your example, you might use =

Substitute ( KEY3 ;
[ "HS" ; "High School" ] ;
[ "MS" ; "Middle School" ] ;
[ "ES" ; "Elementary School" ] ;
[ ", " ; ¶ ]
)

Note the assumption about consistent entry of comma followed by a space.

[RWX]

Thanks very much for the reply.  The problem with your solution is that I need to selectively process this field.  There are certain values in the field that I want to skip.  Also, there is no consistency in terms of how values were entered.  Some are comma separated, some dash separated, some with a space, etc.  I was hoping to look for certain key terms (i.e.: ES, MS, HS), move those to the new field, and ignore the rest.  If those values aren't found, I flag the record for manual review.

 

I figured there might be a way to do this with a calc/function.  I can do it with a pre-import script, and I think that's what I will do, but I'm also curious to learn how to approach this type of situation via a calc/function.  I figured there might be a way to use Pattercount ( ) to test for certain terms and, if found, apply the new values to the new calculated Type field that will be imported.  Maybe I'm making this more complicate than it needs to be...

 

Thanks again for the response and advice!

[comment]

The problem with your solution is that I need to selectively process this field.

 

Actually, the problem is with your question, since the constraints are not well defined.

 

 

I was hoping to look for certain key terms (i.e.: ES, MS, HS),

 

That's possible, but again: in some circumstances such test could easily miscalculate - for example, by counting "less" as an occurrence of the pattern "ES". If that's unlikely, you could try =

List (
Case ( PatternCount ( KEY3 ; "HS" ) ; "High School" ) ;
Case ( PatternCount ( KEY3 ; "MS" ) ; "Middle School" ) ;
Case ( PatternCount ( KEY3 ; "ES" ) ; "Elementary School" )
)

 If those values aren't found, I flag the record for manual review.

 

That's not really necessary, as you can easily perform a find for such records.

[Fenton]

Comment's calcs were very near to success. The first one uses a special property of the Substitute () function, which is that it is "case sensitive"; which you really need. Hopefully people seldom used "HS", etc., for other things. If so, then you'd need to do more, to get the "other" out. His calc would change "HS" to "High School", which is usable text.

 

The PatternCount allows you to see whether you got a match, which it can if you ran the above to get usable text. 

 

Case () can return only the value(s) you want.

 

The List () function returns each of the above, and does not return duplicate values (at least it seems not to). 

 

Let (

key3_fix = Substitute ( KEY3 ;

[ "HS" ; "High School" ] ;

[ "MS" ; "Middle School" ] ;

[ "ES" ; "Elementary School" ] 

 );

 

List (

Case ( PatternCount ( KEY3_fix ; "high school" ); "High School" );

Case ( PatternCount ( KEY3_fix ; "Middle School" ); "Middle School");

Case ( PatternCount ( KEY3_fix ; "Elementary School" ); "Elementary School" )

 )

)

 

Returns (if present):

High School

Middle School

Elementary School

 

( It would return that above is they'd already typed one in correctly, i.e., "High School".)

 

P.S. Notice that I made "high school" lower case; so you can see that PatternCount is not case-sensitive (as the above works).

 

[ It seems to work. I cannot be sure. For one thing, I cannot be sure about many things.]