Solved

Using selected values in a listbox

Posted on 2003-11-12
21
1,212 Views
Last Modified: 2013-12-18
Sorry experts, this necessarily has a long explanation. Hopefully you'll find it interesting and worth reading! The points will reflect this ;-)

In a database I have a form called Form1 which I use to store data about chemicals. The database has lots of these forms with data in them. The form also has a radio button field called "Selected" which is by default "No" but the user can change to "Yes" if his factory uses that chemical.
I have a view called "Fluids in use"  which filters out these forms that pass the condition Selected="Yes". Its first column is the name of the chemical described on the form.
In another form called Form2, I have a listbox field called Fluids whose formula is @Unique(@DbColumn("":"NoCache";"";"Fluids in use)";1)). So when the user creates a document with this Form2, it displays in the list box all the selected fluid names. All this bit works fine.

Now for the tricky bit. I've noticed that I can click the names in the listbox and a little tick appears by the ones I click. If I close the document and select it in a view and open the properties dialog box, I can see the contents of all the fields in the document, and when I look at the Fluids field it has in it all the names of the chemicals that I have ticked. If I didn't select any chemical by clicking it as described, then this field is empty (ie it shows "") in the dialog box. I can get this to do things for me, like hide fields in the form using a formula like @If(Fluids="") - if a fluid name is ticked, this formula returns false and vice versa.

What I'd now like to do now is to take this a bit further. I'd like to access data on the selected Form1 forms, from within the Form2, but only if their name has been selected in Form2 by clicking the box.

So, to recap:
1. We have lots of instances of Form1, most with Selected="No", some with Selected="Yes"
2. We have a single Form2, with a listbox field Fluids showing the names of fluids where the user has changed Selected to Yes
3. I have clicked several but not all of these fluid names in Form2 and they have a little tick by their name.
4. I want to read the value of another (number) field Field2 in all the Form1 documents that have been given a tick, but no others.

I'm then going to work on the data thus retrieved from the Form1 documents using existing code. This code presently works, but only for all the selected fluids, ie it doen't allow for whether the name is ticked in the listbox or not. It involves reading into a matrix the values of Field2 from all the documents in the "Fluids in use" view. So we need to repeat that but add on this new condition that the fluid names are ticked in the listbox.

Phew! Long one. I hope you made it to here and can help!!

Regards,

Stuart

0
Comment
Question by:StuartOrd
  • 8
  • 7
  • 5
  • +1
21 Comments
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
@DbLookup("":"NoCache";"";"Fluids in use)";Field2; "someFieldName")

Will do it.  You are using Field2 as a lookup key to find entries in the same view.  The fact that there are multiple keys is OK -- as long as there are matches for all the keys, this lookup will work properly.  Instead of "someFieldName" -- use a column number or the name of the field you wish to look up.  You will need a separate @DbLookup for each field.  Field names must be in quotes. Column numbers must not be.
0
 
LVL 19

Expert Comment

by:madheeswar
Comment Utility
u can change the above code to:
res:=@DbLookup("":"NoCache";"";"Fluids in use)";Field2; "someFieldName");
@If(@IsError(res);"";res)
0
 

Author Comment

by:StuartOrd
Comment Utility
Sorry, I still wasn't clear enough. This has to be done with script rather than @ commands.

Let's say I have lots of documents made with Form1 in my database, and of these 6 have got Selected = "Yes". Their names have been put into a field  called Name. Lets say the names entered for the selected 6 are A, B, C, D, E and F. Other data has been put into fields Data1, Data2 and Data3 on those forms.

When I create a Form2 document, the listbox has the list of 6 names shown. None is ticked. I decide that three of them are relevent on the present Form2, say A, C and D, and so I click them and they each become marked by a tick.

What I want my script to do is to retrieve the data in fields Data1, Data2 and Data3 but only for the documents ticked, ie A, C and D, and store these in an array, say Data1(1), Data1(2), Data1(3), Data2(1).......Data3(3). Then I can use my existing script to work on these numbers.

