• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1878
  • Last Modified:

Lotus Notes: Copying data from one database to another when fields don't match

I need a button on a form which, when pressed, creates a new form in another database, copying data from one set of fields within the form being created to another.  The simple "copy to database" action would be perfect if the fields matched.  Unfortunately they don't, and there is a lot of existing data in both databases so I can't match them.  However, the field types do match.  So basically the button needs to create a new form in the new database and take the information from field a and put it into field b, etc.  I have about 5 fields, and although the data type matches perfectly, none of the field names are the same.  I'm not conversant with Script (though I'm using Notes 6), so would prefer a formula, but if script is the only option, could you possibly provide the whole script formula with something like "field a copy from" and "field a copy to" in so I can replace it with my data with a minimum of knowledge.  Thanks very much for your help!  I think that the question is an easy one (for a decent programmer rather than someone like myself!), but perhaps a little fiddly so I've given it 125 points.  

Maggie
0
maggieball
Asked:
maggieball
  • 15
  • 14
  • 12
  • +1
2 Solutions
 
cezarFCommented:
try this in a button

LS:
      Dim ws As New NotesUIWorkSpace      
      Dim uidoc As NotesUIDocument
      
      Dim targetDB As NotesDatabase
      Dim targetdoc As NotesDocument
      
      Set uidoc = ws.CurrentDocument
      Set targetDB = New NotesDatabase("<yourServer>", "<yourDBName>")
      Set targetdoc = targetDB.CreateDocument
      targetdoc.Form = "<FormName>"
      targetdoc.Field1 = uidoc.Document.Field1
      targetdoc.Field2 = uidoc.Document.Field2
      targetdoc.Field3 = uidoc.Document.Field3
      targetdoc.Field4 = uidoc.Document.Field4
      Call targetdoc.Save(True,fasle)

Formula:
REM {Copy field to temp vars};
tmpField1 := Field1;
tmpField2 := Field2;
tmpField3 := Field3;
tmpField4 := Field4;
tmpField5 := Field5;

REM {Compose new form};
@Command([Compose];"ServerName":"DatabaseName";"FormName")

REM {Activate new Form};
@UpdateFormulaContext;

REM {Store temp vars to fields in new form};
FIELD Field1 := tmpField1;
FIELD Field2 := tmpField2;
FIELD Field3 := tmpField3;
FIELD Field4 := tmpField4;
FIELD Field5 := tmpField5;

REM {Save and close};
@Command([FileSave]);
@Command([FileCloseWindow]);

hope this helps.
0
 
jackmhlamCommented:
A formula solution with help of @Environment function:

Formula for the button in the original database:

@SetEnvironment("fieldACopyFrom1"; fieldACopyFrom1);
@SetEnvironment("fieldACopyFrom2"; @Text(fieldACopyFrom2));
@SetEnvironment("fieldACopyFrom3"; @Implode(fieldACopyFrom3; "~"));
@PostedCommand([Compose]; "ServerName":"DBName"; "FormName")

where the fieldACopyFrom1, fieldACopyFrom2, and fieldACopyFrom3 are the three fields in the original database, with field type of text, number and text list respectively.

Formula for the default value of the three fields on the other database:

@Environment("fieldACopyFrom1")

@If(@Environment("fieldACopyFrom2") != ""; @TextToNumber(@Environment("fieldACopyFrom2")); "")

@Explode(@Environment("fieldACopyFrom3"); "~")

To prevent form created directly in the other database from getting the environment values, create the compose button in the other database with the formula below:

@SetEnvironment("fieldACopyFrom1"; "");
@SetEnvironment("fieldACopyFrom2"; "");
@SetEnvironment("fieldACopyFrom3"; "");
@PostedCommand([Compose]; "":""; "FormName")
0
 
cezarFCommented:
jackmhlam, won't the formula in my comment work?
 
0
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!

 
marilyngCommented:
jackmhlam:  Welcome!  I see this is your first contribution, and wanted to complement you on your contribution, and for presenting a different point of view!  Nice to see, isn't it, cezarF?
0
 
maggieballAuthor Commented:
Hi Guys.  Thanks so much for the superfast response.  I decided to give cezarF's a go since I didn't understand about having a button in the other database (I'm not a very good programmer I'm afraid!), though I did actually try both formulas, and in both cases got "Server error. File does not exist."

I'm probably just doing some little thing wrong with the file name and maybe it will be hard for you to judge without knowing the network I'm on but here is my formula.  I wasn't sure if I needed the whole path for the filename but I tried it both ways and got the same error.   "Main" is the form name's pseudonym.  Can you see anything obvious that I'm doing wrong?  

tmpField1 := Category;
tmpField2 := ShortDesc;
tmpField3 := Comments;
tmpField4 := Date;
tmpField5 := TeamLeader;
@Command([Compose];"AUYRVA02":"PUBLIC\EXPL\GLOBAL\EXPLT010.NSF";"Main");
@UpdateFormulaContext;
FIELD Category := tmpField1;
FIELD Title := tmpField2;
FIELD Abstract := tmpField3;
FIELD Iss_Date := tmpField4;
FIELD Author := tmpField5;  

I also tried the LS option which certainly looked more elegant, but nothing seemed to happen when I pressed the button.  Any suggestions here?  I'm happy to use either.  Apologies in advance for my programming incapabilities, and thank you very much once again for your help!  Maggie

