Solved

Added new view from other database platform system using oledb connection w/Uselsx "*lsxlc" need to add this view to existing script

Posted on 2013-11-04
21
537 Views
Last Modified: 2013-12-18
i currently have a script that connects to a different platform and retrieves list of topics and populates that list in a drop down field within my form.  Currently today there is only 1 view being accessed from that other platofrm.  Now there is another view that was created and the populated field on my form needs to use both views but 1 or the other depending upon a field value in my form.  Here is the existing script:
Sub Postopen(Source As Notesuidocument)
      
      Dim ses As New LCSession
      ses.ClearStatus    
      
      Dim doc  As NotesDocument
      Set doc = Source.Document
      
      
      Dim con As New LCConnection("oledb")
      
      With con
            .Provider = "SQLOLEDB"
            .Server = "ENYRSRSQL"
            .Database = "EPT"
            .Metadata = "ActiveProjects"
            .UserID = "3PTR3@d"
            .Password = "3PTR3@d"
      End With
      
      On Error Resume Next
      con.Connect
      If Err = True Then    
            Messagebox  "Unable to Connection failed with error " & Err & ": " & Error
            Exit Sub
      End If
      
      Dim MyFldList As New LCFieldList
      Dim MyFld As LCField    
      Dim SQL As String
      Dim MyResult As String
      
      SQL = "SELECT RTRIM(Project)+' - '+RTRIM(Title) FROM ActiveProjects ORDER BY Project DESC"
      con.Execute SQL, MyFldList
      
      Set MyFld = MyFldList.GetField(1)
      
      While (con.Fetch(MyFldList) > 0)
            MyResult = MyResult & MyFld.Text(0) & Chr(10)
      Wend
      
      Call Source.FieldSetText("EVDSqlTitle",MyResult)
      
      'Messagebox MyResult
      
      con.Disconnect
      Set MyFldList = Nothing
      Set MyFld = Nothing
      Set con = Nothing
      Set ses = Nothing      
      
End Sub

the new view is called ActiveProjectsMEIPASS.  The original view is called ActiveProjects as you can see in the script above.  If my existing field EVDproject = MEIPASS value on the form then I want to use the new view on the other platform and retrieve projects only assosciated with MEIPASS other wise use the existing view ActiveProjects and bring in all non MEIPASS projects.
got bits and pieces but I can't the if structure.  
Thank you !
0
Comment
Question by:pratigan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
21 Comments
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 500 total points
ID: 39623728
With con
            .Provider = "SQLOLEDB"
            .Server = "ENYRSRSQL"
            .Database = "EPT"
            .Metadata = "ActiveProjects"
            If doc.EVDproject(0)="MEIPASS" Then
                   .Metadata = "ActiveProjectsMEIPASS"
            End If
            .UserID = "3PTR3@d"
            .Password = "3PTR3@d"
      End With

Is this what you meant?
0
 
LVL 4

Author Comment

by:pratigan
ID: 39624656
That's exactly what I meant.  Thank You Sjef.  I will code and test that throughout the day today and get back to you.
Thank you !
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39624762
:-) After all the projects you did and the questions we answered together I'm quite sure you could have done this by yourself.

Eh, or is this maybe a different user pratigan?
0
Independent Software Vendors: 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!

 
LVL 4

Author Comment

by:pratigan
ID: 39624964
no it's the right one... lol That .metadata interface and the different platform were throwing me for a loop.  I didn't figure it to be as easy as it was.
Thanks !
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39624985
Study the With statement in the Help database, maybe that takes some of your confusion away.
0
 
LVL 4

Author Comment

by:pratigan
ID: 39634412
I'm thinking that I have to move the code from its current location.  Currently it is in the Postopen event and therefore doesn't change the values in the list when I select MEIPASS.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39634503
If the field containing the condition can be changed dynamically, PostOpen isn't any good indeed. You have to put it in the QueryRecalc event, plus you have to select "Recalculate form when value changes" and for the field with the list you have to select "Refresh options when form is refreshed". Well, the options are named differently but you'll see soon enough what I mean. ;-)
0
 
LVL 4

Author Comment