Can you help for this?

Thanks,

Stuart
0
 
LVL 19

Expert Comment

by:madheeswar
Comment Utility
Place the below code in your field exiting event in ur ListNameField

Dim ws As New notesuiworkspace
      Dim uidoc As notesuidocument
      Dim curdoc As notesdocument
      
      Set uidoc=ws.currentdocument
      Set curdoc=uidoc.document
      
      Dim ss As New notessession
      Dim db As notesdatabase
      Dim view As notesview
      Dim doc As notesdocument
      Dim dc As notesdocumentcollection
      
      Set db=ss.currentdatabase
      Set view=db.getview("URViewhavingDocsfromFORM1")
      
      Dim item As notesitem
      Set item=curdoc.GetFirstItem("ListboxFieldName")
      
      k=0
      For x =0 To Ubound(item.values)
            Set dc=view.getAlldocumentsbykey(item.values(x),True)
            Set doc=dc.getfirstdocument
            Dim arr() As String
            Dim arr1() As String
            Dim arr2() As String
            
            While Not doc Is Nothing
                  Redim Preserve arr(k)
                  Redim Preserve arr1(k)
                  Redim Preserve arr2(k)
                  arr(k)=doc.FielData1(0)
                  arr1(k)=doc.FielData2(0)
                  arr2(k)=doc.FielData3(0)
                  k=k+1
                  Set doc=dc.getnextdocument(doc)
            Wend
      Next

I did not tested it, but thats the logic u need to use.
0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
Don't know why it has to be script, but OK.  Here is a general purpose "equivalent" to @DbLookup in LotusScript.

Function DbLookup(view as notesView, keyArray as string, columnOrFieldName as varuant) As variant
  Dim result() as string, repeat as integer
  Dim docs as notesDocumentCollection, doc as notesDocument, docResult as variant, docResultArray() as string
  Forall key in keyArray
    Set docs = view.getAllDocumentsByKey(key)
    set doc = docs.getFirstDOcument
    do until doc is nothing
      if isNumber(columnOrFieldName) then
        docResult = columnValues(columnOrFieldName)
        if isArray(docresult) then
          docResultArray = docResult
        else
          redim docResultArray(0)
          docResultArray(0) = docResult
        end if
      else
          docResultArray=doc.getItemValue(columnOrFieldName)
      end if
      forall singleResult in docResultArray
        if repeat then
          redim preserve result(ubound(result)+1)
          result(ubound(result)) = singleresult
        else
          redim result(0)
          result(0) = singleResult
          repeat = true
        end if
      end forall
      set doc = docs.getNextDocument(doc)
    loop
  End Forall
End Function
0
 
LVL 31

Expert Comment

by:qwaletee
Comment Utility
Oops...


    loop
  end forall
  DbLooup = result
End Function


I;m sure there are a few other bugs as well!
0
 
LVL 24

Expert Comment

by:HemanthaKumar
Comment Utility
Stuart

I don't know why others missed this point but can't you use @PickList ( or scrip equivalent PickList) to get column values. This is more superior to DBLookups and DBColumn plus doesn't have size limitation.

It is simple to implement....Create a hidden column with Following formula

Fluids + "~" + @Text(NumberField)

And let us say this is 4th column in the view

Create a Computed Field FluidsSelected and the create a button beside it for lookup with following formula similar to this

sel := @PickList([CUSTOM]; @DBName; "Fluids"; "Select Dialog"; "Select Fluids"; 4);
@If(@Trim(sel) = ""; @Return(@Prompt([ok]; "Warning"; "No values selected from the list")); "");
FIELD FluidsSelected := @Word( sel; "~"; 1);
FIELD FluidNumber := @Word(sel; "~"; 2); ""

~Hemanth
0
 

Author Comment

