convert to canonical name in script or equivalent

Posted on 2006-04-24
Last Modified: 2013-12-18
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

Question by:SysExpert
    LVL 18

    Accepted Solution

    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)

    LVL 63

    Author Comment

    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.

    LVL 63

    Author Comment

    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 !
    LVL 18

    Expert Comment

    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..
    LVL 63

    Author Comment

    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.
    LVL 18

    Expert Comment

    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.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
    This video teaches users how to migrate an existing Wordpress website to a new domain.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now