Need to remove letters from a field

[amalthea5]

I need help figuring out how to write a script for this task. I have a very large database (8000+) of members. There are two fields for their names: first name and last name. After their last name, there are some appended letters designating their certification. I need to write a script to remove these appended letters from all the inactive members.

 

So for example:

First Name: John

Last Name: Smith, A.B.C.

 

I need to have the script remove the ", A.B.C." part from all the members flagged as inactive. How should I go about doing that? Any help or resources you can point me at would be fantastic.

 

Thanks!

[LaRetta]

Is there always a comma between the last name and the rest of the stuff to remove?

If so then try this:

Left ( table::Field ; Position ( table::Field ; "," ; 1 ; 1 ) - 1 )

This will give you the last name without the extra stuff.

[eos]

Left ( table::Field ; Position ( table::Field ; "," ; 1 ; 1 ) - 1 )

 

Given the possibility that some some “seniors”, “juniors” and other phenomena may lurk in these 8,000 records, maybe it would be better to return everything before the last comma:

Let ( 
  name = Table::nameLast ; 
  Left ( name ; Position ( name ; "," ; 1 ; PatternCount ( name ; "," ) ) - 1 ) 
)

 

There are two fields for their names: first name and last name. After their last name, there are some appended letters designating their certification.

 

I suggest you devise a different strategy to store these certifications; it's quite easy to combine the name and certification info from two (or more) fields. With your current system, what do you do if you need only one or the other?

 

“One field, one fact”, as the rule goes …

[amalthea5]

Yeah I'm taking this database over and redesigning a good portion of it. The previous people had some terrible database logic. So I will be creating a new field once I clean this up. In fact, I might just try your suggestions to get those appellations removed from every entry and go ahead with my restructuring. Gotta love cleaning up

 

Thanks for your suggestions guys! I'll let you know how it goes.

[LaRetta]

Whichever 'stripping' calculation you use, be sure a take a close look because credentials oftentimes have comma as well so Oliver's (EOS) calc can break too. Example quite common: MSN, RN, CS

It might be good to create a calculation ( result is number ) with: PatternCount ( yourLastNameField ; "," ) and then search for any with 2 or greater and see how you might best address the issue. And welcome to the world of parsing calculations! They are very fun!!! :-)

[LaRetta]

Something else to keep in mind ... if there are NO commas, your result will be empty.  So only run this after finding records with a comma in that field or protect the calc further.  If you create a calculation to view your data, it might be:

Let ( [
lastName = table::yourField ;
num = PatternCount ( lastName ; "," )
] ;
Case ( 

num  = 1 ; // exactly 1 so remove it all
Left ( lastName ; Position ( lastName ; "," ; 1 ; 1 ) - 1 ) ;

num = 2 ; // do you assume the first comma is before Junior?  Or credentials have comma?
"MANUALLY CHECK" ;

not num ; lastName
) // end case
) // end let

So the bottom line is ... create a calc to look at your data before replacing it and be sure your parsing rules cover all possibilities.  And back up first. :-)

[amalthea5]

Thanks so much! It worked on almost all the entries except a few. The few it didn't work on are because my predecessors entered information in incorrectly. But that's alright because it's only a handful so I can manually fix that.

 

Now I'm off to learn more about scripting for this lovely program

[LaRetta]

Since you are new to FM but not new to programming, it would help if you know that FM is context-based.  An 'occurrence' of a table is placed in the graph - it is only a representation of your table.  And layouts must be based upon a table occurrence.  Scripts fire based upon perspective - what table occurrence you are standing on at the time you fire the script.

 

Welcome to FileMaker and welcome to FMForums!  There are many great folks here willing to assist.  :-)