by:StuartOrd
Comment Utility
Hi again,
I've been straightening up my list box and a few other things so this has been delayed. But now I'm trying madheeswar's code as it looked easier to understand. I've been altering it to do the processing that I want to do as it seems easier than dealing with arrays now that I try it out. Unfortunately there are a few problems. Here's the present code in my list of chemicals in field "Fluids":
Sub Exiting(Source As Field)
      Dim ws As New notesuiworkspace
      Dim uidoc As notesuidocument
      Dim curdoc As notesdocument
      
      Set uidoc=ws.currentdocument
      Set curdoc=uidoc.document
      
      Dim ss As New notessession
      Dim db As notesdatabase
      Dim view As notesview
      Dim doc As notesdocument
      Dim dc As notesdocumentcollection
      
      Set db=ss.currentdatabase
      Set view=db.getview("2a. Fluids in use")
      
      Dim item As notesitem
      Set item=curdoc.GetFirstItem("Fluids")
      
      Dim FlashLow, FlashCurrent As Variant
      Dim GasGroupHigh, GasGroupCurrent, TempClassHigh, TempClassCurrent As String
      
      'This sets initial values
      FlashLow=1000
      GasGroupHigh="IIa"
      TempClassHigh="T1"
      
      For x =0 To Ubound(item.values)
            Set dc=view.getAlldocumentsbykey(item.values(x),True)
            Set doc=dc.getfirstdocument
            While Not doc Is Nothing
                  FlashCurrent=doc.Flash(0)
                  If FlashCurrent<FlashLow Then
                        FlashLow=FlashCurrent
                  End If
                  GasGroupCurrent=doc.Group(0)
                  If GasGroupCurrent<GasGroupHigh Then
                        GasGroupHigh=GasGroupCurrent
                  End If
                  TempClassCurrent=doc.TempClass(0)
                  If TempClassCurrent<TempClassHigh Then
                        TempClassHigh=TempClassCurrent
                  End If
                  Set doc=dc.getnextdocument(doc)
            Wend
      Next
      'Call Source.FieldSetText("FlashMin" , FlashLow)
      'Call Source.FieldSetText("Group" , GasGroupHigh)
      'Call Source.FieldSetText("TempClass" , TempClassHigh)
End Sub

Problems
1. The last 3 statements refuse to compile but they are taken from code i have elsewhere that works fine. Any idea why?
2. The line Set dc=view.getAlldocumentsbykey(item.values(x),True) puzzles me. What does the bit in brackets do? Is it the code to pick the ticked documents?
3. How does the loop work? Doesn't it reset the first document if the Set doc=dc.getfirstdocument command is where it is. But it has to come after the For statement to have a value of x.


0
 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 100 total points
Comment Utility
Source.FieldSetText assumes thatthere is a variable called Source that contains a notesUiDocument.  That is auomatically true for form events (QueryOpen, PostOpen, QuerySave, etc.).  It is not true for a field exiting event.  What you can do is add this:

     Dim ws as new notesUiWorkspace
     Dim Source as notesUiDocument
     Set Source = ws.currentDocument
     Call Source.FieldSetText("FlashMin" , FlashLow)
     Call Source.FieldSetText("Group" , GasGroupHigh)
     Call Source.FieldSetText("TempClass" , TempClassHigh)

This is somewhat sloppy coding technique, but will work.

Set dc=view.getAlldocumentsbykey(item.values(x),True)

This is equivalent to:
Dim key as string
key = item.values(0)
Set dc=view.getAlldocumentsbykey(key,True)

So, your only mystery is, why item.values(0)?  item.values is always an array, even though, in most instances, it is an array with one element.  item.vale1) usually does not exist, unless you have a list field.  So, to extract a plain string, not an array, to use as a key, we get the ARRAY ELEMENT out of values, not the full values array itself.  IN this instance, it probably isn't necessary, as get by key accepts an array anyway.
0
 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 100 total points
Comment Utility
Scratch that last sentence,  forgo, we are using x, not 0.  That helps explain the next part actually...

3. How does the loop work? Doesn't it reset the first document if the Set doc=dc.getfirstdocument command is where it is. But it has to come after the For statement to have a value of x.


