BulldogMike
asked on
Remove fields from a Database - search and replace
I have a database called the required fields DB. It contains 400 records (based on template names) and each record contains a list of up to 50 field names.
I want to create a program to loop through the database and remove each field named field1 and field2 (or change their names to "") for each record in the DB. How do I approach this situation.
Thank You
I want to create a program to loop through the database and remove each field named field1 and field2 (or change their names to "") for each record in the DB. How do I approach this situation.
Thank You
How? Very carefully...
Create an agent to run from the actions menu.
The below is LotusScript to remove the fields.
The comments should roughly tell you what it does.
The below is LotusScript to remove the fields.
The comments should roughly tell you what it does.
Dim session As New NotesSession
Dim db As NotesDatabase
Dim collection As NotesDocumentCollection
Dim doc As NotesDocument
Dim item As NotesItem
Set db = session.CurrentDatabase
'set collection to contain all documents in the database
Set collection = db.AllDocuments
'tell doc to hold the first document from our collection of all documents
Set doc = collection.GetFirstDocument()
While Not(doc Is Nothing)
'Check if there is a field of the required name on our document
If doc.HasItem( "field1" ) Then
'there is, do get a hold of the field
Set item = doc.GetFirstItem( "field1" )
'remove the field
Call item.Remove
'save our changes
Call doc.Save( False, False )
end if
'check for the second field
If doc.HasItem( "field2" ) Then
Set item = doc.GetFirstItem( "field2" )
Call item.Remove
Call doc.Save( False, False )
end if
'get the next document from our collection
Set doc = collection.GetNextDocument(doc)
Wend
A more serious answer: are you sure you want to do that? And why? Do you want to save space in the databases? You could save up to 2% per database I assume.
At least make backups of all templates involved, for if you delete one field too many, a disaster may have been created. And what if those templates or databases are interrelated, and users start to use them, and all relations are wrong?
Next issue: do you use replication? If you loop through all databases, and there are many updates, ALL documents will be updated, leading to long replications per database. Plus, if there are people who rely on the last modification time, they see ALL documents modified (marked unread).
So I'd say, if you go through with this, don't loop through them all, but make a button or action per template.
Here's a recipe (intended for LotusScript):
- do a db.Search per database on @IsAvailable(field1) || @IsAvailable(field2)
- loop through the collection found
- per document, use doc.RemoveItem("field1") to remove fields one by one
- save the document
- and go to the next
At least make backups of all templates involved, for if you delete one field too many, a disaster may have been created. And what if those templates or databases are interrelated, and users start to use them, and all relations are wrong?
Next issue: do you use replication? If you loop through all databases, and there are many updates, ALL documents will be updated, leading to long replications per database. Plus, if there are people who rely on the last modification time, they see ALL documents modified (marked unread).
So I'd say, if you go through with this, don't loop through them all, but make a button or action per template.
Here's a recipe (intended for LotusScript):
- do a db.Search per database on @IsAvailable(field1) || @IsAvailable(field2)
- loop through the collection found
- per document, use doc.RemoveItem("field1") to remove fields one by one
- save the document
- and go to the next
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok maybe I didn't do a good job of explaining
The RF database consists of 400 records. Each record contains a list of (up to 50) component or field names that are required to be in the data file when data is merge into a template. If all required fields are not there, the program will error out that request.
This is the structure of the records that displays when the database is opened
Item # - template ID QRS#1 QRS#2 Notes Form Name
1 ADV-WEIGHTMGMT F04 C06 ADV-WEIGHTMGMT
when you open a record the following displays
QL Template Name: ADV-WEIGHTMGMT
Notes Form Name: ADV-WEIGHTMGMT
QRS #1: F04
QRS #2: C06
REQ Fields: BRT_ID
PBR_NM
PBR_ADR_LN1
PPH_PHN_NB
PPH_FAX_NB
PXD_DGH_NM
CNF_INR_TX
CNF_DSP_QY
CNF_RFL_QY
BNF_NM
I want to remove two of the REQ field names from each record that we no longer need displayed here.
Is this clearer?
The RF database consists of 400 records. Each record contains a list of (up to 50) component or field names that are required to be in the data file when data is merge into a template. If all required fields are not there, the program will error out that request.
This is the structure of the records that displays when the database is opened
Item # - template ID QRS#1 QRS#2 Notes Form Name
1 ADV-WEIGHTMGMT F04 C06 ADV-WEIGHTMGMT
when you open a record the following displays
QL Template Name: ADV-WEIGHTMGMT
Notes Form Name: ADV-WEIGHTMGMT
QRS #1: F04
QRS #2: C06
REQ Fields: BRT_ID
PBR_NM
PBR_ADR_LN1
PPH_PHN_NB
PPH_FAX_NB
PXD_DGH_NM
CNF_INR_TX
CNF_DSP_QY
CNF_RFL_QY
BNF_NM
I want to remove two of the REQ field names from each record that we no longer need displayed here.
Is this clearer?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes your analysis is correct and its a multivalue field. I'll try you solution in a little while. I think this will work
Thank you
Mike
Thank you
Mike
ASKER
Thanks Sief and Sam for your help