Solved

Remove fields from a Database - search and replace

Posted on 2011-03-07
8
605 Views
Last Modified: 2013-12-18
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      
0
Comment
Question by:BulldogMike
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 35062412
How? Very carefully...
0
 
LVL 7

Expert Comment

by:Sam654
ID: 35062472
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.

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

Open in new window

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 35062561
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Assisted Solution

by:Sam654
Sam654 earned 100 total points
ID: 35062578
Have I got this wrong? I've assumed that you want to remove fields (actual Notes fields) from a document, but now that I re-read, maybe you're wanting to remove information from within a field? You have a single field storing all your field names and you want to remove 2 of these field names?

If so put the following into an agent and run it from the actions menu. Highlight a single doucment, run the agent on it, check for the desired result and then select multiple documents and run it on them all.




FieldToChange := "" ;
TextToChange := "" ;
ReplacementText := "" ;

@ReplaceSubstring( FieldToChange ; TextToChange ; ReplacementText )

Open in new window

0
 

Author Comment

by:BulldogMike
ID: 35069090
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?
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 400 total points
ID: 35069432
Oh, that changes a lot I think.

Let me try to rephrase what I think you want: there is a multi-value field (?) in each document that contains the names of all required fields, and you want to remove 2 names in all those documents. Correct? Is also my assumption correct, that the field is multi-value?

In that case a simple Formula agent will do what you want. Basically, if the field with required names is called ReqNames, the agent's code could be this:

NotRequired:= "field1":"field2";
FIELD ReqNames:= @Trim(@Replace(ReqNames; NotRequired; ""))
0
 

Author Comment

by:BulldogMike
ID: 35070471
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
0
 

Author Closing Comment

by:BulldogMike
ID: 35083188
Thanks Sief and Sam for your help
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question