Uploading Names into a Names Field

Your code is setting the FundUsers field with the contents of the spreadsheet cell, then overwriting that field with the constant value "NAMES" on this line:

      Set DocRdr = New NotesItem(doc, "FundUsers", Names)

The syntax for NotesItem.New is:

      Set notesItem = New NotesItem( notesDocument, name$, value  [, specialType% ] )

So, you really only need one line to create the new field, populate it with the names and set it to the NAMES type.  Your new code should be:

Dim DocRdr As NotesItem
Set DocRdr =New NotesItem(doc, "FundUsers", .Cells(row, FundMgr).Value, NAMES)

This code seems to induce the same problem.

If do a SAVE on a record I imported, the Names field gets copied correctly to the Readers field
downstream. But, if I don't then the Reader field does not work in the view. When I login with an Admin ID I can see the name was entered the same on each test. So the data copies but the type is wrong?

I would consider using a simple update agent to go through and recompute all the fields on your imported records.  Maybe that will resolve the issue.


How to refresh computed fields on a form, quickly?
Last Modified: Wed, Jan 13 1999

If you frequently change the design of a form, especially making a few fields computed from editable, you need to know this (maybe you know this already!).

Details

Computed fields on a form won't get computed unless you open the document in the UI and refresh it.  If you have changed the design of a field from editable to computed and if you had a lot of documents already in the database, it is a nightmare to open each document, edit it and save it.   Instead there is a short-cut to recalculate the computed fields.  Here is how:

Create an agent which would run Manually from the Actions Menu / Run Once.  In the formula for the agent, simply use the following @functions:

@Command([ToolsRefreshAllDocs])

or

@Command([ToolsRefreshSelectedDocs])

When this agent is run from the view, the former @Command will refresh all documents in the view and the latter will refresh only the selected documents in the view.

Remarks

Be careful doing this, because many times you might have a computed field which stores the last modified date and user name, which may get recalculated to your name and the current time!!  The workaround in this case is to write an agent which will only update the fields that you need to update. (Even in this case, you can't prevent Notes from writing the internal field $UpdatedBy ;-))
 ---

I hope this helps !

Do you think that the problem could be related to my using a list of names? Instead of just one?
Perhaps it is having trouble formating a comma separated list as a set of names when loading via the script?

I did a quick test and I think that the list is the problem. So, I guess I will need to parse out and load the names one at a time? Or can I convert FundMgr to an array before I update the Fundusers field?

Why don't you post
a sample database
sample import file
instructions to reproduce the problem using the files provided.  Please verify that you can reproduce it yourself following these instructions

This will clarofy a lot, because I think your description makes it difficult to understand all the code you've run, all the things you've done, and what does and does not work.

Please make sure to describe THE EXACT RESULT you 1) actually got, and 2) would have expected instead

I'll say this again:  "I never manipulate my authors and readers fileds programatically".

That does not mean that I don't change document access programatically.  I do.  I just don't manipulate AUTHORS or READERS fields directly.  

I have a 2 standard fields (DocAuthors and DocReaders) that I use for most forms.  Each field is Computed, and contains a standard formula that covers all cases and never changes.

For exmaple, for my DocAuthors field, one of the values in the Computed formula is AdditionalAuthors.  This is a NotesItem that does not even have to exist on the form.

When I want to add or remove access for a user, group or role, I edit the AdditionalAuthors item in the backend, then call ComputeWithForm which computes the new DocAuthors (and DocReaders) values.

That way, I never have to worry about item types.  It always works.  Also, having only 1 AUTHORS and 1 READERS field on each document makes troubleshooting access issues very easy!

Others may argue that ComputeWithForm is too slow, but think about how often you need this to run and make up your own mind.

If you want me to post my standard DocAuthors and DocReaders formulas, just let me know.

I have read your standard DocAuthors/reader and found it very, very helpful.

But in this case, I think my issue can be isolated to just the Names field. When I pull in a single name from excel, it works. When I pull in a list of names it fails. Notes must be storying the data as a text field with commas in it, instead of a delimited set of fields.  

Perhaps I can parse out the names from the excel field and insert them into my target field one at a time. I bet there is an example of this if I search.

