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
530 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
  • 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

706 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

13 Experts available now in Live!

Get 1:1 Help Now