by:pratigan
ID: 39639003
the post calc field is working from the start of the form only.  When I select MEIPASS first thing and then go to title field, only MEIPASS projects are listed.  Good.  but If I go back and change the radio button from MEIPASS to Evolution and then select the title field, the MEIPASS projects still show listed.  Also if Evolution first then tht list remains on radio button change.
the radio button is set to Refresh fields on keyword change
the title field(the one that gets generated from the different view in the script) is set to Refresh choices on document refresh.
aggravating ..... lol
0
 
LVL 4

Author Comment

by:pratigan
ID: 39639135
I'm thinking the best place for this script is in the Exiting event from the radio button field that drives the title lists further down in the form.  this way the script would only be executed once.  With the script in the POSTrecalc or queryrecalc the script gets executed everytime the cursor changes fields.
Your thoughts ?
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39639136
> the title field(the one that gets generated from the different view in the script)
What type of field is that? Is it a dialog list or other type of selection field? If it is not, why not?
0
 
LVL 4

Author Comment

by:pratigan
ID: 39639168
yes the title feld is a Dialog List field that gets generated using formula of choice and th selection in the formula field is EVDSqlTitle (coming from th script in the form postrecalc or queryrecalc)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39639172
Usually, Exiting is a place that doesn't excite me. It has a serious drawback: if the user, while in the field, types Ctrl-S and then Ctrl-F4, the Exiting event never fires. Both Recalc events do fire when the user tries to save the document.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39639175
Does the Title field have the option Refresh choices on document refresh set? (or similar text)
0
 
LVL 4

Author Comment

by:pratigan
ID: 39639187
yes it does.  

I'm just thinking the exiting to have the script only execute once.  as a recalc it executes everytime the cursor changes field.  It's not that big of a form so it's not that big of overhead
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39639276
> as a recalc it executes everytime the cursor changes field
I don't understand. You should not have the auto-recalc option of the form enabled, that's overkill. Just for the Title field, enable Refresh choices on document refresh.
0
 
LVL 4

Author Comment

by:pratigan
ID: 39639321
thats the problem.  if Iremove automatically refresh fields from the form properties, then the title list is blank.  I have the project field set to refresh fields on keyword change radio button containing 2 values, evolution or MEIPASS)  the title button is set to refresh choices on document refresh.
The strange thing is, when I go a few fields past the title field and then go back ad change the radio button to MEIPASS or evolution, then go back to the title field, the values have changed to reflect the correct values for the project radio button seection.  Meaning the project field is the first field inthe form.  It defaults at Evolution.... two fields down the tile field reflects Evolution projects in the list.  go 2 more fields down an then back up to chane the project field to MEIPASS.  go down to the title field now ad the projects listed are MEIPASS titles.  only if I go 1 or more fields past the title do they change to a correct list.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39639322
There is also an Onchange event, but I'm not in favour of that one either, but you might try it. I assume it gets triggered immediately after changing the value of the field.
0
 
LVL 4

Author Comment

by:pratigan
ID: 39639352
it is workable as is.  I will try the Onchange.  I've used it before in other forms and it worked nicely.
0
 
LVL 4

Author Comment

by:pratigan
ID: 39639355
Than You for your help Sjef.  very much appreciated.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39639377
If you can avoid the use of the automatic update, you should, and almost at all costs. It'll execute the code all the time. I'd say that in 99% of all cases you can do without it.

The fields are evaluated top to bottom and left to right. So if the list of choices for field2 depends on field1, field1 should be placed before field2 on the form. If choices are to be calculated using LotusScript, you may have to put that code in the QueryRecalc event on the form, and the resulting list of choices in a field that is NOT on the form. Why not? The form always fiddles with this type of field, plus I'd suggest to make that field Computed for Display. And since you cannot give a CfD field a value from LotusScript, you don't add it to the form. Instead, you use ReplaceItemValue to add it to the document, and then you set the SaveToDisk property of the field to False.
0
 
LVL 4

Author Comment

by:pratigan
ID: 39639423
I will take heed of this.  Interesting layut for the computerd for display field.  I wll try that as well.
Thank You Agan !!
I totally appreciate your help.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

615 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