Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Field Property

Posted on 2001-08-16
24
Medium Priority
?
247 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
[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
  • 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
Independent Software Vendors: 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 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