Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Changing how names come over


Wingwalker

Recommended Posts

We need to import data from an Excel document that has both First and Last Names in the same field. Each name is on a separate line, but as I said, "John Doe" is in one field. My FileMaker solution has two separate fields of "FirstName" and "LastName". How do I write a script to put the names in their respective fields?

 

To further complicate things, some even have middle a initial. Is there a way to do all this?

 

Thanks for your help,

 

James

Link to comment
Share on other sites

Create two new calc fields to separate the names fields.

 

FirstName: Text Calc: LeftWords(NameField,1)

 

And

 

Last Name: Text Calc: RightWords(NamefIeld,1)

 

This will provide the first and last names in separate fields.

 

Be well . . .

 

Tom

Link to comment
Share on other sites

I'm not crazy about creating calculations for fields which will/should be used for regular data entry. There is ALREADY a first and last name field!! Why have extra calculation fields needlessly? James, you didn't indicate whether this is a one-time import operation or an on-going process but regardless, this may work better for you: Add one standard text field (used only for such imports) called FullName. Establish the following calculations on your standard FirstName and LastName fields. Then import your Excel FullName into FULLNAME. During the import, click 'Perform Auto Enter'.

 

Add an Auto-Enter by calculation (Do not replace existing values) to your FirstName field with:

 

If( WordCount(FullName) = 2 ; LeftWords(FullName ; 1))

 

Add an Auto-Enter by calculation (Do not replace existing values) to your LastName field with:

 

If( WordCount(FullName) = 2 ; RightWords(FullName ; 1))

 

Why didn't I account for anything other than a 2-word name? This is NOT an exact science, James. What if you have Dr. David Nelson? Or Mrs. J. M. Smith? Or Raymond Davis, PhD.? Or Mary Van Dyke? You may not care about middle initials but that's not all the possible combinations. This import will correctly work for most of your names by only acting on the 2-word combination and it will leave the fields blank if there are three (or more) so you can search for empty FirstName and LastName, scroll the list and manually determine (and correct) if it's Mary Van Dyke, etc. Since you will be capturing the full name in your FullName field, corrections will be easier (and you won't loose critical name information). You will STILL have to scan both fields in case an incoming FullName is Mr. Davidson or simply a last name of Van Dyke.

 

Note also that, since the Auto-Enter is set (by calculation) to 'Do Not Replace Existing Value,' these values will only be set during the import phase (because that's the only time you'll use the FullName field) and will then be available for Users to change further as needed AND it will not interfer with new record creation by your Users (because in those instances, the FullName will be empty so the calcs will not fire.

 

P

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...

Important Information

Terms of Use