Link to home
Start Free TrialLog in
Avatar of phuongnguyen
phuongnguyen

asked on

notes view

Hi,
I have a view with 2 columns and values like this:

ID             Fullname
1              Thomas Dinkel
1              Gary Wang
2              Hans Peter
1              Robert Cameroon
1              Paul Hale
6              Paul Hale


Now I create a form with 5 fields. The first field is for user to enter his ID. I want that everytime user creates document from this form, they enter ID, then program will get records in the above view to fill in 4 remaining fields in the form, depend on the ID field. How can i do this? Help me, pls.
Thanks,
Avatar of madheeswar
madheeswar
Flag of Singapore image

Create Lotus Script to fetch data and place the LS in field(Id field) exiting event.
Once they exit from the field, remaining fields will get updated.

Even u can do it through formulae.

Like:
FIELD id:=id;
@If(id="";"";@dbLooup("":"";"";"(urVIEWname)";id;columnNo)
LS:
dim ws as new notesuiworkspace
dim uidoc as notesuidocument
dim curdoc as notesdocument
Dim session As New NotesSession
  Dim db As NotesDatabase
  Dim view As NotesView
  Dim doc As NotesDocument
set uidoc=ws.currentdocument
set curdoc=uidoc.document

  key =  curdoc.id(0)
  Set db = session.CurrentDatabase
  Set view = db.GetView ("URViewName" )
  Set doc = view.GetDocumentByKey (key )
  If Not (doc Is Nothing) Then
   ' Messagebox "$" & doc.GetItemValue ("Price")(0),, _
    '"Price"
curdoc.fieldname1=doc.fieldname1
curdoc.fieldname2=doc.fieldname2
'and so on.
  Else
    Messagebox "Id is not existing"
  End If

Place the above script in Sub Exiting event of field.
The other fields may be Editable or computed.
Formula:
FIELD id:=id;
res:=@dbLooup("":"";"";"(urVIEWname)";id;columnNo);
@If(id="";"";@If(@IsError(res);"";res))

U can make the above formula as default and make the field as computed.
TO do the first lookup (choose the id), create a dialogbox with a formula to get the values, name the field ID:

res := @dbcolumn( "":"nocache";@dbname;"YourView";1);
@if(@iserror(res);"error";res)

Then create the other fields.  They will all be computed text fields , and contain lookups:
For instance, to get the Fullname in the FullName field, you would put :

res := @if(ID="";"No ID Yet";@dblookup("":"nocache";@dbname;"YourView";ID;2));
@if(@iserror(res);"Error";res)

You need to Refresh the form before the fields are updated, you can do this in 3 ways :
- manually (press F9)
- enable the Form property "Automatically refresh fields"
- check the property in the ID field "Refresh fields on keyword change"

cheers,

Tom
Maddy, why on earth would you use Lotusscript for something like this ?  It will probably work, but why write 20 lines of code when you can do the exact same thing in 2 ?
because if the user doesn't know LS, he will get familiarize with it.

and I also provided formula as an alternative. Formulas are faster. Don't forget about limitation of formulas(64K).

For this scenario, formulas is the best. And there are some users who wants LS only. so, provided both solutions.

Avatar of qwaletee
qwaletee

For the first field, use a listbox field, with choices "based on view," and show your view.

For the second field, make it cmputed with @DbLookup for the formula, example: @Text(@DbLookup(""; ""; "name of your view"; name_od_the_first_field; 2)

Just make sure the view has its first column sorted.  Oh, and make sure the ID field is text, not numeric, on both the form you are working on and the form you are displaying in that lookup view.  If not, change it to text, THEN EDIT AND SAVE EACH DOCUMENT so the field values get updated to the new data type.
Avatar of phuongnguyen

ASKER

Thanks for the comments, but what i meant is: when I input the value "1" into the ID field and press ok button (for ex.), i will get 4 records:

1              Thomas Dinkel
1              Gary Wang
1              Robert Cameroon
1              Paul Hale

and I would like to update these 4 records into 4 fields like:
- fieldA: Thomas Dinkel
- fieldB: Gary Wang
- fieldC: Thomas Dinkel
- fieldD: Gary Wang

Is there any way?

what happens if it is having 10 documents with the same id? Do u have 10 fields for that?
in formula:
for first field:
FIELD id:=id;
res:=@Subset(@dbLooup("":"";"";"(urVIEWname)";id;columnNo);1);
@If(id="";"";@If(@IsError(res);"";res))

for second field:
FIELD id:=id;
res:=@Subset(@dbLooup("":"";"";"(urVIEWname)";id;columnNo);2);
@If(id="";"";@If(@IsError(res);"";res))

change the no's in @Subset, u can get the results.

For script, if u want I can re-write.
ASKER CERTIFIED SOLUTION
Avatar of madheeswar
madheeswar
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Madheeswar,
Sorry if i have bothered you. In fact, I did not change my requirement, just because the limitation in English writing. And I do not think you are free. Thanks. Bye.
phuongnguyen,
Thanks for understanding. There are few askers where I came across. They just think we are free and they expect 100% solutions. They won't bother to try from their side. It really hurts for experts. Sometimes, due to lack of time, we point to right direction. And some times we give 100% code if we have time and patience.
And there are some questions which are asked completly wrong and later changes the requirement. You can say it due to limitaion of English or the asker can't put the problem in a proper way.


If you said that way then I do not have anything to say. Are you so angry?
I donot have any intensions to hurt anyone.
today I am angry. And not because of your question. I saw another thread, where the asker needs a cut&paste solution and it is very rude in the way the asker treating Experts. it really hurts.
I am also one of u. I asked questions and expected solution. (may not be 100%).
Atleast a good pointer in right direction will help. while we try to achieve the desired solutions, we can learn a lot.
I joined EE to ask questions. And slowly I turned to answer questions. And I gained lot of knowledge.

I assume you have solved your question.