You can do this either as a calculation or a custom function.
Fields (for a calculation) or arguments (for a CF) are fname1, fname2, lname1, lname2 and bname.
fn1 = Trim ( fname1 );
fn2 = Trim ( fname2 );
ln1 = Trim ( lname1 );
ln2 = Trim ( lname2 );
ln3 = If ( ln1 = ln2; ln1; "" ); // last name if same for both
bn = Trim ( bname );
sp = Char ( 32 );
amp = sp & "&" & sp;
mn = Case (
IsEmpty ( fn1 & fn2 & ln1 & ln2 ); bn;
not IsEmpty ( ln3 ) and not IsEmpty ( fn2 ); fn1 & amp & fn2 & sp & ln1;
IsEmpty ( ln2 ) and not IsEmpty ( fn2 ); fn1 & amp & fn2 & sp & ln1;
IsEmpty ( ln3 ) and not IsEmpty ( fn2 ); fn1 & sp & ln1 & amp & fn2 & sp & ln2;
fn1 & sp & ln1
Trim ( mn )
You don't need this as a Custom Function to use it as an auto-enter calculation; it doesn't care whether you use a CF or the calculation itself. Make a CF if you need the same logic in several places. Use Trim() as an auto-enter calculation with the original fields.
Let ( [
full1 = Substitute ( List ( first1 ; last1 ) ; ¶ ; " " ) ;
full2 = Substitute ( List ( first2 ; last2 ) ; ¶ ; " " ) ;
part1 = Case ( last1 = last2 ; first1 ; full1 ) ;
not count ( full1 ; full2 ) ;
Substitute ( List ( part1 ; full2 ) ; ¶ ; " & " )
Thanks for your Quick reply. I thought there must be a shorter way of doing it. Can't wait to try it out.
Thanks, I need to study this one a bit.......