Solved

Field Property

Posted on 2001-08-16
24
239 Views
Last Modified: 2013-12-18
If I decide that I want to change a field in a database from a text field to a number field, How do I get all the documents to convert the text to a number without going thru every document. I tried:@Command([ToolsRefreshAllDocs])

;@All

but half way thru it came up with the message: Cannot convert text to a number.  Help, please.
Thanks.
0
Comment
Question by:schmad01
  • 7
  • 5
  • 5
  • +3
24 Comments
 
LVL 3

Expert Comment

by:Gunsen
ID: 6392935
Try this :

FIELD field_name := field_name;
@Set("field_name"; @TextToNumber(field_name));
SELECT @All
0
 
LVL 9

Accepted Solution

by:
Arunkumar earned 75 total points
ID: 6395434
Write this code in an agent and run the agent on selected documents, change the FIELDNAME to whatever field name you want to convert on the documents.

Dim ss As New notessession
     Dim db As notesdatabase
     Dim dc As notesdocumentcollection
     Dim doc As notesdocument
     Dim Newitem As notesitem
     
     Set db = ss.CurrentDatabase
     Set dc = db.UnprocessedDocuments
     Set doc = dc.GetFirstDocument
     Do While Not doc Is Nothing
          vals = doc.FieldName(0)
          Call doc.Removeitem("FieldName")
          Set Newitem = New NotesItem(doc , "FieldName" , Cint(vals))
          Call doc.Save(True,False)
          Set doc = dc.GetNextDocument(doc)
     Loop

Good Luck!
-Arun
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 6396343
Just a thought, but maybe there's a document with non-numeric value in the field and it can't be converted for that reason.
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 6396624
may be right !

Welcome back JM !

;-)
0
 
LVL 8

Expert Comment

by:Jean Marie Geeraerts
ID: 6396651
Hey, how are you? No more gossip questions ?? ;-)
0
 

Author Comment

by:schmad01
ID: 6397079
Maybe,  I get a type mismatch error. How can one possible go thru 1000 documents to find the culprit?
0
 

Expert Comment

by:Moonshadow
ID: 6397184
Simple.  Put a MsgBox statement in above the line that does the field conversion.  Use some unique identifier as the text of the message box.  Then run your code.

It'll display a boatload of dialog boxes, but you need to PAY ATTENTION TO THE TEXT IN EACH ONE.

Eventually, you'll hit the error and you'll know which document caused the problem by referencing the unique text in the dialog.

Or, alternately, you can set a dummy variable to the same unique value right before the conversion statement.  Then enable the LotusScript debugger and run the code.  Click "Continue" and the code chugs along.  When it craps out, you'll get dumped back into the Debugger at the bad line.  Just look at the dummy variable's value in the Variables tab and you'll be able to trace back to the bad document.

Cheers!
0
 

Author Comment

by:schmad01
ID: 6397219
Moonshadow,
I'm not too familiar with script.  Can you show me what you mean? Thanks.
0
 

Expert Comment

by:Moonshadow
ID: 6397255
Are you using Arun's script or your own Formula stuff?

If you're using Formula, it's easier to just use the dialog box approach.  Let's say that your documents each have a "subject" that is more or less unique per document.  You'd just need to put in the following line of code before your conversion stuff:

@Prompt( [Ok]; "Blah"; Subject );

That'll display the dialog box right before the error craps things out.

In script, there's a couple of ways to do it.  The easiest would be to add the following code just above your conversion code:

dim dummy as variant
dummy = doc.Subject

...again, assuming that "Subject" is a unique (more or less) identifier.  Then, before you run the code, click "File - Tools - Debug LotusScript".

I'm assuming you're using R5 here...

...then run the code.  Click "Continue" when the debugger comes up.  Then when the code crashes, the debugger will pop back up.  Click on the lower pane of the window on the "Variables" tab and look for "dummy".  The value to the right is the subject of the document that's causing the crash.

Cheers!
0
 

Author Comment

by:schmad01
ID: 6397305
I'm on Version4.X.  I will be using Arun's script.
0
 

Expert Comment

by:Moonshadow
ID: 6397325
Are you using Arun's script or your own Formula stuff?

If you're using Formula, it's easier to just use the dialog box approach.  Let's say that your documents each have a "subject" that is more or less unique per document.  You'd just need to put in the following line of code before your conversion stuff:

@Prompt( [Ok]; "Blah"; Subject );

That'll display the dialog box right before the error craps things out.

In script, there's a couple of ways to do it.  The easiest would be to add the following code just above your conversion code:

