INTERACT FORUM

More => Old Versions => Media Center 13 (Development Ended) => Topic started by: llafriel on March 17, 2009, 08:25:55 am

Title: Expression to get last name from field
Post by: llafriel on March 17, 2009, 08:25:55 am

Is there a way to get all characters from right to the first open space? A sort of 'Right(value,character)', where value is the string and character is the letter where to stop.

I'd like to do Right([Composer]," "), to get the last name of the composer.

Title: Re: Expression to get last name from field
Post by: Lasse_Lus on March 17, 2009, 08:53:33 am

with function ListItem you can do that, but it needs be a "List field" you can create a testfield, copy Composer to that field replace "space" with "semicolon"

and use the function ListItem([TESTComposer],1)

with the function Right you count and it works if you have the same number of letters all the time, but i doubt that you have

Title: Re: Expression to get last name from field
Post by: llafriel on March 17, 2009, 11:12:38 am

Quote from: Lasse_Lus on March 17, 2009, 08:53:33 am

with the function Right you count and it works if you have the same number of letters all the time, but i doubt that you have

Wich is why I wanted an expression that looks for the first space.
Your solution works also, but neither will work when there are more than two names.. Guess I'll have to do it manually.

Title: Re: Expression to get last name from field
Post by: Lasse_Lus on March 17, 2009, 11:18:59 am

maybe you can involve the  ListCount(...) function and if function

Title: Re: Expression to get last name from field
Post by: marko on March 17, 2009, 11:54:13 am

listitem can be run on any field, not just list type fields.

for the composer field, listitem( [composer],1,/ ) returns the first item from the composer field, using the space character as the specified delimiter.

To get the last item, we need to know how many items a given string has, which is not so straight forward.
I tried nesting a listcount() function within a listitem function, but this returned a syntax error :(

This workaround seems to work:

1. Create a new field. Give it a short name, let's say, Item. The field will be "calculated data" and the expression will be: listcount([composer],/ )
2. Enter the following expression to retrieve the last item from a space delimited composer field: listitem( [composer],[item],/ )

-marko.

Title: Re: Expression to get last name from field
Post by: llafriel on March 17, 2009, 01:21:03 pm

Thanks to both of you! Works well enough.

Title: Re: Expression to get last name from field
Post by: hit_ny on March 17, 2009, 02:42:55 pm

Quote from: marko on March 17, 2009, 11:54:13 am

I tried nesting a listcount() function within a listitem function, but this returned a syntax error :(

What about ?

listitem([composer],[=listcount([composer],/ )],/ )

Title: Re: Expression to get last name from field
Post by: marko on March 17, 2009, 02:55:48 pm

nope, but you were on the right track...

=listitem( [composer],listcount([composer],/ ),/ )

works, without the need for any additional custom fields. Nice one hit_ny :)

Title: Re: Expression to get last name from field
Post by: hit_ny on March 17, 2009, 03:10:15 pm

Cool  :D

but still don't understand the use of '/' to imply delimiter is " "  ?

Title: Re: Expression to get last name from field
Post by: marko on March 17, 2009, 03:22:05 pm

Space characters after commas are ignored by the expression evaluator. Placing the forward slash before the space 'escapes' the space character and so the expression evaluator treats it as a part of the expression.

-marko.