You have TWO loops!


For each value of the array in item.values
  process that value
Next

WHat does "process that value" do?

Get all documents matcing the current value.  Then...
While we have not reached the end of the list of matcing documents
   process a document
   get the next document
Loop

Does this make more sense to you now?  item represents a field -- a list field.  We need to process each entry in the list, get all matcing documents, and do something with each document.  So, we end up with a for loop for the item.values, and a while loop for the document collection that matches the item.values(s) key.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 19

Accepted Solution

by:
madheeswar earned 250 total points
Comment Utility
Use the below code:
as it is. Don't amend anything. I think u need more of logics as I see ut code.
Sub Click(Source As Button)
      Dim ws As New notesuiworkspace
      Dim uidoc As notesuidocument
      Dim curdoc As notesdocument
      
      Set uidoc=ws.currentdocument
      Set curdoc=uidoc.document
      
      Dim ss As New notessession
      Dim db As notesdatabase
      Dim view As notesview
      Dim doc As notesdocument
      Dim dc As notesdocumentcollection
      
      Set db=ss.currentdatabase
      Set view=db.getview("2a. Fluids in use")
      
      Dim item As notesitem
      Set item=curdoc.GetFirstItem("Fluids")
      
      k=0
      For x =0 To Ubound(item.values)
            Set dc=view.getAlldocumentsbykey(item.values(x),True)
            Set doc=dc.getfirstdocument
            Dim arr() As String
            Dim arr1() As String
            Dim arr2() As String
            
            While Not doc Is Nothing
                  Redim Preserve arr(k)
                  Redim Preserve arr1(k)
                  Redim Preserve arr2(k)
                  arr(k)=doc.Flash(0)
                  arr1(k)=doc.Group(0)
                  arr2(k)=doc.TempClass(0)
                  k=k+1
                  Set doc=dc.getnextdocument(doc)
            Wend
      Next
      curdoc.FlashMin=arr
      curdoc.Group=arr1
      curdoc.TempClass=arr2
      Call uidoc.refresh
End Sub

Try the above and let us know.

Ur questions has been answered by Qwaletee.

Thanks Qwaletee.
0
 

Author Comment

by:StuartOrd
Comment Utility
Thanks, that's very helpful. I'll try it and let you know. Might be a couple of days as I've got to go away till Sunday.
I keep asking if there are any good books on explaining Lotus Script (something like "Lotus Script for Dummies" or better still "Fred's Teach Yourself Lotus Script in 30 years") but still not found one. If I keep asking questions, qwaletee will have written it! (Well, why not write one - you could)
How do you learn this much detail - Lotus training courses, or what? Despite your excellent help, this is a hard way for me to learn.
0
 
LVL 19

Expert Comment

by:madheeswar
Comment Utility
Initially I am also like you. Slowly, learning front end and backend classes and surfing notes.net, I learned myself. I did not read any books.

And coming for you, u can download red papers from Lotus and u can try to do some examples.

Just try to know what is front end and backend. Where u have to use them and where not.

Front end means all the UI classes.

backend other classes.

U need to know thier methods and properties. What each method and property will do. U have detailed explanation in Domino designer.
I will see, if i have any good links to learn LS.

Best of luck.
0
 
LVL 19

Expert Comment

by:madheeswar
Comment Utility
0
 

Author Comment

by:StuartOrd
Comment Utility
Hi again,

I've found half an hour at lunchtime!

I've tried the code and it runs, but fails to enter any results in the fields as the code exits. It might be because also I didn't see any data building in the arrays as the code cycled around the loops - X was building up but k remained at 0, and arr, arr1 and arr2 all remained blank. I've checked all the field names and they are fine. It does cycle round the For loop the correct number of times, ie x increases up to the number of fluids that were clicked and then it breaks out of the loop, but it's only passing through While once.