If that fails I will try inserting the data into a secondary field and then copy it over to my target field via a formula... using the computewithform in the script to invoke the copy, as per your idea.

Quote:  "Notes must be storing the data as a text field with commas in it, instead of a delimited set of fields."

This is easy to check.  Right after your code runs, check the contents of your names field.  If the field's data type is "Text", then you are right and we can fix the problem with the split function (no need to parse).  If the field's data type is "Text List", then the problem is somewhere else.

If the data is comming in as a comma separated string, then try this:

Dim DocRdr As NotesItem
Dim xlValues as Variant
xlValues = .Cells(row, FundMgr).Value
if (not is array(xlValues )) then xlValues = Split(xlValues, ",")
Set DocRdr = New NotesItem(doc, "FundUsers",xlValues , NAMES)

Also, if you have the names field anywhere on the form (hidden or not), make sure that it is set to allow multiple values.  If not, any edits that are made to the form (including ComputeWithForm) will cause the contents of the field to revert to a CSV string.

This is splitting the data out correctly but when I look at the DocRdr it shows the names defined as
strings. Is there a way to use an evaluate statement so that I can leverage @name to store the
names as a names type?

No, @Name will not help.

BTW: When you say that the names are defined as strings, which field property are you looking at ("Data Type" or "Field Flags")?

"Data Type" should be "Text List"
"Field Flags" should be "SUMMARY NAMES"

I'm looking at the datatype on the docrdr item and the fundusers item under the notes doc. I have also checked the field attributes by putting checkpoints in querysave and postopen.

Thank you for patient efforts to help me through this, but I'm going to have to give up and put a workaround in place. I will have the users go into each row we import and do a save. I will put the import code and problem description below:

Problem Description:
I'm importing data from excel that contains a fund, the fundusers and a fundemail.
The fund users are a set of names, I load them into a Names field then, using another script, I update Reader fields in other parts of the database. Everything works if I import a single name. But fail when importing a list. The failure is that the reader fields downstream are no longer recognized. The data is getting loaded, I can see the correct names using an Admin ID.

The workaround is to update the form after importing from excel. This is apparrently doing something to the names field. Once the manual update is done, the pushes to reader fields downstream work as intended.

Here is the code:
Sub Click(Source As Button)
      Dim FileNum As Integer
      Dim xlFilename As String
      Dim szFilter As String
      Dim ws As New notesuiworkspace
      Dim xlsFileName As Variant
      Dim DocMail As NotesItem
      Dim DocRdr As NotesItem
      Dim strNames As String
      Dim xlValues As Variant
      Dim delimiter As String
      Dim eval As NotesItem
      Dim FinMsg As String
      'Filenum% = Freefile()
      'xlFileName$ = Inputbox("What file name and path? example:H:\June282001.xls")
      
            'Get appropriate file names when required
      szFilter = "Excel Spreadsheet|*.xls|All Files|*.*|"
      xlsFileName = ws.OpenFileDialog (False, "Select Spreadsheet", szFilter)
      Msgbox xlsFileName(0)
      If xlsFileName(0) ="" Then
            Msgbox "No Spreadsheet selected, Exiting Program"
            Exit Sub
      End If
      
      xlFilename = xlsFileName(0)
      
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim doc As NotesDocument
      Set db = session.CurrentDatabase
      Set doc = New NotesDocument(db)
      Dim One As String
      
      Dim row As Integer
      Dim written As Integer
      
 '// Next we connect to Excel and open the file. Then start pulling over the records.
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Print "Connecting to Excel..."
      Set Excel = CreateObject( "Excel.Application" )
      Excel.Visible = False '// Don't display the Excel window
      Print "Opening " & xlFilename & "..."
      Excel.Workbooks.Open xlFilename '// Open the Excel file
      Set xlWorkbook = Excel.ActiveWorkbook
      Set xlSheet = xlWorkbook.ActiveSheet
      
 '// Cycle through the rows of the Excel file, pulling the data over to Notes
      Goto Records
      Print "Disconnecting from Excel..."
      xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      Print " " '// Clear the status line
      
      
