convert to canonical name in script or equivalent

I am not sure if this is an issue with evaluate or just my plain unfamiliarity with getting functions to work properly with Evaluate.

Here is the scenario.

I have  4 levels of approvers with 1 name in each of 4 fields DS_Email_1 thru _4
Names are foramtted as simiar to Angel Liccia/TRISTATE/ORGNAME
I need to convert all 4 fileds to Cananical form.

I wanted to run an agent to do this and was working on a shared script since I will be doing this a lot.
The problem is that all I get back is an empty string.

I would appreciate any help  quickly, as this is holding back some important projects.

Thanks in advance

R 6.54 and R 6.55 patched

Sub LU_Namefixup(vw_name As String ,col As Integer ,fld_name As String )
      ' convert to Canonical form for WFS fields - Can not change primary sort key of view reliably
      ' go through view and update fields
      ' Sample Evaludae that works ' mem = Evaluate(|s1 := @DbLookup("":"NoCache";"" ; "JC_LU1" ; "|+  iTempPrice +|" ;fld_name  ) ; @If(@IsError(s1) ; "" ; @trim(s1))|)
      Dim sess As New NotesSession
            ' @NameLookup([Exhaustive];"Angela Liccia/TRISTATE/ORGNAME";"FullName")  ' Works as a function
      Set db = sess.CurrentDatabase
      Set view = db.GetView(vw_name )
      Set doc = view.GetFirstDocument
      While Not doc Is Nothing
            ' Fix names - convert to canonical

            strLU = doc.GetItemValue(fld_name)
            retlist = Evaluate(|@NameLookup([Exhaustive];strLU ;"FullName")|,doc) ' test without error checking ? with or without doc, still blank
            ' retlist = Evaluate(|s1 := @NameLookup([Exhaustive];strLU ;"FullName") ; @If(@IsError(s1) ; "" ; @trim(s1))|) ' returns blank
       ' Non sorted field, so it can be changed
            Call  doc.ReplaceItemValue(fld_name ,  retlist )
            Set doc = view.GetNextDocument(doc)
End Sub

LVL 63
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ok, I'm not following your logic in your agent.  If you have four fields, and all are formatted wrong (that is, abbreviated rather than canonical)
then your agent needs to select forms having one or more of the four fields filled and then step through the collection by document and convert the fields.


'instantiate session, db, collection.
'search for collection
dim flds as variant, nn as NotesName
flds = Evaluate({("fldone":"fldtwo":"fldthree":"fldfour")})
if coll.count <1 then exit sub
set doc = coll.getfirstdocument
while not doc is nothing
  isChanged = False
  for k = lbound(flds) to ubound(flds)
    if doc.hasItem(flds(k)) then
       if len(doc.getItemValue(flds(k))(0))>0 then
           set nn = new NotesName(doc.getItemValue(flds(k))(0))
           call doc.replaceItemValue(flds(k),nn.canonical)
           isChanged = true
       end if
    end if
 if ischanged then call true, false, true
 set doc = coll.getnextdocument(doc)
For evaluate statements to work on an underlying doc, then the evaluate statement needs to reference the doc
  thisvar = evaluate({@Dblookup("":"NoCache";"":"";fieldname;Col)},doc)
Also you can include a whole lot of stuff in the evaluate statement or macro:
NotesMacro$={TheList:=@DbLookup("":"";@dbName;view;field;key;col); TheList2:=@if(@isError(theList)|theList="";"Nothing";@Trim(@Unique(thelist)));TheList2}

ThisVar= Evaluate(NotesMacro$,doc)


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SysExpertAuthor Commented:
marilyng ,
Thanks for the quick response.

The issue I am  having is with the Evaluate returning a blank. I will try your script instead to see if it converts correctly.

Since I plan to use this as a general routine, I will have it run on a view, with just 1 field per loop.
A bit inefficient, but since these are just small lookup tables, and they will be updated only a few times a year, it should not make much of a difference.

SysExpertAuthor Commented:
Your solution appears to work great !!
A minor issues. How dow I pass the names of al the fields I want converted.

I tried

Fldnames = |("DS_Email_1";"DS_Email_2";"DS_Email_3";"DS_Email_4")|
As a parameter to the subroutine, but the Evaluate  shown below fails.

'      flds = Evaluate({("DS_Email_1":"fldtwo":"fldthree":"fldfour")})  ' Works OK, but I need to pass it as a param
      flds = Evaluate(fld_name) ' "Operation failed" error message if not correct format. ??

I always mess up the simple stuff...

Thanks !
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

You really can't pass as a parameter without associating a document with the evaluate statement

Usually, then, you'd have to do something like this:
flds=Evaluate(({"} + fldName + {":"}+ fldName1 + {"}),doc)

Why bother to do this when you can just:

Dim flds as variant
Redim flds(0)
flds(0) = doc.getItemValue(fld_name)(0)

Evaluate is a great tool for doing stuff in formula language to replace a bunch of script functions, subs, and calls when the evaluate statement is more robust.  But in the case of creating an array, six of one..
SysExpertAuthor Commented:
I like to build script Libraries, and I am using the bulk of your routine to do the conversion, but it would be nice to find a way to pass Just the field names to the  line that expands them.

What other options are there to create the flds array, via a parameter or two.

I will be dealing with different forms that may have 1 to 5 fields that need to be converted.

the calling routine will do
      Call LU_Namefixup("DS_LU" , 2 ,Fldnames )

which passes the view name, a dummy parameter that is not being used, and I was hoping the list of field names.

I guess I could simply concat them together with a delmiter, and use the Split routine to expand them.

Again, thanks for your help.
Where are you declaring the fields?  that is, since you're using a lot of different forms, other than cycling through every name, author, reader field, you'd have to tell the script which fields to use for which forms.  So, somewhere in your class library you're going to do a

getfieldList(db as NotesDatabase, doc as NotesDocument) as variant

dim tmpvar as variant
dim formname as string
Dim dbname as string
dim key as string
key = lcase(db.title + doc.form(0))

Select case key

case "12345bui"
  case else
end select

'error check on tmpvar
getFieldList = tmpVar
end function

Or, alternatively you can put a fldlist field on each form (this is what I do) and just find it with my calls.

I do validations using the above select.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.