I have a Lotus Notes field named “ProjectStatus” where updates are added weekly.
Here is an example of the output on the Lotus Notes form:
7/22/13: Here is the 2nd week status
7/8/13: Here is the first week status
I want to keep the history on the form which it does.
I have a Notrix job that maps this field to a table on SQL that passes the data to a report. I would like only the most recent comment to appear on the report so only
“7/22/13: Here is the 2nd week status” would display.
What I am asking for is either help wriiting a formula on the Notrix job that could do this or changing the field on the form somehow to acoomodate this with keeping the history on the form.
Here is the querysave for the subform that updates the ProjectStatus field:
Sub Querysave(Source As Notesuidocument, Continue As Variant)
'Update History fields.
Dim session As New NotesSession
Dim doc As NotesDocument
Dim dateTime As New NotesDateTime( "" )
Dim user As String
Dim itemInit As NotesItem
Dim item As NotesItem
Set doc = source.Document
Call dateTime.SetNow
user = session.CommonUserName
'Update the Project Status field if the user added text to the ProjectStatusPlus field. Reset the ProjectStatusPlus field for the next save.
Set item = doc.GetFirstItem( "ProjectStatusPlus" )
If ( item.text <> "" ) Then
doc.ProjectStatus = doc.ProjectStatus(0) & Chr(13) & Chr(10) & Date & " -- " & user & " wrote: " & item.text & Chr(13) & Chr(10)
doc.ProjectStatusPlus = ""
Else
doc.ProjectStatus = doc.ProjectStatus(0)
End If
Shouldn’t you be asking this to Notrix? I’d think they’d know best how to either structure your data or have ideas how to parse a text field.
Having said that, here are some general ideas you might want to think about:
Make ProjectStatus a multi-value field. If the newest item is always first, then it’s always element(0).
Switch to a parent/child design with updates being response docs. You can display them in the main doc as an embedded single category view and let Notrix figure out how to map into the structure on the non-Notes side.
If Notrix can’t treat CRLF as a delimiter, can it use other strings as delimiters? If so, stuff something like a tilde between updates.
Thanks to you both!I did contact Notrix first and they informed me that the only access to developers is when there is a product defect or problem with the products normal functioning. They don’t have support for Notes development
I think I will go with creating a new field for last status, thanks, I think I was making this more difficult than it needed to be…long day. It is great to have this forum!