How to convert 'domain\FirstName.LastName' into 'FirstName LastName' using string functions - report

DoddersAnton
DoddersAnton Member Posts: 3
in NAV Three Tier
Hi,

I'm currently creating a document report for purchase orders. I'm specifically working on the word document template to populate the body of the email, I need to convert the created by field into a full name. (Purchaser name is not available).

I need to convert 'xxx\FirstName.LastName' into 'FirstName LastName'.

To do this in report builder, I used a combination of inst(), left/right & len functions:
=LEFT(RIGHT(left(Fields!Createdby.Value, instr(Fields!Createdby.Value,".")-1), LEN(left(Fields!Createdby.Value, instr(Fields!Createdby.Value,".")-1)) - 5),1) &  LCASE(RIGHT(RIGHT(left(Fields!Createdby.Value, instr(Fields!Createdby.Value,".")-1),LEN(left(Fields!Createdby.Value, instr(Fields!Createdby.Value,".")-1)) - 5), LEN(RIGHT(left(Fields!Createdby.Value, instr(Fields!Createdby.Value,".")-1), LEN(left(Fields!Createdby.Value, instr(Fields!Createdby.Value,".")-1)) - 5)) -1)) & " " & Left(Right(Fields!Createdby.Value, len(Fields!Createdby.Value) - instr(Fields!Createdby.Value,".")),1) & Lcase(RIGHT(Right(Fields!Createdby.Value, len(Fields!Createdby.Value) - instr(Fields!Createdby.Value,".")),LEN(Right(Fields!Createdby.Value, len(Fields!Createdby.Value) - instr(Fields!Createdby.Value,".")))-1))

However, I'll need to do this on the query side for it to create the full name I need in the word document. The below code is as far as I've got, I think I can remove the domain element but I'm not sure how to replace the '.' with a space.
COPYSTR("Purchase Header"."Created By User",5,STRLEN("Purchase Header"."Created By User"))

I'm new to creating reports in Navision Dynamics and would greatly appreciate any guidance.

Many thanks,
A

Best Answer

Answers

  • DoddersAnton
    DoddersAnton Member Posts: 3
    Slawek_Guzek wrote: »
    CONVERTSTR( 
  COPYSTR("Created By User", STRPOS("Created By User", '\')+1),
   '.', ' ');

    Thank you Slawek!

    I then used your code to capitalise the first letter of the first name & last name
    CreatedByUserName := CONVERTSTR(COPYSTR("Created By User", STRPOS("Created By User", '\')+1),'.', ' ');

    CreatedByUserName2 := COPYSTR(CreatedByUserName,1,1) + LOWERCASE(COPYSTR(CreatedByUserName,2,STRPOS(CreatedByUserName,' ')-1)) 
+ COPYSTR(CreatedByUserName,STRPOS(CreatedByUserName,' ')+1,1) 
+ LOWERCASE(COPYSTR(CreatedByUserName,STRPOS(CreatedByUserName,' ')+2,STRLEN(CreatedByUserName)));

Categories