convert to canonical name in script or equivalent

Posted on 2006-04-24
Medium Priority
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 )
            Call doc.save(0,0)
            Set doc = view.GetNextDocument(doc)
End Sub

Question by:SysExpert
  • 3
  • 3
LVL 18

Accepted Solution

marilyng earned 2000 total points
ID: 16526555
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 doc.save 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

ID: 16526754
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

ID: 16527293
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 !
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 18

Expert Comment

ID: 16529553
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

ID: 16531145
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

ID: 16534874
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses
Course of the Month16 days, 11 hours left to enroll

864 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