Replace values in a range of documents using lookup list

HCL Domino Domino Forum
1

Hi,

I have a large number of documents which need to be updated. I need to update all documents which contain this field. The value that this filed is updated to is conditional.

I was going to use an IF statement , see below.

If(MarketingUnit = “Swiss”;

@SetField(“AccountCode”;“123”);

MarketingUnit = “France”;

@SetField(“AccountCode”;“1234”);

etc.

This works except for the fact there are hundreds of different ac codes. I was hoping that it would be possible to create some sort of look up table. i’ve checked the helpfiles but no look so far. Then my code would look at the table and extract the corresponding value

Is this possible?Can any one help?

Thanks,

Morgan

2

Subject: Replace values in a range of documents using lookup list

You could create a profile document for each value, and then do something like…

field AccountCode := @getProfileField( ; “accountCode”; marketingUnit);

where = form of the profile document

“accountCode” is the field name in the profile document that codes the accountcode value to set to

John

3

Subject: Replace values in a range of documents using lookup list

I would do this with Lotus Script. This is how I would do it:

Create a form called ACLookup

Two fields at least: MarketingUnit and AccountCode

Create a view with two columns:

col 1: MarketingUnit

col 2: AccountCode

sort on col1

Next, the hard part…populate the ACLookup documents with the hundreds of Account Codes, one per Marketing Unit. You can import this from a spreadsheet, if you want to try that I can help you with it as well.

Once these docs are created, confirm they show up in this new view. Let’s call that view “(ACLookup)”

Now your agent to populate the large number of documents you want to update with the proper ac code:

(this is without me testing, so if I make a mistake, sorry)

Agent Name = “Populate AC Codes”

script:

Initialize

On Error Resume Next

Dim s As New notessession

Dim db As notesdatabase

Dim view As notesview

Dim dc As notesdocumentcollection

Dim lookupdoc As notesdocument, doc As notesdocument

Dim counter As Long

counter = 0

Set db = s.currentdatabase

Set view = db.getview(“(ACLookup)”)

Set dc = db.unprocesseddocuments

Set doc = dc.getfirstdocument

Do While Not (doc Is Nothing)

Set lookupdoc = view.GetDocumentByKey(doc.MarketingUnit(0))

doc.AccountCode = lookupdoc.AccountCode(0)

If doc.save(True,True) Then

counter = counter + 1

print "Updating " + cstr(counter)

End If

Set doc = dc.GetNextDocument(doc)

Loop

Msgbox “finished updating " + Cstr(counter) + " documents”

end sub

I think that will do it. Give it a try.

  • Matt
4

Subject: RE: Replace values in a range of documents using lookup list

Hi Matt,

I have tried to implement the suggestions outlined above. I have imported all the forms via a spreadsheet and created the agent.

When I run the agent no documents are updated and this is confirmed by the counter.

Do you have any ideas what the problem may be.

thanks for your help,

Morgan

5

Subject: RE: Replace values in a range of documents using lookup list

Morgan,

Look in the database. Open the view you created. If you put the parentheses around the name, you will have to hold down CTRL-SHIFT while clicking on VIEW, GOTO and you will see the view you created. Open it. What is in the first column? It should be the MarketingUnit value. And this column HAS to be sorted.

Next, turn on the debugger, and look at what happens after the first getdocumentbykey call. The lookupdoc object should be populated. If it is, open the ITEMS and look for the item called “AccountCode” and see what the value is. It should have the account code you are trying to pull back for that particular MarketingUnit.

Let me know if this gets you past your problem.

  • Matt
6

Subject: RE: Replace values in a range of documents using lookup list

Hi Matt,

Thanks for your help, the problem was a typo in the name of the View. typical.

Its fixed and works perfectly.