Sub Click(Source As Button)
      
      Dim ws As New NotesUIWorkSpace    
      Set uidoc = ws.CurrentDocument
      Set targetDB = New NotesDatabase("AUYRVA02", "PUBLIC\EXPL\GLOBAL\EXPLT010.NSF")
      Set targetdoc = targetDB.CreateDocument
      targetdoc.Form = "Main"
      targetdoc.Category = uidoc.Document.Category
      targetdoc.ShortDesc = uidoc.Document.Title
      targetdoc.Comments = uidoc.Document.Abstract
      targetdoc.Date = uidoc.Document.Iss_Date
      targetdoc.TeamLeader = uidoc.Document.Author
      Call targetdoc.Save(True,False)
      
End Sub
0
 
cezarFCommented:
maggieball,
the "\" is used by formula for escaping characters. try this ..
@Command([Compose];"AUYRVA02":"PUBLIC\\EXPL\\GLOBAL\\EXPLT010.NSF";"Main");

>>but nothing seemed to happen when I pressed the button<<
you need to open the target database and check if the document has been created.   if you want to see the newly created document, issue a Call ws.EditDocument(False, targetdoc) after the Call targetdoc.Save(True,False)

marilyn, :) dont want to use environment variables as much as possible. IMO, does not make sense keeping them there after using them. also, they require disk OI's, ECL, etc.

jackmhlam, no offense. :)
0
 
jackmhlamCommented:
cezarF, agree using @UpdateFormulaContext and temporary variables are easier, and environment variables are not necessary. Good for me to learn about @UpdateFormulaContext, thanks.
0
 
marilyngCommented:
See, nice discussion!  Environment variables don't disturb me, I use them when I have to, it's a text file and the footprint really isn't that large, and a lot of applications use them.  It's a preference thing.
0
 
maggieballAuthor Commented:
cezarF, thanks so much.  The button is working perfectly now for text and time fields, but the rich text field Comments (which needs to go into Abstract) and the keywords field Category which goes into the field of the same name isn't coming through.  The field types match.  Do I need to add something to those field formulas to get them to work?  (like @Implode(Category; "~")) for the keywords?

