Trying to find the best way to go about this. I have an excel sheet with a list of 2300 zip codes in the first column. The other columns have the State and other info. I need to use these zip codes to search through documents in a notes database. If it finds a matching zip code in the document then change the necessary information in that document and it’s child documents using the info in the other columns.Or use formula? Create a view and only display the documents that have the zipcodes in the huge list of excel and run an agent to changes the fields on selected documents?
I’m just not sure how to go about it. Can someone point me in the right direction?
First I would setup a form to hold each line of the Excel table and a view to display the information (sorted by Zip Code)
Then import the excel information into the database using the form.
Then I would write an agent to go through “every document” and get it’s Zip code.
If the zip code is set, on that document (call it checkDoc). then look for that zip in your new view and find the matching document (zipDoc) and then update checkDoc with the ZipDoc information.
Will this just be a one time data scrub or on going?
If it’s an ongoing thing, you might want to set a flag of the date and time of the ZipDoc modified date on the document to indicate when it was last done. If the flag has the same date as ZipDoc you can skip it otherwise, update the information.
The other question is after your agent has updated the information, can the user modify it and make it invalid again. If so, then you might not want to use the flag.
Why not save your excel file as a .csv or .wk4 (Lotus 123), import the file into a notes database (call it the zip code db) with a simple form and sorted view and do a dblookup to get the info.
If its only a one time deal, this might be the easiest way. If the update needs to run daily (which I doubt), then write an agent to import the excel file and update the documents.
This will be a one time run agent. Some zip codes may not exist and if that’s the case then skip over them. Only update the ones it finds in the database that match in the excel file and change the information in that document and child documents
your question has been answered, import the excel file into a notes db then do lookups to the db to do your validations, easy. It’s much easier and quicker to do the lookups to a notes view then it is to write code to do the lookups to an excel file.