Avatar of varvoura
varvoura
 asked on

Urgent Help

Hi all,

I need some clarification on the following code. What I am trying to do is to check against a multivalue field and explode its list then use the evaluate command to evaluate and use the variable in my Lotusscript and odbc query.

Here's what I am trying to do, please let me know if it works,

fielda - multivalue field on a configuration document which contains may codes on some documents and one code on other documents, for example, 10, c, 34,....

in my lotusscript, I will get the value of this field:

getfielda=doc.fielda(0)

Then I will declare a variable

fieldacontent=("@explode(getfielda,doc)
fieldaeval=Evaluate(fieldacontent)

To what I understand is that I can now use fieldaeval in my script to check whether an imported field has a value which is contained in fieldaeval so that if it does, we don't import the document.

qry.sql = select fieldb, fieldc, fieldd, fielde, from testtablea Order by fieldd " and NOT +fieldaeval+ contains '" & fielde & "'"

Where fielde is the field that i want to make sure that the value in it is not equal to any of the values in fielda which is on the configuration document.
Will this work? is the syntax of at the end of the query correct ? NOT .........

Thank you all in advance.

Lotus IBM

Avatar of undefined
Last Comment
varvoura

8/22/2022 - Mon
varvoura

ASKER
..
Where fielde is the checking field.
If the value in fielde is contained in in the fieldeval variable then we won't add this document to the result set which we are importing

madheeswar

Instead do one thing...

dim item as notesitem
sey item=doc.getfirstitem("fielda")

for x=0 to ubound(item.values)
msgbox item.values(x)
'Now check using If condition...

next
SOLUTION
Sjef Bosman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
varvoura

ASKER
OK, I am assuming that we're getting rid of the evaluate statement and replacing it with a for or a forall loop.

let me see what I can put together and I'll post again soon

Thank you both

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
varvoura

ASKER
Here's a portion of the script which I modified using a for loop, just to give you an idea on what I am trying to do. Will this for loop work to restrict the import to those documents which do not exist in fielda list??


Set Qry.Connection = Con
                  Qry.SQL =  " select  testtable_fieldb, testtable_fieldc, testtable_fieldd, testtable_fielde, testtable_fieldf, " +_
                  " table2_fieldb, table2_fieldz, table2_fieldy " + _
                  " from testtable_file left outer join table2_master on ((testtable_fieldb = table2_fieldb)" + _
                  " order by testtable_fieldb"
                  
                  Set item=doc.getfirstitem("fielda")
                  
                  For x=0 To Ubound(item.values)

                  Msgbox item.values(x)
                        
                  Qry.SQL = Qry.SQL & " and testtable_fielde != '" & item.values(x) & "'"
                  
                  Next

                  
                  Set Res.Query = Qry
                  Res.Execute
                  
                  RetCode% = Res.FirstRow    


                  If RetCode% = False Then
                        Msgbox "Can't connect to datasource
                        RetCode% = Res.Close( DB_CLOSE )
                        RetCode% = Con.Disconnect
                        Exit Sub
                  End If
                  

                  Do While RetCode% = True
                        
                        Set view = db.GetView( "testview" )
                        keys( 1 ) = code(0) + Cstr( Res.GetValue("fieldc") )
                        Set updatedoc = view.GetDocumentByKey( keys )                        
                        
 
                        
                        If updatedoc Is Nothing Then
                              Set newdoc = New NotesDocument(db)          
                              newdoc.form = "newdocument"
                              newdoc.field1 = Cstr( Res.GetValue("testtable_fieldb") )
                              newdoc.field2 = Cstr( Res.GetValue("testtable_fieldc") )
                              newdoc.field3 = Cstr( Res.GetValue("testtable_fieldd") )
                              ......
                              Call newdoc.ComputeWithForm(False,False)
                              Call newdoc.save (True, True)
                              
                        Else
                              
                                    updatedoc.field1 = Cstr( Res.GetValue("testtable_fieldb") )
                              updatedoc.field2 = Cstr( Res.GetValue("testtable_fieldc") )
                              updatedoc.field3 = Cstr( Res.GetValue("testtable_fieldd") )

                              Call updatedoc.ComputeWithForm(False,False)  
                              Call updatedoc.save (True, True)
                              
                        End If                  
                        

                        ...All the remaining bit of the script


varvoura

ASKER

Set item=doc.getfirstitem("fielda")
               
            For x=0 To Ubound(item.values)

               Msgbox item.values(x)
                   
               Qry.SQL = Qry.SQL & " and testtable_fielde != '" & (x) & "'"
               
               Next

I am trying to check against the value of the item on each iteration, can i not just using the iteration variable?
varvoura

ASKER
Here's my modified script. I only need some minor help.
The current script looks at the field in the configuration document and if any of its values equals to the code field in brought by the result set then the document isn't imported. The problem with mine now is because I used a for loop, the script seems to create a new document in the database for each value I have in the field which is in the configuration document.
For example, if fielda in the configuration document has values 10, 20, 30 and the code field in the result set doesn't have any of this number, then the same document is created 3 times in the database when I am creating a document.

......Declaration here      
Set Qry.Connection = Con
      Qry.SQL = " select testtable_fieldb, testtable_fieldc, testtable_fieldd, testtable_fielde, testtable_fieldf, " +_
      " table2_fieldb, table2_fieldz, table2_fieldy " + _
      " from testtable_file left outer join table2_master on ((testtable_fieldb = table2_fieldb)" + _
      " order by testtable_fieldb"


                  
                  
                  Qry.QueryExecuteTimeOut = 300    
                  Set Res.Query = Qry
                  Res.Execute
                  
                  RetCode% = Res.FirstRow    
                  
 
                  If RetCode% = False Then
                        Msgbox "Can't connect to data source
                        RetCode% = Res.Close( DB_CLOSE )
                        RetCode% = Con.Disconnect
                        Exit Sub
                  End If
                  
                  
                  Do While RetCode% = True
                        
                        Set view = db.GetView( "testview" )
                        keys( 1 ) = testfield1(0) + testfield2(0)
                        Set updatedoc = view.GetDocumentByKey( keys )      
                        
'NEW CHANGES THAT I MADE FOR THE CHECKING

                        Set item = doc.getfirstitem("list")
                        For x = 0 To Ubound(item.values)

(WHERE FIELD B HAS THE CODE THAT I NEED TO CHECK AGAINST. THIS FIELD HAS ONLY ONE CODE PER CUSTOMER)

      If (item.Values(x) <>  Cstr( Res.GetValue("testtable_fieldb")))  Then
                              
         If updatedoc Is Nothing Then

          Set newdoc = New NotesDocument(db)          
          newdoc.form = "formname"
          newdoc.fieldc = Cstr( Res.GetValue("testtable_fieldc") )
          newdoc.fieldd = Cstr( Res.GetValue("testtable_fieldd") )
          newdoc.fielde = Cstr( Res.GetValue("testtable_fielde") )
          newdoc.fieldn = Cstr( Res.GetValue("testtable_fieldn") )
          Call newdoc.ComputeWithForm(False,False)  
          Call newdoc.save (True, True)


                                          
      Else
                                          

      updatedoc.fieldc = Cstr( Res.GetValue("testtable_fieldc") )
      updatedoc.fieldd = Cstr( Res.GetValue("testtable_fieldd") )
      updatedoc.fielde = Cstr( Res.GetValue("testtable_fielde") )
      updatedoc.fieldn = Cstr( Res.GetValue("testtable_fieldn") )
      Call updatedoc.ComputeWithForm(False,False)  
      Call updatedoc.save (True, True)
                                          
      End If      
End If
Next      

The problem I think that the for loop is going to go through all the elements in the fielda and it seems to create the entire set of document in the database for each element(if fielda has 3 items,then the same document is created 3 times). How can I go about fixing this problem where no matter how many numbers are in fielda then if the number in the number in testtable_fieldb <> any of the values in fielda then the document is created only once if it does not exist and updated if it does exist, otherwise not created or updated.

MY SCRIPT SEEMS TO BE DOING THE RIGHT THING(AS PER MY DESCRIPTION) IF I HAVE ONLY ONE VALUE IN FIELDA. FOR EXAMPLE, IF FIELDA HAS ONLY VALUE "10" AND THE TESTTABLE_FIELDB HAS "10" THEN THE DOCUMENT IS NOT IMPORTING WHICH IS GREAT, BUT IF FIELDA HAS "10" AND FIELDB HAS "20" THEN THE DOCUMENT IS IMPORTING.

Your help with this is much appreciated.

                  ........rest script here
                  
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
madheeswar

......Declaration here    
Set Qry.Connection = Con
     Qry.SQL = " select testtable_fieldb, testtable_fieldc, testtable_fieldd, testtable_fielde, testtable_fieldf, " +_
     " table2_fieldb, table2_fieldz, table2_fieldy " + _
     " from testtable_file left outer join table2_master on ((testtable_fieldb = table2_fieldb)" + _
     " order by testtable_fieldb"


               
               
               Qry.QueryExecuteTimeOut = 300    
               Set Res.Query = Qry
               Res.Execute
               
               RetCode% = Res.FirstRow    
               
 
               If RetCode% = False Then
                    Msgbox "Can't connect to data source
                    RetCode% = Res.Close( DB_CLOSE )
                    RetCode% = Con.Disconnect
                    Exit Sub
               End If
               
               
               Do While RetCode% = True
                   
                    Set view = db.GetView( "testview" )
                    keys( 1 ) = testfield1(0) + testfield2(0)
                    Set updatedoc = view.GetDocumentByKey( keys )    
                   
'NEW CHANGES THAT I MADE FOR THE CHECKING

                    Set item = doc.getfirstitem("list")
                    For x = 0 To Ubound(item.values)

(WHERE FIELD B HAS THE CODE THAT I NEED TO CHECK AGAINST. THIS FIELD HAS ONLY ONE CODE PER CUSTOMER)

     If (item.Values(x) <>  Cstr( Res.GetValue("testtable_fieldb")))  Then
                         
        If updatedoc Is Nothing Then

        goto creaatedoc

                                   
     Else
                                   

     updatedoc.fieldc = Cstr( Res.GetValue("testtable_fieldc") )
     updatedoc.fieldd = Cstr( Res.GetValue("testtable_fieldd") )
     updatedoc.fielde = Cstr( Res.GetValue("testtable_fielde") )
     updatedoc.fieldn = Cstr( Res.GetValue("testtable_fieldn") )
     Call updatedoc.ComputeWithForm(False,False)  
     Call updatedoc.save (True, True)
                                   
     End If    
End If
Next  
creaatedoc:
Set newdoc = New NotesDocument(db)          
         newdoc.form = "formname"
         newdoc.fieldc = Cstr( Res.GetValue("testtable_fieldc") )
         newdoc.fieldd = Cstr( Res.GetValue("testtable_fieldd") )
         newdoc.fielde = Cstr( Res.GetValue("testtable_fielde") )
         newdoc.fieldn = Cstr( Res.GetValue("testtable_fieldn") )
         Call newdoc.ComputeWithForm(False,False)  
         Call newdoc.save (True, True)
 
use the above...
varvoura

ASKER
Can it be that simple?

Just to clarify?Only because I need this in early morning and I have no test environment at home

 Set item = doc.getfirstitem("list")
  For x = 0 To Ubound(item.values)
 If (item.Values(x) <>  Cstr( Res.GetValue("testtable_fieldb")))  Then
                     
        If updatedoc Is Nothing Then

        goto creaatedoc

Else
                               
     updatedoc.fieldc = Cstr( Res.GetValue("testtable_fieldc") )
     updatedoc.fieldd = Cstr( Res.GetValue("testtable_fieldd") )
     updatedoc.fielde = Cstr( Res.GetValue("testtable_fielde") )
     updatedoc.fieldn = Cstr( Res.GetValue("testtable_fieldn") )
     Call updatedoc.ComputeWithForm(False,False)  
     Call updatedoc.save (True, True)
                                   
     End If    
End If
Next  

creaatedoc:
Set newdoc = New NotesDocument(db)          
         newdoc.form = "formname"
         newdoc.fieldc = Cstr( Res.GetValue("testtable_fieldc") )
         newdoc.fieldd = Cstr( Res.GetValue("testtable_fieldd") )
         newdoc.fielde = Cstr( Res.GetValue("testtable_fielde") )
         newdoc.fieldn = Cstr( Res.GetValue("testtable_fieldn") )
         Call newdoc.ComputeWithForm(False,False)  
         Call newdoc.save (True, True)


What do you mean by use the above...? Do I use the update section after the create doc?This will take it back to what exactly I am doing.

What I am understanding is that you're checking against the field, if item.value(x) <>cstr(getvalue("testtable_fielde) then you go and create the document in create document sub, else you update.
But then you've got the Next, which supposedly finds that a document is created and won't create again.
Is this correct??

Thanks heaps Maddy, I really appreciate you trying to help





madheeswar

I saw the If condition and following document creation. Based on those lines, I have modifed your code.

Since u need to created the document once, then I am exiting from the Do While loop and going to created doc once and exit out.

with rgds,
maddy
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
varvoura

ASKER
I want each document that does not exist in the database to be created once if the value of tabletest_fielde NOT equal to any of the values of the fielda which is the multivalue configuration document field.

Is this what you understood from my explanation above?

varvoura

ASKER
That's fine maddy, looking at it again, I understand what it is doing.

Thanks again for your help
varvoura
varvoura

ASKER
Maddy,

I think that there's a problem here, I don't want to exit the do while loop, I want to loop through the entire result set and create each document with each loop as long as it does not exist in lotus notes otherwise update it.
The only new condition that I need to add is to check whether or not this document that I am about to create or update has its (tabletest_fielde) which is suppose to be a field on each record in result set does not have the same value as any of the values that are found in the fielda of the configuration document (doc.fielda) or I referred to it above as doc.

Are we still on the same track?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
varvoura

ASKER
thx Maddy, Will test and advise.

Regards