Records:
      row = 0 '// These integers intialize to zero anyway
      written = 0
      
      Print "Starting import from Excel file..."
      
      Do While True
Finish:
            With xlSheet
                  row = row + 1
                  Set view = db.GetView("Main View")
                  Set doc = db.CreateDocument '// Create a new doc
                  'doc.Form = "ImportForm1"
                  doc.Form = "ImportFundList"
                  
                  If .Cells (row, 1).Value = "" And .Cells(row,2).Value = "" And .Cells (row, 3).Value = "" And .Cells(row,4).Value = ""       And .Cells (row, 5).Value = "" And .Cells (row, 6).Value = "" Then
                        Goto Done
                  End If
                  
                  'If .Cells (row, 1).Value = "BudgetXrefName" And .Cells(row,2).Value = "BudgetService" And .Cells (row, 3).Value = "BudgetMgedBy" And .Cells(row,4).Value = "BudgetXrefAmount" And .Cells (row, 5).Value = "BudgetYTDActual" And .Cells(row,6).Value = "BudgetInvFreq" Then
                  If  .Cells (row,1).Value = "End" Then                  
                        Msgbox "finish Row= " & row
                        Goto Done
                  End If
                  
                  Dim ColCheck As String
                  Dim Col As Integer
                  Dim ColMax As Integer
                  Dim FundType As Integer
                  Dim FundName As Integer
                  Dim FundMgr As Integer
                  Dim FundMail As Integer
                  Dim Office As Integer
                  Dim Other As Integer
                  Dim ColStatus As String
                  ColStatus = "Pass"
                  
                  ColMax = 7
                  Col = 1
                  ColCheck = "No"
                  
                  If ColCheck = "No" Then                        ' on the first pass we check/get the correct colum names
                        While .Cells (1, Col).Value <> "Fund Type" And Col < ColMax
                              Col = Col +1
                        Wend
                        If Col = ColMax Then
                              ColStatus = "Fail"
                              Msgbox ColStatus & " Fund Type Column"
                        End If
                        FundType = Col
                        
                        Col = 1
                        While .Cells (1, Col).Value <> "Fund Name" And Col < ColMax
                              Col = Col +1
                        Wend
                        If Col = ColMax Then
                              ColStatus = "Fail"
                              Msgbox ColStatus & " Fund Name Column"
                        End If
                        FundName = Col
                        
                        Col = 1
                        While .Cells (1, Col).Value <> "Manager Names" And Col < ColMax
                              Col = Col +1
                        Wend
                        If Col = ColMax Then
                              ColStatus = "Fail"
                              Msgbox ColStatus & " Manager Names Column"
                        End If
                        FundMgr = Col
                        
                        Col = 1
                        While .Cells (1, Col).Value <> "MailTo" And Col < ColMax
                              Col = Col +1
                        Wend
                        If Col = ColMax Then
                              ColStatus = "Fail"
                              Msgbox ColStatus & " MailTo Column"
                        End If
                        FundMail = Col
                        
                        Col = 1
                        While .Cells (1, Col).Value <> "Office" And Col < ColMax
                              Col = Col +1
                        Wend
                        If Col = ColMax Then
                              ColStatus = "Fail"
                              Msgbox ColStatus & " Office Column"
                        End If
                        Office = Col
                        
                        Col = 1
                        While .Cells (1, Col).Value <> "Other" And Col < ColMax
                              Col = Col +1
                        Wend                  
                        If Col = ColMax Then
                              ColStatus = "Fail"
                              Msgbox ColStatus & " Other Column"
                        End If
                        Other = Col
                        
                        'Col = 1
                        'While .Cells (1, Col).Value <> "BudgetService" And Col < ColMax
                        '      Col = Col +1
                        'Wend                  
                        'If Col = ColMax Then
                        '      ColStatus = "Fail"
                        '      Msgbox ColStatus & " BudgetService Column"
                        'End If
                        'ServiceCol = Col
                        'ColCheck = "Yes"       ' check columns one time
                  End If
                  'doc.AMOUNTBILLED = .Cells(row, 7).Value
                  'doc.SHIPMETHOD = .Cells( row, 8).Value
                  'doc.SHIPDATE = .Cells(row, 9).Value
                  'doc.TRACKINGNUMBER = .Cells(row, 10).Value
                  
                  If ColStatus = "Pass" Then
                        If Row > 1 Then  ' first row is the label name, dont import it
                              doc.FundType = .Cells( row, FundType ).Value
                              doc.FundName = .Cells(row, FundName ).Value
                              
                              
                              'doc.FundUsers = .Cells(row, FundMgr).Value
                              strNames = .Cells(row,FundMgr).Value
                              
                              delimiter = "," ' Change to you delimiter.
                              xlValues = .Cells(row, FundMgr).Value
                              'If (Instr(xlValues, delimiter) <> 0) Then xlValues = Split(xlValues, delimiter)
                              If (Instr(xlValues, delimiter) <> 0) Then xlValues = Split(xlValues, delimiter)
                              Set DocRdr = New NotesItem(doc, "FundUsers",xlValues , NAMES)
                              
                              