Now, here's a tricky follow on question (I've upped the points value to reflect the extra question which will require some Admin knowledge).  Both databases replicate on different servers.  AUYRVA02 is my home server, but there are about 5 different servers and users in any of those locations may need to press the button and have their own home server replica (we aren't allowed cross access to servers outside of our home ones) chosen as the default.  Is there a way to allow for 5 different potential servers in the formula and, as with doclinks, have the formula choose based on the the appropriate home server?  For example, can I use OR in the server part of the formula?  

Once again thank you!  My internal IT dept was going to charge me $300 to do that button for me and I would have had to wait a month!  You are superb and this is an excellent service!  Maggie
0
 
maggieballAuthor Commented:
Actually the keywords work too.  It's just the rich text which isn't coming across (of course that's the most important field).  Is some specific thing required for the rich text field.  Thanks again.  Maggie
0
 
marilyngCommented:
Hi Maggie,

This is script, so it should work.   Paste this in the click event of your button.  Not tested, but error trapping is on.
Not sure if you meant home servers = mail servers... so I set it for the user's mail server.  YOu can probably define your servers by the user's Notes Name, too.  That would require a little subroutine to look that up.   I'm sure Cezar might have a slick formula suggestion for you, too.  Whichever works for you.

Sub Click
      Dim ws As New NotesUIWorkSpace
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim uidoc As NotesUIDocument
      Dim doc As NotesDocument
      'Set current database
      Set db = session.CurrentDatabase
      Set  doc = ws.currentdocument.Document
      Dim currentServer As NotesName, servername As String
      If db.server="" Then
            servername = ""
      Else
            Set currentServer = New NotesName(db.Server)
            servername = currentserver.Abbreviated
      End If
      
      On Error Goto Handle_Error
      
      'Get User's Home mail server
      Dim mailserver As Variant, targetserver As String
      mailserver = Evaluate({@subset(@Maildbname;1)})
      If mailserver(0)<>"" Then
            targetserver = mailserver(0)
      Else
            targetserver = servername
      End If
      'Now define the target database
      Dim targetdb As New NotesDatabase(targetserver,"PUBLIC\EXPL\GLOBAL\EXPLT010.NSF")
      If Not targetdb.IsOpen Then
            Call targetdb.Open("","")
      End If
      
      If Not targetdb.IsOpen Then
            Msgbox "Sorry, couldn't open the target database",,"Unable to Continue"
            Exit Sub
      End If
      Print "Creating the new document..."
      Dim targetDoc As NotesDocument
      Set TargetDoc = targetdb.CreateDocument
      With targetdoc
            .form="Main"
            .category = doc.category
            targetdoc.ShortDesc = doc.Title
            'Transfer Rich Text Field...
            Dim item As NotesItem
            Set item =doc.GetFirstItem("Comments")
            If Not item Is Nothing Then
                  Call item.copyItemtoDocument(targetdoc,"Comments")                  
            End If
            'Transfer Category field
            Set item =doc.GetFirstItem("Category")
            If Not item Is Nothing Then
                  Call item.copyItemtoDocument(targetdoc,"Category")                  
            End If            
            targetdoc.Date = doc.lss_date(0)
            targetdoc.TeamLeader =doc.Author(0)
            targetdoc.ComputeWithForm False, False
            targetdoc.Save True,False, True
      End With
      
      Dim response As Integer
      response = Msgbox("Your document has been copied, did you want to open it now?",36,"Finished!")
      If response=6 Then
            Dim viewdoc As NotesUIDocument
            ws.SetTargetFrame("_BLANK")
            Set viewdoc = ws.EditDocument(False,targetdoc)
      Else
            Msgbox "Thank you!  you can view your copied document on " + servername + " server.",,"Done"
      End If
      
ExitHere:
      'clean up
      If Not targetdoc Is Nothing Then Set targetdoc = Nothing
      If Not targetdb Is Nothing Then Set targetdb = Nothing
      Exit Sub
Handle_Error:
      'Error handling
      On Error Goto 0
      Msgbox "Sorry, there was an error processing your request: " + Error$ + "-" + Str(Err),,"Unable to Continue"
      Resume exitHere
      Exit Sub
      
End Sub
0
 
cezarFCommented:
maggieball, guess you definitely need LS to copy a richtext field. as suggested by marilyn, you can use item.copyItemtoDocument to transfer the RT item to another doc. or notesdocument.CopyItem method to change the name of the target field right away.  
0
 
cezarFCommented:
maggieball, doesn't this work in copying rich text to another doc?

FIELD RTField := SomeRTField;  
0
 
marilyngCommented:
cezar, it should work, shouldn't it?  But I think what might  happening when (1st LS) you store to tempfields, and using UIDOC you forego all field types and everything converts to text.

The same might  be true of the formula when you store to tmp fields, or environment variables.

thinking formula.. hmm..you would have to create the document, and somehow get a handle on it to do the @SetDocField("UNID";"thisvalue") wouldn't you?   Don't know, just discussing..
0
 
maggieballAuthor Commented:
Thanks.  I'm afraid that I'm getting an error with the script marilyng.  (wrong number of arguments for the event handler).  It's probably something tiny, but since I don't know script and this is a reasonably complex one, I'm not sure where it might be going wrong.  I'm very happy to use the script though, and it looks like it covers everything.  I pasted it in exactly as you've written above.  Can you please have a look through and see why I might be getting the error?  I can't save it to test.

Thanks again for all your help.  Maggie
0
 
maggieballAuthor Commented:
Oh, and to answer cezar's question on copying across the RTF, with the formula, even text typed in the rich text field doesn't come across, but interestingly, after pressing the button I get a new rich text field with nothing it in under the existing field, and the help text matches the field on the form I'm transferring from.

I also tried using the LS option, using cezar's suggestion of adding Call ws.EditDocument(False, targetdoc) to the script so I could check, and the same thing happened where I got a new field, with nothing in it, but also none of the plain text fields came across - the only thing that transferred was the keyword field which has matching field names.

Thanks again!  Maggie
0
 
marilyngCommented:
Can you please have a look through and see why I might be getting the error?  I can't save it to test.<<  Dunno, I only have two event handlers:  On Error goto, and there is nothing wrong with these.   I compiled in R6.5.3, what version are you running? Are you saying you pasted the script and can't save it because of the "errors?"

Maggie, where did you paste the script?  

Create a new action button for the FORM, and set it to "LotusScript" and then paste the script in the CLICK event of the button. The form must be open in order to copy from the form to the other database, and the user must click on the action button.

If you want to select documents from a VIEW, then that is different, and needs to be called from an agent, or you want it to run when the document closes, then again that is different.

Also, in the Option section, include:  Option Declare

Then save and close the action, the option Declare will tell you if there are uninstantiated elements.

Since I have error trapping, the only errors you should see would be from my message box with the title "Unable to Continue"
-------
Also, do not paste any other script in with this one, try it CLEAN first.  (it's really not that complicated)
0
 
cezarFCommented:
also, debug LS to know which line causes the error. comment out the "On Error Goto" while debugging.
i'd suggest you use the script instead of the formula. rich text field can be manipulated better in LS.
:)
0
 
maggieballAuthor Commented:
I'm using 6.5.3, and yes, I can't save the script because of the errors ("Data not saved due to script errors").  It is an action button on the form, pasted into the click event of the button, meant to be run by a user in an open form as you've described, by pressing the button.  The error I'm getting is "wrong number of arguments for event handler: CLICK" (that's all I can see!).  The formula I used was exactly what you provided above (the long one from marylyng).  I ensured that File, Tools, Dubug LotusScript was on, but it didn't make any difference.  Again, forgive my massive ignorance, but does the debug run along with the button?  I can't run the button because I can't save the script.

I'm happy to use Script, but since I was last certified in 4.6 some 8 years ago, I have no idea about anything, which is the only downside.  So not sure what to do with it now.  Any suggestions gratefully received.  Maggie
0
 
jackmhlamCommented:
Referring to marylyng's script, please

replace: Sub Click

with: Sub Click(Source As Button)

See if you can save then. Jack
0
 
cezarFCommented:
maggieball,
which line does it point to when you get "Data not saved due to script errors"? ussually it will be in red.
0
 
maggieballAuthor Commented:
Believe it or not, the first line "Sub Click" is in red.  
0
 
maggieballAuthor Commented:
Whoops, just saw Jack's suggestion.  Okay, the button now works and I really like the messages it gives the user.  It connects with the right database and so on.  The only problem now is that it isn't actually copying anything except the matching fields.  In other words, it's doing exactly what Cezar's Notes formula did (albeit with a server check, which is great).  Any ideas why the fields might not be copying across, except for the matching field one?  Thanks!  Maggie
0
 
cezarFCommented:
which are the fields that are not being copied. you need to add all the fields you want to be copied in the program. could you post your code here?
0
 
maggieballAuthor Commented:
I'm just trying to understand the Script so I can see where it might not be copying, but I can't quite get it.  The script is definitely working, but the only thing actually coming across is Category.
If it helps, here is the map:

ShortDesc (from Doco) needs to be copied to Title (Main) - text field
Comments (from Doco) needs to be copied to Abstract (Main) - Rich text field
Date (from Doco) needs to be copied to Iss_Date (Main) - Date field
TeamLeader (from Doco) needs to be copied to Author (Main) - text field
Category (from Doco) needs to be copied to Category (Main) - text to keywords - this one works as it stands.  All of the other fields are remaining blank in Main.

Thanks!  Maggie
0
 
maggieballAuthor Commented:
Cezar, this is the code (I just unquestioningly used Marylyn's).  If I need to change something please let me know (I'm quite dense here I'm afraid).  I don't see Abstract anywhere though so maybe I'm just missing a few things.  My map is above.  At the moment in the script as it stands, only Category gets copied.

Sub Click (Source As Button)
      Dim ws As New NotesUIWorkSpace
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim uidoc As NotesUIDocument
      Dim doc As NotesDocument
     'Set current database
      Set db = session.CurrentDatabase
      Set  doc = ws.currentdocument.Document
      Dim currentServer As NotesName, servername As String
      If db.server="" Then
            servername = ""
      Else
            Set currentServer = New NotesName(db.Server)
            servername = currentserver.Abbreviated
      End If
      
      On Error Goto Handle_Error
      
     'Get User's Home mail server
      Dim mailserver As Variant, targetserver As String
      mailserver = Evaluate({@subset(@Maildbname;1)})
      If mailserver(0)<>"" Then
            targetserver = mailserver(0)
      Else
            targetserver = servername
      End If
     'Now define the target database
      Dim targetdb As New NotesDatabase(targetserver,"PUBLIC\EXPL\GLOBAL\EXPLT010.NSF")
      If Not targetdb.IsOpen Then
            Call targetdb.Open("","")
      End If
      
      If Not targetdb.IsOpen Then
            Msgbox "Sorry, couldn't open the target database",,"Unable to Continue"
            Exit Sub
      End If
      Print "Creating the new document..."
      Dim targetDoc As NotesDocument
      Set TargetDoc = targetdb.CreateDocument
      With targetdoc
            .form="Main"
            .category = doc.category
            targetdoc.ShortDesc = doc.Title
          'Transfer Rich Text Field...
            Dim item As NotesItem
            Set item =doc.GetFirstItem("Comments")
            If Not item Is Nothing Then
                  Call item.copyItemtoDocument(targetdoc,"Comments")              
            End If
          'Transfer Category field
            Set item =doc.GetFirstItem("Category")
            If Not item Is Nothing Then
                  Call item.copyItemtoDocument(targetdoc,"Category")              
            End If          
            targetdoc.Date = doc.Iss_Date(0)
            targetdoc.TeamLeader =doc.Author(0)
            targetdoc.ComputeWithForm False, False
            targetdoc.Save True,False, True
      End With
      
      Dim response As Integer
      response = Msgbox("Your document has been copied, did you want to open it now?",36,"Finished!")
      If response=6 Then
            Dim viewdoc As NotesUIDocument
            ws.SetTargetFrame("_BLANK")
            Set viewdoc = ws.EditDocument(False,targetdoc)
      Else
            Msgbox "Thank you!  you can view your copied document on " + servername + " server.",,"Done"
      End If
      
ExitHere:
     'clean up
      If Not targetdoc Is Nothing Then Set targetdoc = Nothing
      If Not targetdb Is Nothing Then Set targetdb = Nothing
      Exit Sub
Handle_Error:
     'Error handling
      On Error Goto 0
      Msgbox "Sorry, there was an error processing your request: " + Error$ + "-" + Str(Err),,"Unable to Continue"
      Resume exitHere
      Exit Sub
      
End Sub
0
 
cezarFCommented:
seems that the field mapping is not coorrect. let me work on it. ill post the code back later.  
0
 
cezarFCommented:
try this... just remove my comments, just wanted to point out what could have caused the problem.

Sub Click (Source As Button)
      Dim ws As New NotesUIWorkSpace
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim uidoc As NotesUIDocument
      Dim doc As NotesDocument
     'Set current database
      Set db = session.CurrentDatabase
      Set doc = ws.currentdocument.Document
      Dim currentServer As NotesName, servername As String
      If db.server="" Then
            servername = ""
      Else
            Set currentServer = New NotesName(db.Server)
            servername = currentserver.Abbreviated
      End If
      
      On Error Goto Handle_Error
      
     'Get User's Home mail server
      Dim mailserver As Variant, targetserver As String
      mailserver = Evaluate({@subset(@Maildbname;1)})
      If mailserver(0)<>"" Then
            targetserver = mailserver(0)
      Else
            targetserver = servername
      End If
     'Now define the target database
      Dim targetdb As New NotesDatabase(targetserver,"PUBLIC\EXPL\GLOBAL\EXPLT010.NSF")
      If Not targetdb.IsOpen Then
            Call targetdb.Open("","")
      End If
      
      If Not targetdb.IsOpen Then
            Msgbox "Sorry, couldn't open the target database",,"Unable to Continue"
            Exit Sub
      End If
      Print "Creating the new document..."
      Dim targetDoc As NotesDocument
      Set TargetDoc = targetdb.CreateDocument
      
      'With targetdoc <----- commented so as NotesACL to confuse you
      targetdoc.form="Main"
      targetdoc.category = doc.category
      
      'targetdoc.ShortDesc = doc.Title <-------------- assign the other way
      targetdoc.Title = doc.ShortDesc
      
     'Transfer Rich Text Field...
      Dim item As NotesItem
      Set item = doc.GetFirstItem("Comments")
      
'--->commented it as field names are different
            'If Not item Is Nothing Then
            '      Call item.copyItemtoDocument(targetdoc,"Comments")              
            'End If
      targetdoc.CopyItem item, "Abstract"
      
'--->category already being copied after form = "Main"
          'Transfer Category field
            'Set item =doc.GetFirstItem("Category")
            'If Not item Is Nothing Then
            '      Call item.copyItemtoDocument(targetdoc,"Category")              
            'End If          
      'targetdoc.Date = doc.Iss_Date(0)<-------------- assign the other way
      targetdoc.Iss_Date = doc.Date
      'targetdoc.TeamLeader =doc.Author
      targetdoc.Author = doc.TeamLeader
      targetdoc.ComputeWithForm False, False
      targetdoc.Save True,False, True
      'End With
      
      Dim response As Integer
      response = Msgbox("Your document has been copied, did you want to open it now?",36,"Finished!")
      If response=6 Then
            Dim viewdoc As NotesUIDocument
            ws.SetTargetFrame("_BLANK")
            Set viewdoc = ws.EditDocument(False,targetdoc)
      Else
            Msgbox "Thank you!  you can view your copied document on " + servername + " server.",,"Done"
      End If
      
ExitHere:
     'clean up
      If Not targetdoc Is Nothing Then Set targetdoc = Nothing
      If Not targetdb Is Nothing Then Set targetdb = Nothing
      Exit Sub
Handle_Error:
     'Error handling
      On Error Goto 0
      Msgbox "Sorry, there was an error processing your request: " + Error$ + "-" + Str(Err),,"Unable to Continue"
      Resume exitHere
      Exit Sub
End Sub
0
 
maggieballAuthor Commented:
When it ran I got "Sorry, there was an error processing your request: "function requires a valid ADT argument - 4207"
0
 
cezarFCommented:
at which line? guess its the date field.
0
 
cezarFCommented:
do you have computed fields in the main form? if not the "targetdoc.ComputeWithForm False, False" is not necessary
0
 
marilyngCommented:
But that's not going to throw that particular error! :)  Wow, go offline for a few minutes and the world falls apart. :))

perhaps it's the date field that Cezar changed: targetdoc.Iss_Date = doc.Date <<??

Maggie, sorry about the first line, I composed it in an agent window and not a button window.  Also, didn't quite know which fields went where, so try this,  I took out the with targetdoc statement so you can see where the fields are mapping.

Sub Click(Source As Button)
      Dim ws As New NotesUIWorkSpace
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim uidoc As NotesUIDocument
      Dim doc As NotesDocument
      'Set current database
      Dim item As NotesItem
      Set db = session.CurrentDatabase
      Set  doc = ws.currentdocument.Document
      Dim currentServer As NotesName, servername As String
      If db.server="" Then
            servername = ""
      Else
            Set currentServer = New NotesName(db.Server)
            servername = currentserver.Abbreviated
      End If
      
      On Error Goto Handle_Error
                Stop      

      'Get User's Home mail server
      Dim mailserver As Variant, targetserver As String
      mailserver = Evaluate({@subset(@Maildbname;1)})
      If mailserver(0)<>"" Then
            targetserver = mailserver(0)
      Else
            targetserver = servername
      End If
      'Now define the target database
      Dim targetdb As New NotesDatabase(targetserver,"PUBLIC\EXPL\GLOBAL\EXPLT010.NSF")
      If Not targetdb.IsOpen Then
            Call targetdb.Open("","")
      End If
      
      If Not targetdb.IsOpen Then
            Msgbox "Sorry, couldn't open the target database",,"Unable to Continue"
            Exit Sub
      End If
      Print "Creating the new document..."
      Dim targetDoc As NotesDocument
      'Create new document here..................................
      Set TargetDoc = targetdb.CreateDocument      
      targetdoc.form="Main"
      targetdoc.title = doc.GetItemValue("ShortDesc")(0)      
      targetdoc.Author = doc.GetItemValue("TeamLeader")(0)      
      targetdoc.lss_Date = doc.getItemValue("Date")(0)      
      'Transfer rich text field
      Set item =doc.GetFirstItem("Comments")
      If Not item Is Nothing Then
            Call item.copyItemtoDocument(targetdoc,"Abstract")                  
      End If            
      'Transfer Category  list field
      Set item =doc.GetFirstItem("Category")
      If Not item Is Nothing Then
            Call item.copyItemtoDocument(targetdoc,"Category")                  
      End If            
      'compute the calculated fields on the target form
      targetdoc.ComputeWithForm False, False
      targetdoc.Save True,False,True
      'end targetdoc..................................................................
      
      Dim response As Integer
      response = Msgbox("Your document has been copied, did you want to open it now?",36,"Finished!")
      If response=6 Then
            Dim viewdoc As NotesUIDocument
            ws.SetTargetFrame("_BLANK")
            Set viewdoc = ws.EditDocument(False,targetdoc)
      Else
            Msgbox "Thank you!  you can view your copied document on " + servername + " server.",,"Done"
      End If
      
ExitHere:
      'clean up
      If Not item Is Nothing Then Set item = Nothing
      If Not targetdoc Is Nothing Then Set targetdoc = Nothing
      If Not targetdb Is Nothing Then Set targetdb = Nothing
      Exit Sub
      
Handle_Error:
      'Error handling
      On Error Goto 0
      Msgbox "Sorry, there was an error processing your request: " + Error$ + "-" + Str(Err)+ _
      " Line: " + Str(Erl),,"Unable to Continue"
      Resume exitHere
      Exit Sub
      
End Sub


----------
Now, paste this into your action button, then try it once.  If you get an error, go back to the code and remark out the  "On Error goto Handle_Error" line like this:

        'On Error goto Handle_Error

Then turn on Lotus Script Debugging (files>>tools>>Debug lotusscript)
Then open the form in the client and press the button.  The debugger will automatically stop on the first line, and let you step through the rest of the script, by pressing the "Step Into" Button.  If it encounters an error, it will stop on the line that contains the error.  If this happens, make a note of the line, and click on the variables tab to explore what the values are that might not be working correctly.

You'll notice a "Doc" that you can expand, and then "items" that will list all your fields, and their current values.  You will also notice "Target doc" and where it is in the creation.  

But, anyway see if this helps and if it's easier to read.
0
 
cezarFCommented:
just some enhancements you might consider. using array of source/target fieldnames to make the mapping easier.

Sub Click(Source As Button)
      Dim ws As New NotesUIWorkSpace
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim uidoc As NotesUIDocument
      Dim doc As NotesDocument
     'Set current database
      Dim item As NotesItem
      
      Dim sourceFieldArr(4) As String  'change array dimension if you want to add more field names
      Dim targetFieldArr(4) As String  'dimension of sourse and target field arrays should always be the same
      
      
      Set db = session.CurrentDatabase
      Set  doc = ws.currentdocument.Document
      Dim currentServer As NotesName, servername As String
      
      If db.server="" Then
            servername = ""
      Else
            Set currentServer = New NotesName(db.Server)
            servername = currentserver.Abbreviated
      End If
      
      On Error Goto Handle_Error
      Stop    
      
     'Get User's Home mail server
      Dim mailserver As Variant, targetserver As String
      mailserver = Evaluate({@subset(@Maildbname;1)})
      If mailserver(0)<>"" Then
            targetserver = mailserver(0)
      Else
            targetserver = servername
      End If
     'Now define the target database
      Dim targetdb As New NotesDatabase(targetserver,"PUBLIC\EXPL\GLOBAL\EXPLT010.NSF")
      If Not targetdb.IsOpen Then
            Call targetdb.Open("","")
      End If
      
      If Not targetdb.IsOpen Then
            Msgbox "Sorry, couldn't open the target database",,"Unable to Continue"
            Exit Sub
      End If
      
      'initialize field arrays
      sourceFieldArr(0) = "ShortDesc"
      sourceFieldArr(1) = "TeamLeader"
      sourceFieldArr(2) = "Date"
      sourceFieldArr(3) = "Comments"
      sourceFieldArr(4) = "Category"
      
      targetFieldArr(0) = "Title"
      targetFieldArr(1) = "Author"
      targetFieldArr(2) = "lss_Date"
      targetFieldArr(3) = "Abstract"
      targetFieldArr(4) = "Category"      
      
      Print "Creating the new document..."
      Dim targetDoc As NotesDocument
      Dim i As Integer
     'Create new document here..................................
      Set TargetDoc = targetdb.CreateDocument    
      targetdoc.form="Main"
      For i = 0 To Ubound(sourceFieldArr)
            Set item =doc.GetFirstItem(sourceFieldArr(i))
            If Not item Is Nothing Then
                  Call item.copyItemtoDocument(targetdoc,targetFieldArr(i))              
            End If          
            Set item = Nothing
      Next
      
     'compute the calculated fields on the target form
      targetdoc.ComputeWithForm False, False
      targetdoc.Save True,False,True
     'end targetdoc..................................................................
      
      Dim response As Integer
      response = Msgbox("Your document has been copied, did you want to open it now?",36,"Finished!")
      If response=6 Then
            Dim viewdoc As NotesUIDocument
            ws.SetTargetFrame("_BLANK")
            Set viewdoc = ws.EditDocument(False,targetdoc)
      Else
            Msgbox "Thank you!  you can view your copied document on " + servername + " server.",,"Done"
      End If
      
ExitHere:
     'clean up
      If Not item Is Nothing Then Set item = Nothing
      If Not targetdoc Is Nothing Then Set targetdoc = Nothing
      If Not targetdb Is Nothing Then Set targetdb = Nothing
      Exit Sub
      
Handle_Error:
     'Error handling
      On Error Goto 0
      Msgbox "Sorry, there was an error processing your request: " + Error$ + "-" + Str(Err)+ _
      " Line: " + Str(Erl),,"Unable to Continue"
      Resume exitHere
      Exit Sub      
End Sub
0
 
marilyngCommented:
Ok, just ran a test, and it ran with no problems, no errors.  Rich text copied text, embedded and attached.  didn't test with blank or empty fields.. :)

Sorry about the field mapping problem error.  I got lost in all the postings.. :)
0
 
marilyngCommented:
Hey, Cezar - you know, I was really nice,  I gave you guys two days to answer this question ;)  

Giggle, poor maggieball doesn't understand lotusscript, and you remove my very simple With..end with statment and come back with ARRAYS?!   Stop already, we're confusing the poor person, and it shouldn't take twenty posts to solve this question.



0
 
maggieballAuthor Commented:
It's okay Marylyn!  I'm a little confused, but that's not at all due to the superb help I've had here.  I've doled out points now, and the formula is so close, and works so beautifully that I'm happy even if it isn't doing everything I want.  Rich text is still not coming across for me, though I'm getting that odd new field still - everything else works.  It's just too slow for me to work with the debugger remotely (I'm working from home and the server is outrageously slow), so I'll probably have to wait until later in the week to play in the office.  You've already done so much of the work, that perhaps my IT dept can now help me with that final bit of RichText coming across - who knows, maybe it's some quirk of our servers.  In the meantime, if you have any ideas about why the Script would create a new field with nothing in it, rather than copying the field 'data' across from the fields, I would certainly welcome them.  Thanks again for all your (late night, assuming you are in the US--a big assumption I know) help and patience.  This is a wonderful resource.  Wish I'd found you sooner!  Maggie
0
 
cezarFCommented:
marilyn, no offense. was just trying to help maggieball better understand the code.  seeing the "targetdoc.something" inside a "With..End With" made me think that she's not that good with LS. so decided to comment "With..End With" out.  

as for the arrays, just felt that the code would work better with your "copyItemtoDocument". that way the code would work regardless of the field types. however, calling "copyItemtoDocument" for all five fields repeatedly is not (IMO) an "Expert" like  solution (if i may use it) specialy in the aspect of code maintainability. Also thought that the array would make the field mapping easier.

>>Hey, Cezar - you know, I was really nice,  I gave you guys two days to answer this question ;)  <<
i know you would have solved maggieball's problem within a few posts. no doubt about it. :)
0
 
