BillTr
asked on
Uploading Names into a Names Field
Sorry to be revisiting this. I thought I had it working but I am once again having some issues.
I import a list of Funds and Fundmanagers from Excel into a Names field type using the following:
Dim DocRdr As NotesItem
doc.FundUsers = .Cells(row, FundMgr).Value
Set DocRdr =New NotesItem(doc, "FundUsers", Names)
DocRdr.IsNames = intNames
Unfortunately Notes doesn't seem to store the Names field type correctly when updated via Lotus Script. This causes problems later when I copy the user names into a Reader field because Notes does not recognize the users name in the Reader.
But if I update the imported records directly (by changing a different field), everything works just fine. When I do the update I can see Notes is changing the format on the name to a CN= format. So, the Notes engine is updating the field with the right format at that time. Is there some way to set this via the VB script?
The other piece of information is that updating the second field via an agent or action does not work. I have to update each record directly. So there is something in the UI that is getting invoked, that I'm not triggering when deal from the backend.
I import a list of Funds and Fundmanagers from Excel into a Names field type using the following:
Dim DocRdr As NotesItem
doc.FundUsers = .Cells(row, FundMgr).Value
Set DocRdr =New NotesItem(doc, "FundUsers", Names)
DocRdr.IsNames = intNames
Unfortunately Notes doesn't seem to store the Names field type correctly when updated via Lotus Script. This causes problems later when I copy the user names into a Reader field because Notes does not recognize the users name in the Reader.
But if I update the imported records directly (by changing a different field), everything works just fine. When I do the update I can see Notes is changing the format on the name to a CN= format. So, the Notes engine is updating the field with the right format at that time. Is there some way to set this via the VB script?
The other piece of information is that updating the second field via an agent or action does not work. I have to update each record directly. So there is something in the UI that is getting invoked, that I'm not triggering when deal from the backend.
ASKER
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?
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([ToolsRefreshAllD ocs])
or
@Command([ToolsRefreshSele ctedDocs])
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 !
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([ToolsRefreshAllD
or
@Command([ToolsRefreshSele
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 !
ASKER
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?
Perhaps it is having trouble formating a comma separated list as a set of names when loading via the script?
ASKER
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
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.
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.
ASKER
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.
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 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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"
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"
ASKER
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([C N];(FundUs ers)", 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!
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([C
'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.
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.
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.
ASKER
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.
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 ,dummyImpo rt2"
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
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
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
ASKER
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
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?
ASKER
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
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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?)
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.
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.namesFiel d(0)).Cano nical
Of course, you still have to dela with reader flags, I just give the above as an example.
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.namesFiel
Of course, you still have to dela with reader flags, I just give the above as an example.
ASKER
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?
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?
ASKER
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 )
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 )
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)