'many attempts to get the imported excel to come in correctly.                               
' *******************************************************************************************************
'For x=0 To Ubound(DocRdr.values)
                              '      eval = session.Evaluate("@Name([CN];(FundUsers)", doc)
                              'Next
                              'Msgbox strNames
                              'xlValues = .Cells(row, FundMgr).Value
                              'If (Not Isarray(xlValues )) Then xlValues = Split(xlValues, ",")
                              'Set DocRdr = New NotesItem(doc, "FundUsers",xlValues , NAMES)
'                              Set DocRdr =New NotesItem(doc, "FundUsers", Names)
'                              DocRdr.IsNames = intNames
                              'Set DocRdr =New NotesItem(doc, "FundUsers", .Cells(row, FundMgr).Value, NAMES)
                              'doc.Maillist = .Cells(row, FundMail).Value
                              'Set DocMail =New NotesItem(doc, "Maillist", Names)
                              'DocMail.IsNames = intNames
' *******************************************************************************************************                              
                              Set DocMail =New NotesItem(doc, "Maillist", .Cells(row, FundMail).Value, NAMES)
                              
                              doc.FundOffice = .Cells( row, Office ).Value
                              doc.Other = .Cells(row, Other).Value
                        End If
                  Else
                        Dim Warning As String
                        Warning = |The Import failed because the first row of the Excel file does not match the expected names.
They s/b: Fund Type, Fund Name, Manager Names, MailTo, Office, Other |
                        Msgbox Warning
                        Goto Done
                  End If
                  Call doc.Save( True, True ) '// Save the new doc
                  
                  written = written + 1
                  Print written
                  If written < 300 Then ' set to a max value, just incase
                        Print written
                        Goto Finish
                  Else
                        Print written
                        Messagebox "Finished"
                        Goto Done
                  End If
            End With
      Loop
      Return
Done:
      Print "Disconnecting from Excel..."
      xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      Print " " '// Clear the status line
      FinMsg = | Press PF9 or Refresh to see the data, delete ALL Null rows at the bottom!!!!!!!
Then go into each record and update the status to Update via the button labeled Update Format.
This reformats the names list into the correct Notes format. |
      Msgbox FinMsg
End Sub

Thanks again!

Just a note about the "names" data types.

They are all just text type.  There is no names type.  No reader names or author names either.  Those are all just "flags" on the text type, jsut as the summary flag tells Notes whether a text field is displayable in views or not.

If I recall corretcly, the names "type" was only supposed to be a unique type on the form, never a unique back-end flag, because there is no code anywhere that reads this differntly than text.  When the UI type was invented (to support display format in teh abbreviated names display), the developer working on it made a mistake and set up a special flag on the back end as well, but it serves no purpose.  As long as the data LOOKS like a canonical name ("CN=.../O=..."), it is fine.

Readers names and author names do make use of the unique field flags assigned to them, though the underlying type is still just text.

Therefore, if you are importing "names values" as text, that's fine, and you can even append those values to a reader field.

I always wondered why there was a NAMES type.  Never found a place to use it.  Thanks, qwaletee!

But he's right, you know.  The field contents are always text or text list.  The flags just tell Notes how to handle the text.

