Merge Names...

[Ocean West]

brain is just a bit fried....

Trying to create a merge of names from a portal

TableA -

TableB will have First & Last Name and a option to Show/Hide boolean and and an sort display order field:

1 John Doe

2 Mary Doe

4 Peter Doe

3 Sarah Doe - 1 //boolean hidden

The Calculated Display TableA the cf should return the following

Results: [color:red]Doe, John & Mary & Peter

Now if you change the order of the values:

3 John Doe

2 Peter Doe

1 Mary Doe

Results: [color:red]Doe, Mary & Peter & John

If there are multiple last names:

4 John Doe

3 Mark Public

2 Peter Doe

1 Mary Doe

Results: [color:red]Doe, Mary & Peter & John; Public, Mark

grouping the last names together.

Also would like to have an option to inverse the display so it reads First Name Last:

[color:red]Mary, Petter, John Doe & Mark Public

Ideas appreciated... :

[mfrapp]

maybe another occurence of tableB <> to hidden field with a relationship sort by LastName then Order

would give you the rigth order to use List() on LastName and FirstName as parameter for a CS in a unstored calc field in TableA

since the last name will be sorted just go recursively tru the list of last name until lastname is different from last to display like your example

[NovaChan]

You may have solved this already, but here's a skeleton of a recursive custom function that could work in mfrapp's field in TableA.

/*

MergeNames( firstNameList; lastNameList )



Notes:

- Lists should be sorted

- Convert to tail recursion if handling large lists

*/



Let([

nameFirst = GetValue( firstNameList; 1 );

nameLast = GetValue( lastNameList; 1 );



nameLastPrev = $$cf_nameLast;

$$cf_nameLast = nameLast;



appendResult = 

Case( 

//-- If first entry

IsEmpty( nameLastPrev ); nameLast & ", " & nameFirst;

//-- If new last name

nameLast <> nameLastPrev; "; " & nameLast & ", " & nameFirst;

//-- Else (in same family)

" & " & nameFirst

)



];



appendResult

&

Case( ValueCount( lastNameList ) > 1;



//-- If more values, continue

MergeNames( RightValues( firstNameList; ValueCount( firstNameList ) - 1 )

; RightValues( lastNameList; ValueCount( lastNameList ) - 1 )

);



//-- Else, clear global variables

Let( $$cf_nameLast = ""; "" )



) //End Case



) //End Let

Edited October 19, 2009 by Guest
Changed function name to MergeNames()

[NovaChan]

I was hoping there would be a clever way to solve this with a few List functions and some substitution, but couldn't get it to work. It seems that there would be yet a more elegant way to do this, especially when adding the option to reverse the presentation of first and last name. I'd enjoy seeing what you come up with.

[Ocean West]

Thanks for the suggestions,

here is what I ended up doing... like what mfrapp suggested.

Created multi predicate self joined relationship by ID & Last Name - sorted by the order.

tableB>-

In TableB:

cName_LF =

Case ( not IsEmpty( end ) or hide ; "" ;

TextStyleAdd ( LastName ; Bold ) & ", " & Substitute ( List ( tableB_Family::FirstName ) ; "¶" ; " & " )

)

Then in tableA:

cName_LF =

Let ( [

v1 = Substitute ( list.unique ( List ( tableB::cName_LF ) ; "" ) ; "¶" ; "; " ) ;

v2 = "Vacant"

] ;

Case ( IsEmpty (v1) ; v2 ; v1 )

)

I am using 2 CF CustomList & UniqueList (renamed) the result that is returned is the unique occurrences of the items in the list. Items that are hidden don't show.

There are two more fields in each table that provide the inverse, display of FirstLast Name format.

If i were fully caffeinated. I suspect that this could all be achieved directly in TableA with out needing a field in tableB or the extra self join. Using CustomList. Maybe another day.

[NovaChan]

Thanks for sharing the result.

My approach was aimed at using one field in TableA, but what you ended up with is very clean and easy for someone else to understand and modify. Maybe it would be worth the extra credit of using one field if you had to implement the feature somewhere else.