Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Field Property

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
schmad01
Asked:
schmad01
  • 7
  • 5
  • 5
  • +3
1 Solution
 
GunsenCommented:
Try this :

FIELD field_name := field_name;
@Set("field_name"; @TextToNumber(field_name));
SELECT @All
0
 
ArunkumarCommented:
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
 
Jean Marie GeeraertsApplication EngineerCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ArunkumarCommented:
may be right !

Welcome back JM !

;-)
0
 
Jean Marie GeeraertsApplication EngineerCommented:
Hey, how are you? No more gossip questions ?? ;-)
0
 
schmad01Author Commented:
Maybe,  I get a type mismatch error. How can one possible go thru 1000 documents to find the culprit?
0
 
MoonshadowCommented:
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
 
schmad01Author Commented:
Moonshadow,
I'm not too familiar with script.  Can you show me what you mean? Thanks.
0
 
MoonshadowCommented:
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
 
schmad01Author Commented:
I'm on Version4.X.  I will be using Arun's script.
0
 
MoonshadowCommented:
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
 
MoonshadowCommented:
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
 
MoonshadowCommented:
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
 
schmad01Author Commented:
I'm on Version4.X.  I will be using Arun's script.
0
 
zvonkoCommented:
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
 
zvonkoCommented:
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
 
ArunkumarCommented:
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
 
zvonkoCommented:
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
 
zvonkoCommented:
...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
 
zvonkoCommented:
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
 
zvonkoCommented:
Hello schmad01,

how about my points :-)
0
 
ArunkumarCommented:
Thats it !

I am not gonna allow this anymore!

;-)
0
 
zvonkoCommented:
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
 
ArunkumarCommented:
In that process... why dont you close some deals for me too ???

;-)
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 7
  • 5
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now