Thanks Qwaletee,
Any thoughts as to why a list of names is failing when a single name does not?

The UI must be doing something to the list. I tried doing the update via an agent, and it I still failed. I actually have to update each row one at a time. But once I do that, everything flows fine after that.

Bill,
Thanks again for all your help! I wouldn't have gotten this app out at all without your advice.

FYI...
I split my process into 3 stages, using 3 separate views(import, upload and review), so users cannot upload something that hasn't been updated. I will push them to stay away from the excel upload and use the UI for everything.

Tell ya what.  If I understand you correctly, it doesn't work when first imported, and it does work after it is recalculated.

So, let's see the difference between them!

RUn the following agent on a doc BEFORE it is working, and then after it is working. Post the r esults.  It will display a dialog box at the end of each run, you can click on the title of the dialog and then press Control-C to copy the contents to the clipboard.

Make sure to modify FieldNames at the top to reflect the names of all the actual fields that contain names and.or readers, etc. during this process.  I don't really care about the other fields.

Const FieldsAffected = "dummyImport1,dummyReader1,dummyImport2"
Dim s as new notesSession
Dim doc as notesDocument, item as notesItem, report as string, item as notesItemm CRLF as string
CRLF = chr$(10 & chr$(13)
Set doc = s.documentContext
Forall fieldName in Split(FieldsAffected,",")
   report = report & fieldName & "("
   set item = doc.getFirstItem(fieldName)
   if item is nothing then
      report = report & "missing"
   elseif ubound(item.values) = 0 then
      report = report & "flat text"
   else
      report = report & "text list " & (ubound(item.values) + 1) 'acocunt for zero offset
   end if
   if item is nothing then
      report = report & ")" & CRLF
   elseif item.isReaders then
      report = report & ",READ) " & item.Text CRLF
   elseif item.isAuthors then
      report = report & ",AUTH) " & item.Text CRLF
   elseif item.isNames then
      report = report & ",NAME) " & item.Text CRLF
   elseif item.isReaders then
      report = report & ") " & item.Text CRLF
   end if
End Forall
msgBox report

Okay, keep in mind there are 3 forms involved.
form1 (gets the excel) and updates form2 (stores fundname and associated people)
form3 is created when a tran is entered takes the names off form2 into a Reader field.

Results:
form1 and 2 has only names fields and consistently displays the data as a text list/Name, regardless of whether I recalc the form.
Form3 shows a difference in that here I have the readerfield. But it displays as a textlist/reader field in both cases.

It's working is dependent upon
A). my recalcing the names field on form1. (though I think if I recalc'd form 2 it would work too) then reposting the tran to call it into form3.
B). if I import just one name on form1 as opposed to a list. Something about the comma delimited list is messing it up. I suspect notes has it stored as one long string, per my discourse with Bill I added code to split the names out but that didn't fix it. (I feel like I'm looking past something obvious).

The code I ran:
Sub Initialize
      Const Fieldname = "fundusers"
      Dim s As New notesSession
      Dim doc As notesDocument
      Dim item As notesItem
      Dim report As String
      'Dim item As notesItem
      Dim CRLF As String
      CRLF = Chr$(10 & Chr$(13))
      Set doc = s.documentContext
      'Forall fieldname in Split(FieldsAffected,",")
      report = report & fieldName & "("
      Set item = doc.getFirstItem(fieldName)
      If item Is Nothing Then
            report = report & "missing"
      Elseif Ubound(item.values) = 0 Then
            report = report & "flat text"
      Else
            report = report & "text list " & (Ubound(item.values) + 1) 'acocunt for zero offset
      End If
      If item Is Nothing Then
            report = report & ")" & CRLF
      Elseif item.isReaders Then
            report = report & ",READ) " & item.Text  & CRLF
      Elseif item.isAuthors Then
            report = report & ",AUTH) " & item.Text & CRLF
      Elseif item.isNames Then
            report = report & ",NAME) " & item.Text & CRLF
      Elseif item.isReaders Then
            report = report & ") " & item.Text & CRLF
      End If
'end Forall
      Msgbox report
End Sub

Can I see some actual output?

Hi qwaletee,
Sorry for the delay, I just got back in town.