Also interesting is its failure to run a second time if, after running once, I go back to the Fluids field, click another name, and navigate awayagain - the Exiting event runs, but starting at the very last line! ie EndSub.

I copied the code exactly as you said apart from correcting as follows
     curdoc.FlashMin=arr
     curdoc.HighestGasGroup=arr1
     curdoc.HighestTempClass=arr2
as I told you wrongly above - these are the correct field names to take the results of the logic we have still to put in when we get over the current hurdle.

Also I need to mention that Flash and FlashMin are number fields at present - I presume we just need to alter to ""Dim arr() As Variant" ?


0
 
LVL 19

Expert Comment

by:madheeswar
Comment Utility
check whether item.values have values or not? And check whether the first column is sorted in "2a. Fluids in use" view. First column should have values of Fluids.

If the first column is not sorted, u won't get the values. Please check it and let us know.
0
 
LVL 19

Expert Comment

by:madheeswar
Comment Utility
For Flash and FlashMin, change them to text and let the Dim arr() as string.

Don't alter my code.

Just as I said before, thier may be problem in ur view first column. Check it out and let me know.

I will be available today and will be back on Monday.

Best of Luck till then.
0
 

Author Comment

by:StuartOrd
Comment Utility
The only trouble with making these variables into strings is that I need to find the lowest value of Flash for the selected fluids, and that can be a negative number as is °C temperature scale.

Item.values does not seem to be getting values - it' not shown in the variables list in the debugger. The code now falls over at "For x =0 To Ubound(item.values)" with "object variable not set" so I suppose that makes sense.

I've been looking at this first column and see that it's changed since I first wrote this question - sorry - maybe that's causing the problem. In the view "2a. Fluids in use" the first column is the field "Name" being the fluid name. However the listbox that we are ticking is now being built from code in another question (http://www.experts-exchange.com/Applications/Email/Lotus_Notes/Q_20797502.html) and uses a listbox with selection formula "@DbColumn("" : "NoCache"; ""; "2a. Fluids in use"; 11)" where column 11 has the formula:
DEFAULT$3:=everything;
pad := @Repeat(" "; 40);
list := Name : @Text(Flash) : Group : TempClass;
hidden := @Implode(list;",");
DEFAULT $3 :=@Left(Name+pad;25)+@Left("FP="+@Text(Flash)+pad;11)+@Left("Gas gp="+Group+pad;17)+("Temp classn="+TempClass);
visible+ "|" + hidden
Don't ask me how we got the odd default bits - it happened and it works!

The beauty of this code is that it displays a nice list, for the users, of the fluids that they can select from, and so I left the current question problem for a while to get it sorted out. However it's altered my question a bit.

As a trial, I've changed the selection back to "@DbColumn("" : "NoCache"; ""; "2a. Fluids in use"; 1)"  but alas it didn't work; there was nothing for item.values in debugger and it fell over at the same place. I've also tried making the field Flash on its entry form into Text but it made no difference to the behaviour.
S
0
 
LVL 19

Expert Comment

by:madheeswar
Comment Utility
The code provided willlist all the fluids. Am I correct?

Create a new view, place this coe in the first column and sort it.
Now my code should work. Why ur not getting item.values is, did u selected fluids from the list?
Check the field name again. the multi selction values from the current open document (should be in edit mode) will give the values for item.values

Once it gets the values, I am looping(item.values is the key for doc collection) and getting the values from those documents.

Should not be a problem with my code. Ur missign some thing in ur views or field names.

Basic is my logic works fine.

Check it out and let me know.
0
 

Author Comment

by:StuartOrd
Comment Utility
Hi madheeswar,
It works! You were right. I moved the complex column code over to the left and hid it, sorted it, and corrected my mistake in the code whilst thrashing around, and hey presto, all is fine.
Many thanks, well deserved points! I now need to code the logic to pull out the correct worst case data, but hopefully I'll do that unaided....
Regards,
Stuart
0
 

Author Comment

by:StuartOrd
Comment Utility
Just adjusting having reviewed the answers....
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now