How to query a document and get its field values and insert it into a form field using LotusScript?

0
votes

Example I have 2 views:

  • Profile
  • Transaction

In the transaction form, I want to be able to search a document from a view (using last name and first name) and get the address and insert in into the computed address field in the transaction.

I was thinking of inserting the LotusScript in the postOpen event.

An example would greatly help.

1
lotusscript

1 Answers

2
votes

What is your experience in coding? If you are new to this all, you should start with the formula language: First of all: independant of how your solution looks like, the first sorted column of the view has to contain your search- key.

Best practice is, to use a separate hidden view for this, to not interfere with user- wishes for the design of the view.

The second column contains a computed string with all the information you want to have in the other document, separated by a special character (~ is a very common one) The formula in that column might look like this:

City + "~" + Zip + "~" + StreetAddress + "~" + PhoneNumber

Then in your form create a computed field (e.g. LookupData) with the following code:

_lkp := @DbLookup("":"NoCache";"";"NameOfHiddenView";"HereIsYourSearchKey";2)
@If( @IsError( _lkp ); ""; _lkp )

You will have all data from the given name / key in that field and can make the other fields compute from that. E.g. You have a field called "City". Its formula is:

@Word(LookupData; "~"; 1)

The field "Phone" would have the formula:

@Word(LookupData; "~"; 4)

That's it.

Of course this can be done in LotusScript as well... This would look like (in the Postopen Event):

Dim ses as New NotesSession 
Dim db as NotesDatabase
Dim view as NotesView
Dim strKey as String
Dim docLkp as NotesDocument
Dim doc as NotesDocument

Set doc = Source.document
Set db = ses.CurrentDatabase
Set view = db.getView("NameOfHiddenView")
StrKey = "HereIsYourSearchKey"
Set docLkp = view.GetDocumentByKey(strKey, True)
Call doc.ReplaceItemvalue( "City", docLkp.GetItemValue("City")
Call doc.ReplaceItemvalue( "Phone", docLkp.GetItemValue("PhoneNumber")

There is no errorhandling in this code, no check if the document really exists, etc... Just use it as a starting point...