Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Field Property

Posted on 2001-08-16
24
Medium Priority
?
249 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 300 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
Industry Leaders: 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 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
 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month11 days, 17 hours left to enroll

916 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