marilyngCommented:
Rich text is still not coming across for me<<  OK, I tried this so the field name must be wrong in the code.  Believe me, I did table, embedded and attached to make sure it worked.

This is the rich text declaration.  doc is the doc you have open, targetdoc is where you want the information copied.
In this case  the field in DOC is "Comments" and it's being copied to targetdoc field, "Abstract".  So, if you don't have a field called "Abstract" in the targetdoc, it's there now :)


 'Transfer rich text field
     Set item =doc.GetFirstItem("Comments")
     If Not item Is Nothing Then
          Call item.copyItemtoDocument(targetdoc,"Abstract")              
     End If          

Let us know if this is correct but still now working.

Cezar, put out the other leg so I can tug on that one for awhile to make them even...:)
0
 
maggieballAuthor Commented:
Thanks for your very clear explanation Marylyn.  Unfortunately everything is correct, and obviously something is happening since I get that blank second field.  I might try reworking it for another database and seeing if there is something specific to this database causing the problem (it's a useful button to have in any case for copying, since most of the databases I use are replicating across many servers).  If you think of anything else, please let me know.  I really appreciate the trouble you've taken over this.  Maggie
0
 
marilyngCommented:
Ok, then you need to see if the field is:

A richText Field,
Is Editable,
Doesn't have validation or other events  acting on it.  (in the field itself)

- Here what I did, I  created two documents, one in one database, one in another.  I opened a form in one, filled it out and mashed the action button.  the second document appears all with everything intact.

Therefore there is something amiss on your second document.

1. Does the Field "Abstract" exist?
2. when you open this form in designer, is the field set to RichText?
3. Is it not hidden?  Remove all Hide flags, not good for rich text.
4. Is it an editable field? If not make it so.
5. When you look at the field in designer, is there any code in the validation or any of the field events that you can see?
6. When you open the "saved"  document, check the document properties, (File>>Document Properties), and look for the field "Abstract, "  when you click on it, is the right side of the property box empty?  Is Abstract listed more than once?

Just so you know it works, do the same, create a new database, create one form and one default view, put the targetdoc fields on the form


and then edit your button on the sending form to this "testdatabase.nsf"

so that you remark out this line:

'Dim targetdb As New NotesDatabase(targetserver,"PUBLIC\EXPL\GLOBAL\EXPLT010.NSF")

and replace it with:

Dim targetdb As New NotesDatabase(t"","testdatabase,nsf")

Let us know.
0
 
maggieballAuthor Commented:
Same thing happened in the test.  Abstract remained blank, and it isn't even appearing in the properties at all, even though it appears, blank, in the form.  I checked other documents in the destination database and the abstract field is populated.  No codes, nothing hidden, field exists (until I run the button and then it doesn't come through in properties), and is rich text, editable. I even tried changing the fields around so I was copying to a field other than Abstract, but it didn't copy, or from another field.  No luck!  I don't suppose I could somehow post up the two local copies of the database so you could see it insitu?  Might help?  But already probably gone further than called for.  Maggie

