Conceptual help on field value replacement

HCL Domino Domino Forum
1

Okay- I have a database with ~50,000 documents in it. I need to loop though all of them at make sure that if State is a, then SalesRep should be b - do I want to right 50 if statements in LS or do a lookup to a documentbykey - w/ the right info- any idea on which is more efficient?

2

Subject: Conceptual help on field value replacement

I’d create a view, selecting State = a and SalesRep != b, then run a simple agent to change it.

3

Subject: RE: Conceptual help on field value replacement

I might create a list with the index being the state, and the value foe the index being the sales rep, then loop thru the 50,000 documents, which should not take that long.

You then have the agent ready the next time they change the sales rep assignment.

John

4

Subject: Conceptual help on field value replacement

This is the perfect place to use a List. A List is sort of like an array, except that it uses unique tag strings to identify the members. In this case, if you use the State as the list tag and the SalesRep as the list data, setting the value in the document is as simple as this:

doc.SalesRep = repList(doc.State(0))

Setting up the list should be easy enough – just go through the keyword documents:

Dim repList List As String

Set repView = db.GetView(“<sales_rep_view>”)

Set repDoc = repView.GetFirstDocument

Do Until repDoc Is Nothing

repList(repDoc.State(0)) = repDoc.SalesRep(0)

Set repDoc = repView.GetNextDocument(repDoc)

Loop

To avoid unnecessary updates, you’ll want to check the salesrep value before making the changes in the main document, of course:

If (doc.SalesRep(0) <> repList(doc.State(0))) Then

doc.SalesRep = repList(doc.State(0))

Call doc.Save(True,False)

End If

5

Subject: RE: Conceptual help on field value replacement

Thanks to all- I was leaning towards the view- but I like the list. Thanks.

6

Subject: RE: Conceptual help on field value replacement

Stan,

you are starting to give me a complex. it seems after i post, you follow up with a much better explanation.

i guess that is why you are the man,

cheers, John

7

Subject: RE: Conceptual help on field value replacement

I think we start at about the same time, but my seventy wpm (that’s words per month) typing speed gets my responses up, well, just a little bit later than yours.

8

Subject: RE: Conceptual help on field value replacement

So- here is the code if anyone is interested- it worked like a charm. I am writing to the log each transaction -looking for any issues.

Sub Initialize

Dim db As New NotesDatabase( "Phoenix/HQ/mycompany","live\thisdatabase.nsf" )

Dim view As NotesView

Dim doc As NotesDocument

Set view = db.GetView( "THSJob" )

Dim counterFix As Integer

CounterFix = 0

Dim CounterGood As Integer

CounterGood =0

Set RepDoc = view.GetFirstDocument

Dim RepList List As String

' Put some elements in the list.

replist("AK") = "WENW"

replist("DC") = "EAAT"

Replist("AK")="WENW"

Replist("AL")="EASE"

Replist("AR")="WEMS"

Replist("AZ")="WESW"

Replist("CA")="WESW"

Replist("CO")="WEMT"

Replist("CT")="EANE"

Replist("DC")="EAAT"

Replist("DE")="EAAT"

Replist("FL")="EASE"

Replist("GA")="EASE"

Replist("HI")="WESW"

Replist("IA")="WEMW"

Replist("IL")="EAGL"

Replist("IN")="EAGL"

Replist("KS")="WEHS"

Replist("KY")="EAGL"

Replist("LA")="WEMS"

Replist("MA")="EANE"

Replist("MD")="EAAT"

Replist("ME")="EANE"

Replist("MI")="EAGL"

Replist("MN")="WEMW"

Replist("MS")="EASE"

Replist("MT")="WENW"

Replist("NC")="EAAT"

Replist("ND")="WEMW"

Replist("NE")="WEMW"

Replist("NH")="EANE"

Replist("NJ")="EANE"

Replist("NM")="WEMT"

Replist("NV")="WESW"

Replist("NY")="EANE"

Replist("OH")="EAGL"

Replist("OK")="WEMS"

Replist("OR")="WENW"

Replist("PA")="EANE"

Replist("RI")="EANE"

Replist("SC")="EAAT"

Replist("SD")="WEMW"

Replist("TN")="EAAT"

Replist("UT")="WEMT"

Replist("VA")="EAAT"

Replist("VT")="EANE"

Replist("WA")="WENW"

Replist("WI")="WEMW"

Replist("WV")="EAAT"

Replist("WY")="WEMT"



Do Until RepDoc Is Nothing	

	

	On Error Resume Next

	

	If (Repdoc.Salesrep(0) <> replist(Repdoc.RFWSapplState(0))) Then

		RepDoc.SalesRep=Replist(Repdoc.RFWSapplstate(0) )

		Call Repdoc.Save(True,False)

		CounterFix =CounterFix+1

		Print "Working on Job Number " & RepDoc.JobNumber(0) & " is the "& Cstr(CounterFix) & " I've worked on."

	Else

		CounterGood = CounterGood +1

		Print "No need to work on Job Number " & RepDoc.JobNumber(0) & " it is the "& Cstr(CounterGood) & " I've good one I am working on."

	End If

	Set RepDoc=View.GetNextDocument(RepDoc)

Loop

Print "Total Records fixed is:  " & Cstr(Counterfix)

Print "Total Records skipped as they were correct:  " & Cstr(CounterGood)

End Sub