Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[guy007]

Hi. I have two field names [Composer] and [Artist] which I enter as {Last Name , First Name}. Works fine for my classical music collection. But in some cases I would like to have a new field which would calculate a {First Name Last Name} content from the [Composer] and [Artist] fields. I can do that easily in some programs (FileMaker Pro e.g.) using a calculated field and the Left and Right functions, but didn't see anything in the Expressions functions which would let me do that in MC. I can always create the fields manually, but I'd rather MC did the work. Is there any way to program fields to massage my [Composer] and [Artist] fields into new First Last name fields? Thanks very much. Every question I've asked on this board has been answered quickly and accurately--very impressive. Guy.

[marko]

You can use the "ListItem" function for this, with the comma as a delimiter...

=clean(listitem([composer],1,/,)) clean(listitem([composer],0,/,))

"clean" should take care of any leading or trailing spaces that might occur if you have used {last name , first name} as well as {last name,first name}
"ListItem" can look at any given string, using any given delimiter, and display the nth item in that list.
As your delimiter is a comma, and the MC expression engine uses commas in its evaluations, you must use a forward slash to 'escape' the comma, thus instructing MC that this comma is not part of the expression itself, and so must be treated literally.

I tested this on two files and it appears to work as expected.

-marko.

Every question I've asked on this board has been answered quickly and accurately--very impressive.

We do try, but it's not always like that, you must be asking the right kind of questions at the moment

[guy007]

Hi Marko!!!
As we used to say in college, "Just like it had eyes!" Beautiful solution! Works perfectly. Thanks. Guy

[Matt]

Should we add a ListSwap(...) or SwapName(...) or some other function to make this easier?

[Marra]

Tested Marko's expression and it works in all cases except

"Peter, Paul & Mary"  becomes "Paul & Mary Peter".

I guess the only way around this is to enter "Dylan, Bob" as "Dylan; Bob" and change expression ( /,  with ; )
The delimiter (; in this case) is unique - it is not used in the field except when names are reversed.

[MusicHawk]

Should we add a ListSwap(...) or SwapName(...) or some other function to make this easier?

For what it's worth, IBM Lotus Notes has grappled with this for years. Notes handles multi-value fields/data by using a colon as a list item identifier. It works two-way. An outputted list is items separated by colons. Or, inputting items separated by colons tell the program it is a list.

Supposedly, Ray Ozzie (Notes' architect) chose a colon because it doesn't naturally appear very often in text values that might be part of a list. This lets users more freely use comma and semi-colon within data.

Unfortunately, Notes isn't entirely consistent, letting developers use comma and/or semi-colon as displayed/typable list delimiters for form fields, since users understand using a comma. But this fights with data like company names "JRiver, Inc. -- one data item or two?). Internally, the comma/semi-colon is discarded, replaced by the internal item colon delimiter (actually, not even by that, but that's what programmers see and use).

This makes it fairly straightforward to manipulate data at various levels. With the entire list item delimited by colon, within an item lastname, firstname can be flipped around by just looking for comma or semi-colon, whatever the user prefers. (Developers learn the hard way which to let the user use.)


In MC, semi-colons are used in the UI for list items, and commas are tolerated within items. But there's the challenge of lastname, firstname vs. Peter, Paul & Mary, et al.

Perhaps MC can toss the colon into the plumbing as the explicit list item delimiter (such as in Keywords and other list-type fields), freeing up comma and semi-colon for use within data. If so, at a user's preference, comma could be used for natural data (Peter, Paul & Mary), semi-colon could be used for lastname;firstname, and in expressions/API colon would be used for multi-value lists (Peter, Paul & Mary : Police : Presley; Elvis : Prince)

(Actually, in the above list Notes would have quotation marks delimiting the values; however, actual field/variable names would be handled as shown. Spaces around the colons are ignored allowing for more readable code. Notes has a bunch of functions to manipulate lists.)

This could work because MC requires that a field be explicitly defined as a list or not. So even though a colon might be common in some fields (I use it in naming classical works, for instance), such a field isn't likely to be a list-type.

[Vincent Kars]

Should we add a ListSwap(...) or SwapName(...) or some other function to make this easier?

I think a Find function would be usefull
Find([field],string)
It returns 0 if string is not found, otherwise the starting position of string
FindR([field],string) would be convenient too, it starts searching from the rear of [field]

[Qythyx]

Instead of just a swap function, I'd prefer a ListReverse function. I've been tagging places hierarchicaly, like "US\NC\Charlotte", but sometimes I want to output it as "Charlotte, NC, US". One problem is that the number of items is my lists isn't always the same.

[MusicHawk]

One flexible solution is to treat the values in a list-type field as an array, then provide functions to access and manipulate array elements.

So, Place value "US\NC\Charlotte" (assuming \ is a recognized list separator) can be accessed as [Place.1] = "US", [Place.2]="NC", [Place.3]="Charlotte. (I'm just making up syntax that might be in MC-style, where square brackets identify a field name.)

Then allow concatenation or similar to rearrange, so something like [Place.3]+, +[Place.2]+, +[Place.1] resolves to "Charlotte, NC, US".

(Perhaps a tad tricky for MC to recognize desired punctuation vs. its own syntax since its expressions do not usually require string delimiters. Maybe the commas would need to be escaped.)

If a list field can be handled as an array, we'd just need the usual array functions, to check number of elements, loop through the array, add/change/remove elements, etc.

[gappie]

i think the ListReverse idea is nice. list swap would do it for me also.

 
gab