0
 
marilyngCommented:
Then comments is the incorrect field on your original form.   LIke I said, it shouldn't happen, it's impossible that it should happen if the fields are correct and have the correct names.  You can send me zipped templates of both files if you like.  Email is in profile.
0
 
maggieballAuthor Commented:
It's absolutely perfect now - Marylyn you're beyond wonderful.  For completeness, and to help anyone else who might need to use this formula, I'm putting in the revised formula (which has a save feature - that was my problem - not saving first - duh!).  Thank you thank you thank you.   Maggie

Sub Click(Source As Button)
      Dim ws As New NotesUIWorkSpace
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim uidoc As NotesUIDocument
      Set uidoc = ws.currentdocument
      Dim doc As NotesDocument
     'Set current database
      Dim item As NotesItem
      Set db = session.CurrentDatabase      
      Dim currentServer As NotesName, servername As String
      If db.server="" Then
            servername = ""
      Else
            Set currentServer = New NotesName(db.Server)
            servername = currentserver.Abbreviated
      End If
      
      On Error Goto Handle_Error
      
     'Get User's Home mail server
      Dim mailserver As Variant, targetserver As String
      mailserver = Evaluate({@subset(@Maildbname;1)})
      If mailserver(0)<>"" Then
            targetserver = mailserver(0)
      Else
            targetserver = servername
      End If
     'Now define the target database
      
      Dim targetdb As New NotesDatabase(targetserver,"PUBLIC\EXPL\GLOBAL\EXPLT010.NSF")
      If Not targetdb.IsOpen Then
            Call targetdb.Open("","")
      End If
      
      If Not targetdb.IsOpen Then
            Msgbox "Sorry, couldn't open the target database",,"Unable to Continue"
            Exit Sub
      End If
      'If the  current document is new the user is prompted to save, else exit and do not create global doc.
      If uidoc.IsNewDoc Then
            Dim response As Integer
            response = Msgbox ("Did you want to save your document before copying it?",36,"Document not Saved!")
            If response <>6 Then
                  Msgbox "You need to save this document before you can create te Global Library Link.",,"Unable to continue"
                  Exit Sub
            End If            
      End If
      If  uidoc.EditMode Then
            uidoc.save
            uidoc.Refresh
      End If
      
      'Now set the current document to the uidoc.
      Set  doc = ws.currentdocument.Document
      
      Print "Creating the new document..."
      Dim targetDoc As NotesDocument
     'Create new document here..................................
      Set TargetDoc = targetdb.CreateDocument    
      targetdoc.form="Main"
      targetdoc.title = doc.GetItemValue("ShortDesc")(0)    
      targetdoc.Author = doc.GetItemValue("TeamLeader")(0)    
      targetdoc.lss_Date = doc.getItemValue("Date")(0)    
      Dim rtItem As NotesRichTextItem
     'Transfer rich text field
      Stop
      Set rtitem =doc.GetFirstItem("Comments")      
      If Not rtitem Is Nothing Then
            If  isRTempty(rtitem) =False Then
                  Call rtitem.copyItemtoDocument(targetdoc,"Abstract")    
            Else
                  Dim targetItem As New NotesRichTextItem(targetDoc,"Abstract")                  
                  Call targetItem.AddNewline(1)
                  Call targetItem.AppendText("No comments found to include...")
            End If            
      End If          
     'Transfer Category  list field
      Set item =doc.GetFirstItem("Category")
      If Not item Is Nothing Then
            Call item.copyItemtoDocument(targetdoc,"Category")              
      End If          
     'compute the calculated fields on the target form
      targetdoc.ComputeWithForm False, False
      targetdoc.Save True,False,True      
      
      'Ask if they want to open it.
      response = Msgbox("Your document has been copied, did you want to open it now?",36,"Finished!")
      If response=6 Then            
            'if you want the document to be front then unremark these lines
            uidoc.close
            Set uidoc = Nothing
            '..................................................................................
            ws.SetTargetFrame("_SELF")
            Set uidoc= ws.EditDocument(False,targetdoc)
      Else
            Msgbox "Thank you!  you can view your copied document on " + servername + " server.",,"Done"
      End If
      