I saved the actual output messages to a word doc and can send that to you, but I typed it below with
Name1 - Name4 representing the actual user names. They display as normal names (ie, John A Smith)

Form1.. pulls data in from excel.
fundusers(text list 4, NAME) Name1; Name2; Name3; Name4

Form2... updated from the import form
sanctionedviewers(text list, NAME) Name1; Name2; Name3; Name4

Form3...The data from Form2 is added to a transaction here
Readers(text list, READ) Name1; Name2; Name3; Name4

The problem form is Form2? Or is it a different form?

I also would expect a "before and after," since you said opening and saving fixes the problem.  I need to see the output "as created" as well as after the re-save.

I expect the second output was actually
sanctionedviewers(text list 4, NAME) Name1; Name2; Name3; Name4

Please don't sanitize Name1, etc., because we need to see the canonicalization of the name.

Sorry I dropped the 4. I was copying off screen prints. The before/after did not produce different results as far as the field type data produced by your trap.

On the canonicalization....The names produced in your trap do not display the data in the CN format
(CN=John A Smith/OU=Corp/O=xxx).  I didn't sanitize them on format, except to block out the end user's name.  I'm okay with sending you the screen prints offline, but I'm not comfortable posting it here.

I have been updating form1 to correct the problem, but I tested updating form2 and it does fix the issue as well.

I'm guessing that it's having trouble with the list from excel. Again, if I use just one name on the import everything works. But if I use a list it fails.

I have put together a workaround that I can live with. I don't want to take/waste anymore of your time,,,
It's just one of those things that bug ya...when you can't figure it out.

So..the names in excel have to be in a CN format before I import them? I didn't want to go that route because my endusers not technical types and I wanted to keep it simple for them.

A few posts earlier you said that the names type was intended to be on the frontend and served no purpose to the backend. Perhaps the UI leverages the names type and formats the data into it's appropriate format when I do the save. But, if it was doing that, wouldn't I see the CN format when I view the data?

Perhaps the CN and the UI formating are unrelated solutions to the same problem. (where Notes handles the name correctly if the data is CN OR if the UI formats it?)

SOrt of.  yes, the form UI leverages the specialness of a names field to display it in abbreviated format but store it in canonicalized format.  However, that is an attribuet of the file on the form, not an attribute of the data ite,m stored in the document note.  That's confusing -- many programmers have trouble mentally separating the form definition form the data actually stored. As a mental excercize, imagine that you have a field defined as numeric, and a documeht eas stored with the number 313 in it.  if you changed the form, and set the field type to text, and created another document with the value 144 and saved it, you would have two documents with the same foirm, once with numeric value 313, the other with text value 144.  If you tehn edited the 313 document and saved it, both would now be text.

So, too with the reader/author/names field types.  They all get stored as text, and they have various field flags.  But the names type has a flag that servers no purpose, and in no way changes how Notes does procesisng.  It is the form that affects teh procesisng of display versus storage.

But let's get back to your users.  You don't want them to have to enter canonical names.  That's fine.  You just have to process the import so they become caonoicalized.  And that depends on how you are iuimporting.

If you use a OCL fiel, you can have a formulastart/formulaend with something like:
namesField := @Name([Canonicalize]; namesField)

If you are using LotusScript, you can do something like:
doc.replaceItemValue "namesField" , s.createName(doc.namesField(0)).Canonical

Of course, you still have to dela with reader flags, I just give the above as an example.

Sorry for the delay in getting back.

When I set the names field on the excel import I use the following line.
Set DocRdr = New NotesItem(doc, "FundUsers",xlValues , NAMES)
                              
Is it possible to use canonical in the specialitems field...instead of NAMES?

Thanks for all the help with this issue. I wish I could award more than 500 points. I'll split them as best I can.

You can't use CANONICAL in teh special item parameter.  You can however get the canoncial value first before setting up the item.

Dim nameObject as New NotesName(xlValues)
Set DocRdr = New NotesItem ( doc , "FundUsers" , nameObject.Canonical , NAMES )
or
Set DocRdr = New NotesItem ( doc , "FundUsers" , nameObject.Canonical , READER )