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

Segregate Data


blevey
 Share

Recommended Posts

I have two fields: Company, Person.

 

The problem is people inputted more than one name into Person, e.g.

 

Company: ACB Welding

Person: Tim Zigly

Sam Walice

Bill Zane

 

 

I want to segregate the names into their own record, so I can use in portal. any ideas appriciated...

 

Blevey

Link to comment
Share on other sites

It kind of depends on the data being consistently entered. Your example suggests that the names are return-separated. If so, you can loop through the records and parse them out.

 

I suppose you want to make the original table the "companies" table and create a new one holding all affiliates. The question arises if this is

- one-to-many (companies can have multiple affiliates, but no person will be affiliated to more than one company) or

- many-to-many (persons can be affiliated to more than one company, companies have multiple affiliates).

The latter may seem exotic; depending on circumstances it may never occur or happen all the time. Finding out if this is the case in your database may be quite a hassle. And you need a join table to view affiliations in both directions. I don't see a possibility to fully automate this process (i.e. create a join table and a clean affiliates table in one go). You need to clean up the affiliates (take out the duplicates in the affiliates table, reset the related records in the join table with a correct affiliate ID). This would be partly manual labor I guess, deciding whether John Brown A is the same as John Brown B can't be automated.

Recently I have converted a flat file contacts database to relational; i tried to clean up as much as possible but I did leave some of the data to be corrected by the users.

 

A skeleton structure would be:

 

Companies:

CompanyID

CompanyName

 

Affiliates:

AffiliateID

AffiliateName

 

CompaniesAffiliatesJoin:

JoinID

CompanyID

AffiliateID

 

If you decide against the join you need to have CompanyID in the affiliates table of course.

 

Now you need to create a script with two loops: the first loop runs through all the records in the companies table. the second loop performs the actual parse, for instance using MiddleValues[NameField;$counter;1] where the loop exits if $counter > the number of values in the namefield (again, based on the assumption that all names are return separated). in each cycle in this second loop, a new record is created in the affiliates table and if you choose to do so, in the join table. Use script variables to shunt the necessary IDs and names between tables.

 

Make a backup before running scripts that permanently alter data.

 

You need to flesh this out if you want the names to be parsed out firstname, lastname etc, but this would be partly manual labor also (Mary Beth Applegate, Griff Rhys Jones).

 

What about phone numbers, email adresses etc?.

 

if you have trouble creating the script, please post again.

 

kjoe

Link to comment
Share on other sites

Hi Kjoe:

 

Thanks for your response...

 

That worked great... and the end result is I now have a table (Dealer Sales) with a single record for each sales person... very cool.... thanks and I appriciate the assistance..

 

 

 

 

Blevey

Link to comment
Share on other sites

  • 1 month later...

kjoe

I am working on sperating data in a field name. In that field now is data like this... Smith, Jr David A The next record might be... Smith David A

I want to put each, FName, LName, MiddleI, and Prefix in seprate fields.

But I can't remember how...

Please help

thanks

jlkfour

Link to comment
Share on other sites

Sounds nice and messy. Other than "Jr" after the last name where applicable, and the lack of any consistency about a comma after the last name, how else might the values in this field differ?

 

You need Middle (Text, Start, Size) to snip out the separate bits & pieces and drop them into the appropriate dedicated fields, but on the basis of a sample data set of only two records it's already going to require logical operators to decide which of two possible patterns.

 

That's not confidence-inspiring. One suspects that with a sample set of three one might need to set up logical operators to pick from 3 different possible patterns.

 

It's not like you can tell FileMaker "put the thing that looks like it oughta be a first name over here in the first name field"

Link to comment
Share on other sites

AHunter3

Thanks!

I had the exact text incorrect...

Ervin Jr, Samuel Richard

The comma is either after the last name or the suffix...

Is there anything we can do with that?

Help... please

Link to comment
Share on other sites

What about Billy Bob st. John Smith, esq., the third?

How many of the names fit the bill of one first name, one middle initial, one last name? Will a prefix always be neatly preceded by a comma? (as in "Smith, jr". I'm afraid I am not familiar with anglosaxon naming conventions but i suspect there are many exceptions to the rule.

And will all names in the original field be ordered the same (lastname, prefix, firstname, middleI)?

 

Generally you can make do with MiddleWords(OriginalField;X;Y) to pick out various words from a string. Where X is the starting word and Y the number of words.

 

nice and messy like Ahunter said. Well here's a shot. define four fields that have auto-enter the value 1 (or replace field contents to 1):

 

doublelast

doublefirst

doubleprefix

doublemiddleI

 

then

namefirst= MiddleWords(original;doublelast+doubleprefix+1;doublefirst)

namelast=LeftWords(original;doublelast)

MiddleInit=RightWords(original;doublemiddleinit)

Prefix=
Case 
(
  PatternCount(original;", jr") or PatternCount(original;", sr") or   
  PatternCount(original;", esq") or PatternCount(original;", md") or 
  PatternCount(original;", m.d.");
  MiddleWords(original;doublelast+1;doubleprefix)
)

 

so the assumption is built-in that any name has one last name part, one prefix, one middle initial and one first name, but by changing the number in "doublelast" etc. you can manually correct if it isn't so.

Or just copy-paste until it's OK. :P

 

kjoe

 

PS tested only summarily

PS2 middlewords() etc. will remove any , and . so if you want them back you have to hard code them in.

PS3 if the majority of names have no prefix (which would seem about right) you'd better make DoublePrefix auto-enter 0.

PS4 if you want to copy-paste to correct, then auto-enter, do not replace existing is needed or a scripted solution. I don't know if this is once-off or import dependent, but I wouldn't go for calculation fields. as this would require all subsequent data entry into the original field and make users go insane.

Link to comment
Share on other sites

kjoe & AHunter3

Thanks

I couldn't get it to work... but I figured out a work around. I went back to the excel source and did "data to columns" and then imported the data again and ta-da... it worked!

Thanks Again

Jlkfour

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use