ExitHere:
     'clean up
      If Not item Is Nothing Then Set item = Nothing
      If Not targetdoc Is Nothing Then Set targetdoc = Nothing
      If Not targetdb Is Nothing Then Set targetdb = Nothing
      Exit Sub
      
Handle_Error:
     'Error handling
      On Error Goto 0
      Msgbox "Sorry, there was an error processing your request: " + Error$ + "-" + Str(Err)+ _
      " Line: " + Str(Erl),,"Unable to Continue"
      Resume exitHere
      Exit Sub
      
End Sub
0
 
marilyngCommented:
Oops... there's another function I added to test for empty rich text field:

Function isRTEmpty(thisItem As NotesRichTextItem) As Boolean
      Dim tmpVar As Boolean
      Dim eobj As Variant            
      isRTEmpty= True
'...set value of doc...
      If ( thisItem.Type = RICHTEXT ) Then
            eobj = thisItem.EmbeddedObjects
            If Isarray(eobj ) Then                  
                  Forall o In thisItem.EmbeddedObjects
                        If o.Type <> 0 Then
                              isRTEmpty= False
                              Exit Function
                        End If                  
                  End Forall
            End If
      Else
            If Len(eobj)<>0 Then
                  isRTEmpty= False
                  Exit Function                        
            End If            
      End If            
      
      
      If Len(Trim(thisItem.Text))>0 Then
            isRTEmpty = False
            Exit Function
      End If
      eobj = thisItem.values
      If Isarray(eobj) Then
            If Len(eobj(0))>0 Then
                  Forall i In thisitem.values
                        If i <>"" Then
                              isRTEmpty= False
                              Exit Function
                        End If
                  End Forall
            Else
                  If Len(eobj)>0 Then
                        isRtEmpty = False
                        Exit Function
                  End If
            End If
      End If
      
End Function
0

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!

  • 15
  • 14
  • 12
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now