Jump to content
Norma_Snockurs

CF to return first name and initial from list

Recommended Posts

Norma_Snockurs

OK brainboxes, I still struggle with custom functions and am finding this one tricky...

 

I need to convert a dynamic value list of salespersons names in to an acceptable format for a graphing plugin - all within a single calc statement.

 

Example source - return separated values, could be any small quantity:

 

John Doe

Paul Smith

Andy Capp

Una Nothing

Mandy Lifeboats

etc.

 

Required output v1:

 

"John D" ; "Paul S" ; "Andy C" ; "Una N" ; "Mandy L"

 

or Required output v2:

 

"Doe" ; "Smith" ; "Capp" ; "Nothing" ; "Lifeboats"

 

Any help appreciated. In the meantime I'll keep chipping away at it. It can't be that tricky.smiley-smile

Share this post


Link to post
Share on other sites
Norma_Snockurs
...I'll keep chipping away at it. It can't be that tricky.

Cracked it, I think.

 

// Custom Function - ShortName ( names )

Let(
 [init = LeftWords( names ; 1 ) & " " & Left( MiddleWords ( names ; 2 ; 1 ) ; 1 ) ;
 remain = MiddleWords( names ; 3 ; WordCount( names ) - 2 ) ] ;
 "\""& init & "\""  & If( WordCount( remain ) ; " ; " ) &
   Case( 
     WordCount( remain ) ;
     ShortName( remain ) )
)

Share this post


Link to post
Share on other sites
Maarten Witberg

what happens if you hit a three-name person such as Griff Rhys Jones or George Dubya Bush? I'm thinking you may be better of calculating the short name up front, in a field, make a value list of that and then doing a substitute of the value list directly (using ValueListItems). No cf needed, unless I miss something. Which is entirely possible.

Share this post


Link to post
Share on other sites
Norma_Snockurs
what happens if you hit a three-name person such as Griff Rhys Jones or George Dubya Bush?

I did consider that Maarten and you are of course correct to bring that up. However, in the context I'm using this function there is unlikely to ever be more than 8 or 9 names at most and they are just labels on a bar chart. The source list is compiled from first and last names only. In that instance Griff J or George B is perfectly acceptable.

I may yet go with a version 2 of the function anyhow which would just take RightWords( value ; 1 ) and give me a list of last names.

 

you may be better of calculating the short name up front, in a field, make a value list of that and then doing a substitute of the value list directly (using ValueListItems). No cf needed
I'm using the List function to get related values which has a similar result with a set of unique values. I figured though that I needed the recursiveness of a custom function else how do I predict how many last names there will be? Not sure I understand how I could use Substitute in the scenario you describe.

Share this post


Link to post
Share on other sites
Norma_Snockurs
what happens if you hit a three-name person...?

I found a better way to do it. This will only use the first and last words of a name regardless of how many words it comprises. A solitary first name will be returned as only that - without the initial. Empty values will be ignored.

The custom function's source for the (names ) parameter could be List() or a Value List howsoever derived.

 

// Custom Function - ShortName ( names )

Let(
 [init = GetValue( names ; 1 ) ;
  short = LeftWords( init ; 1 ) & If ( WordCount ( init ) >1 ; " " & Left( RightWords( init ; 1 ) ; 1 ) ) ;
  skip = (MiddleValues ( names ; 2 ; 1 ) = "") ;
  remain = MiddleValues( names ; 2 + skip ; ValueCount( names ) - 1 + skip ) ] ;
  If( short ≠ "" ; "\"" & short & "\""  & If( ValueCount( remain ) ; " ; " ) ) &
  Case( 
     ValueCount( remain ) ;
     ShortName( remain ) )
)

Share this post


Link to post
Share on other sites
Maarten Witberg

I was thinking in this direction:

 

"\"" &Substitute( List(YourRelation::NameShort) ; "¶" ; "\"; \" ") & "\""

 

List() produces a list of (as you mentioned) nonunique items in the (sort) order of the relationship, and ValueListItems of unique items in alphabetical order. I suppose List is just what you need then.

Share this post


Link to post
Share on other sites
Norma_Snockurs
List() produces a list of ... nonunique items in the (sort) order of the relationship, and ValueListItems of unique items in alphabetical order.

 

Worth pointing out actually. It has occurred to me that if you were to pit one method against the other in this particular case where the source is a field in (n) unsorted records, each occurrence of which has a unique value, then the List() method could well turn out to be a fair bit faster. Using a value list means that FM has to eliminate all duplicate data and then run a sort to get it in to alphabetical order.

My solution needs the names to remain unsorted anyhow.

Share this post


Link to post
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.




×
×
  • Create New...

Important Information

Terms of Use