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.

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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

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

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?

ASKER CERTIFIED SOLUTION

madheeswar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

varvoura

ASKER

thx Maddy, Will test and advise.

Regards