dim dummy as variant
dummy = doc.Subject

...again, assuming that "Subject" is a unique (more or less) identifier.  Then, before you run the code, click "File - Tools - Debug LotusScript".

I'm assuming you're using R5 here...

...then run the code.  Click "Continue" when the debugger comes up.  Then when the code crashes, the debugger will pop back up.  Click on the lower pane of the window on the "Variables" tab and look for "dummy".  The value to the right is the subject of the document that's causing the crash.

Cheers!
0
 

Expert Comment

by:Moonshadow
ID: 6397326
Are you using Arun's script or your own Formula stuff?

If you're using Formula, it's easier to just use the dialog box approach.  Let's say that your documents each have a "subject" that is more or less unique per document.  You'd just need to put in the following line of code before your conversion stuff:

@Prompt( [Ok]; "Blah"; Subject );

That'll display the dialog box right before the error craps things out.

In script, there's a couple of ways to do it.  The easiest would be to add the following code just above your conversion code:

dim dummy as variant
dummy = doc.Subject

...again, assuming that "Subject" is a unique (more or less) identifier.  Then, before you run the code, click "File - Tools - Debug LotusScript".

I'm assuming you're using R5 here...

...then run the code.  Click "Continue" when the debugger comes up.  Then when the code crashes, the debugger will pop back up.  Click on the lower pane of the window on the "Variables" tab and look for "dummy".  The value to the right is the subject of the document that's causing the crash.

Cheers!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Expert Comment

by:Moonshadow
ID: 6397333
Hmm...sorry 'bout that.  Not sure why my answer is reposting on a refresh.  Weird.

Someone else may have to correct me (I've only really dealt with R5), but the technique should be the same with R4 as well.
0
 

Author Comment

by:schmad01
ID: 6398516
I'm on Version4.X.  I will be using Arun's script.
0
 
LVL 10

Expert Comment

by:zvonko
ID: 6399279
Hey folks,

why so complicated?

For me is this agent enough:
FIELD field_name := field_name;
@Set("field_name"; @TextToNumber("0"+field_name));
SELECT @All

...and the points go to: Gunsen

Cheers <|;-)
zvonko
0
 
LVL 10

Expert Comment

by:zvonko
ID: 6399331
Hey folks,

here the ultimativlly improved, error trapping, exception handling, rollback,
full functionaly "DoWathImean" encoding:

FIELD field_name := field_name;
@Set("field_name"; @TextToNumber("00"+field_name));
SELECT @All

...can you imagine the next step of improvement? <|;-)
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 6400034
pongadaa punnaakku pasangalaa !

Hey Stamp, Welcome back!  Hey Jerrith, i was out of job for about 4 months and i did not have an internet connection at home and now i am on a job back again.  

Soon, you will find our own gossip questions !  Take it easy guys.

By the way who is going to catch the faulty document ?

;-)
0
 
LVL 10

Expert Comment

by:zvonko
ID: 6400502
Hy Arun,

perhaps the triple zero will catch the faulty doc <|;-)

I am fairly sure that empty items are the cause for the fault...

...and am looking for my friends translating your tamil cinema slang :-)
0
 
LVL 10

Expert Comment

by:zvonko
ID: 6401598
...but perhaps are also blank chars (leading or trailing blanks) cosing the faults.

If so, than this can be an improvement:
FIELD field_name := field_name;
@Set("field_name"; @TextToNumber("0"+@Trim(field_name)));
SELECT @All
0
 
LVL 10

Expert Comment

by:zvonko
ID: 6401643
Sorry,

there is one more pitfall to walk around:
FIELD field_name := field_name;
@Set("field_name"; @TextToNumber("0"+@Trim(@Text(field_name))));
SELECT @All

Try this one and tell us if any error messages occure.

Regards,
zvonko
0
 
LVL 10

Expert Comment

by:zvonko
ID: 6619026
Hello schmad01,

how about my points :-)
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 6619040
Thats it !

I am not gonna allow this anymore!

;-)
0
 
LVL 10

Expert Comment

by:zvonko
ID: 6619064
Arun,

have you seen how many open questions this man is driving?

Don't be so, let me take some of them, then they are closed :-)
0
 
LVL 9

Expert Comment

by:Arunkumar
ID: 6619101
In that process... why dont you close some deals for me too ???

;-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Adjusting times for a meeting room 3 140
Domino Server 11 65
Lotus Notes Mailbox (Mail Forwarding) 9 40
Save conflict messages on existing documents 15 54
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

896 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now