Solved

calculating a new number when creating a document via profile document

Posted on 2001-08-02
40
240 Views
Last Modified: 2013-12-18
0
Comment
Question by:hreinart
  • 11
  • 10
  • 8
  • +3
40 Comments
 
LVL 13

Accepted Solution

by:
CRAK earned 50 total points
Comment Utility
Not a good idea:
What if you run several replica's of the same database? You'll get multiple documents with the same number!
What if two people want to updat ethe profile at the same time? A replication or save conflict!
Do you need a sequential number or a unique number?
If unique: why not settle for @DocumentUniqueID?
Works every time with no effort at all!
0
 
LVL 9

Expert Comment

by:Arunkumar
Comment Utility
he needs  sequential numbering !!!
0
 

Author Comment

by:hreinart
Comment Utility
i need in the end sequential and unique numbering.
only 1 replica exists, no problem.

the document which is being created the a problem report.
when tlaking about problems the user can/should easy talk about problem #45, so everyone should see which problem he's referring to.

i wouldn't mind to have a separate database where only the numbering is done, if this would it make more easy.

does anyone see the error in the formular?
i don't understand why this error messages is presented.
0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
Create a form (eg. "myProfile") with an editable numeric field (eg. "myField").

Create a way for an db-manager to edit the profile (initialize it), using @Command([EditProfile]; "myProfile"): fill out an initial number.

On the normal forms you create a numeric field as well (e.g. "myNumber"). I can think of several ways to assign a number to the document, but let say we use the QuerySave event.

Add formula code like:

Field myNumber:=myNumber;
Value:="";
@If(
  myNumber="";
  @Do(
    @Set("Value"; @GetProfileField("myProfile"; "myField") + 1);
    @SetProfileField("myProfile"; "myField"; Value);
    @SetField("myNumber"; Value)
    );
  @Return("")
  )

Other ways would be: a field formula, lotusscript events on the form or formula or script agents processing selected (unnumbered) docs.
0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
So Arun, you had that feeling to....?  ;-))
0
 

Author Comment

by:hreinart
Comment Utility
hi,

i just found this solution @notes.net
and tried it out successfully.
it works as i need it.

what do we do now?
hreinart

-----------------------------------


Try creating a Computed when Composed Number Field with the following formula:



REM "first doc number";
tempPRText := 1;
tempPRNumber := tempPRText;

REM "all docs after the first and add 1 to increase counter";
tempText:=@Subset(@DbColumn("" : "NoCache"; ""; "(docnumber)"; 1); 1);
temp := tempText;
@If(temp != ""; temp + 1; tempPRNumber)



then create a view called (docnumber) with the first column sorted in decsending order with the column value of the field you created above

 
0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
Whatever you feel like..... either delete the question or evaluate the answers.
One suggestion from my side: compared to @DbColumn, a profile works a lot faster! You won't notice in one document/one user but try doing 100 DbColumns in one junk of code and compare it to 100 GetProfileFields.....
In addition: DbColumn retrieves max 64k. It returns an error if you've got too many docs!
I wouldn't delete the question just yet.....!
0
 
LVL 4

Expert Comment

by:sloeber
Comment Utility
I use another method.
Because, if you make with two persons the same time a new doc, both documents will have the same number.
Or if one person create a new doc, and leave it a couple hours open before closing it, then there will be another guy who also made a new doc, and they will have both the same number.
What I always do is make a form with one field and call the form Numbers or something like that.
Make one document, and give the startnumber, from which you want start like 10000.
You make also a view (example "LastNumber") with selection criteria Form = Numbers
and in the first column you let see the field.
In your form, you make,
let say you have a field called Resnumber, where the unique number must come.
Then you set this in the querysave event
Sub Querysave(Source As Notesuidocument, Continue As Variant)
S=Source.fieldgettext("Resnumber")
     If S="" Then
          Dim session As notessession
          Dim dbsource As notesdatabase
          Dim doc As notesdocument
          Set session=New notessession
          Set dbsource=session.currentdatabase
          Set view=dbsource.getview("LastNumber")
          Set nummer=view.Getfirstdocument
          If nummer Is Nothing Then
               N=9999
          Else
               N=nummer.Nr(0)
               nummer.Nr=N+1
               Call nummer.save(True, False)
          End If
          Call source.fieldsettext("Resnumber",N)
          Set doc= source.document
          Call doc.save(True,True)
     End If    
End Sub

The advantage of this method is that you always work only by one document.
So, the 64K rule, has nothing to do with this.
You can never have two times the same number.
You can always easy change the numbering.
etc .........;


Greets,
Sloeber
0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
You're right about the 64k limit, but it's still slower.....
0
 

Author Comment

by:hreinart
Comment Utility
good ideas, i'll try them out.
...but harder to whom to give the points...

sloeber:
any possibility to see the number when creating this document?
the use may note in down when creating it.
i've seen the way in some databases where this number is presented after save on a new screen, but it doesn't look nice.

please have also a look on the next question i'll post now.


thanks
hreinart
0
 
LVL 9

Expert Comment

by:Arunkumar
Comment Utility
Hey guys!

Im out of EE for almost 100 days now and now i think i am back, how about welcoming me with a 400 point ???

Beggar started begging on his first day ???

;-)
0
 

Author Comment

by:hreinart
Comment Utility
hi all,

could you explain the 64k limit to me please?
does it depend on the document size or on the amount of documents being displayed in he view (in this case i could only display e.g. the latest 20)?

i'd like to get a feeling when problems would occur.
i'll try the ideas on monday.

arunkumar:
i was also out for quite a long time.
i could give you this question.
my old one is still not answered.
the task was to download web pages into a domino database.
you can try e.g. http://www.netdoktor.de/
i'd like to get all documents (including all linked pages)
into a domino databse. today i do this manuall (copy and paste the complete page into a discussion page).
i know this should be core domino functionality, but i never got it to work (i read the manual a lot of times!).

best regards
hreinart
;-)

0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
It is explained in the help files:

@DbColumn can return no more than 64K bytes of data. Use the following equations to determine how much of your data can be returned using @DbColumn.
For lookups that return text:
2 + (2 * number of entries returned) + total text size of all entries
For lookups that return numbers or dates:
(10 * number of entries returned) + 6

Thus, it depends on the amount of data that all documents generate in the specified column.
The more documents and/or the more content the columns shows, the easier you get to that 64k limit (=error!)...
0
 

Author Comment

by:hreinart
Comment Utility
hi crack,

would that mean that if 6000 documents are listed, i'd need to calc:

6.000 * 10 = 60.000
60.000+6 = 60.006 (limit is 64.000)

???

or is the documents size that value to be calculated?


best regards
hreinart
0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
It's the size of the data displayed in the column; fortunately not the documents size (imagine if there was an attachment on it...)!

If text:
Number of entries returned = no. of docs in view = 6000
Average 10 chars in the column for each document = 60000
Lookup size = 2 + (2 * 6000) + 60000 = 72002 bytes = error!

If number/date: 60006 bytes as you state; ok if the database does not grow a lot further.....
0
 

Author Comment

by:hreinart
Comment Utility
hi crack,

that's understandable.

if the first colum just returns a number e.g 1456
we only calculate 4 character due to your "Average 10 chars in the column for each document ".

this would mean i can show a lot more than 6000?

hreinart
0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
Make sure it returns 1456 (number); not "1456" (text)!
Numbers are stored in a few bytes (that causes the difference between integer, long etc.); strings use one byte for each character.

If all doc's generate the numbers as a 4-digit string, it would cost 2 + (2 * 6000) + (4 * 6000) = 36002 bytes: your database could grow approx. twice as big!
If 5-digits strings: 42002 bytes, etc.

But back to basics.... have you tried/speed tested the profiledocument yet?
It seems that you're choosing calculated risk and poor performance over speed and/or hardly any risk....
0
 
LVL 4

Expert Comment

by:sloeber
Comment Utility
Post thee same code in the queryopen but don't save the number document
Sub QueryOpen(Source As Notesuidocument....
     S=Source.fieldgettext("Resnumber")
    If S="" Then
         Dim session As notessession
         Dim dbsource As notesdatabase
         Dim doc As notesdocument
         Set session=New notessession
         Set dbsource=session.currentdatabase
         Set view=dbsource.getview("LastNumber")
         Set nummer=view.Getfirstdocument
         If nummer Is Nothing Then
              N=9999
         Else
              N=nummer.Nr(0)
              End If
         Call source.fieldsettext("Resnumber",N)
         Set doc= source.document
         Call doc.save(True,True)
    End If    
End Sub

Greets,
Sloeber
0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
Still open today, if you need more help please add comments, since much time has passed so the Experts
can be advised of any progress you may have made here.

You have 18 open questions, all of which require your attention.  Please check the HELP DESK link on
the left for Member Agreement, Guidelines and the Question/Answer process.  If you need help to split
points, or other special handling of your questions or account, please post a zero point question in
Community Support with details.

Your attention to updating your open questions is not only needed, but appreciated.

Thanks,

Moondancer
Community Support Moderator @ Experts Exchange
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:hreinart
Comment Utility
hi moondancer,

please give sloeber ans crack 50 points each.



thanks
hreinartz
0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
Thank you, hreinart ...

This is what I've done, changed the value of this question to 50 points, refunding the other 50 to you, and awarded CRAK within this question.  

All that remains to complete this point split is for you to Ask a new question in this topic area with title:
Points for sloeber
and in comments paste this link
http://www.experts-exchange.com/jsp/qShow.jsp?ta=lotusnotes&qid=20162115
which will then award sloeber with the other 50 points.  sloeber will then either comment or Propose an Answer to finalize the other half of this point award split.

If a moderator does this for you instead, you no longer have the detail in your personal history of how and where you spent your points.

If more is needed, please comment.

Thanks,
:)
EE Moderator
0
 

Expert Comment

by:awinemaster
Comment Utility
I am trying this code but the field on my form is a numeric field and this does a gettext.  I am new to ls and can't figure out how to update the code to treat the field as a numeric field not a text field.

Sub Querysave(Source As Notesuidocument, Continue As Variant)  
     S=Source.fieldgettext("TRANS_NBR")
     If S="" Then
          Dim session As notessession
          Dim dbsource As notesdatabase
          Dim doc As notesdocument
          Set session=New notessession
          Set dbsource=session.currentdatabase
          Set view=dbsource.getview("LUSNum")
          Set nummer=view.Getfirstdocument
          If nummer Is Nothing Then
               N=9999
          Else
               N=nummer.Nr(0)
               nummer.Nr=N+1
               Call nummer.save(True, False)
          End If
          Call source.fieldsettext("TRANS_NBR",N)
          Set doc= source.document
          Call doc.save(True,True)
     End If    
End Sub

Anyone still interested in this?

Thank you!
0
 
LVL 9

Expert Comment

by:Arunkumar
Comment Utility
        Call source.fieldsettext("TRANS_NBR",N)
         Set doc= source.document
         Call doc.save(True,True)

Replace the above three lines with the following.

Set doc = Source.Document
doc.Trans_NBR = Cint(N)
Call doc.Save(True,True)

Good Luck!
Arun.
0
 

Author Comment

by:hreinart
Comment Utility
hi,

it's quite long ago since i used this.
as far as i remember i didn't use this script.

if you can't figure it out, i can try to recover the old original version...but it'd take some time.

i used the profile document solution.



regards
hreinart
0
 

Author Comment

by:hreinart
Comment Utility
..did you look in notes.net -> sandbox?
0
 

Expert Comment

by:awinemaster
Comment Utility
I have a view named LUSNum selecting a form with a number field.  I created one document with the number 1.  On the QuerySave event of a different form, I've added the code to set the field TRANS_NBR to a sequential & unique number.
Even changing the three lines above, I am still getting a type mismatch error.  Here's the code again.

Sub Querysave(Source As Notesuidocument, Continue As Variant)  
     S=Source.fieldgettext("TRANS_NBR")
     If S="" Then
          Dim session As notessession
          Dim dbsource As notesdatabase
          Dim doc As notesdocument
          Set session=New notessession
          Set dbsource=session.currentdatabase
          Set view=dbsource.getview("LUSNum")
          Set nummer=view.Getfirstdocument
          If nummer Is Nothing Then
               N=9999
          Else
               N=nummer.Nr(0)
               nummer.Nr=N+1
               Call nummer.save(True, False)
          End If
          Set doc = Source.Document
          doc.Trans_NBR = Cint(N)
          Call doc.Save(True,True)
         
     End If        
End Sub


I was reading through the Lotus Developer Domain, but I haven't made it through all of the topics yet.  Would you recommend the sandbox first?

Thanks for your quick response!
0
 

Expert Comment

by:awinemaster
Comment Utility
I have a view named LUSNum selecting a form with a number field.  I created one document with the number 1.  On the QuerySave event of a different form, I've added the code to set the field TRANS_NBR to a sequential & unique number.
Even changing the three lines above, I am still getting a type mismatch error.  Here's the code again.

Sub Querysave(Source As Notesuidocument, Continue As Variant)  
     S=Source.fieldgettext("TRANS_NBR")
     If S="" Then
          Dim session As notessession
          Dim dbsource As notesdatabase
          Dim doc As notesdocument
          Set session=New notessession
          Set dbsource=session.currentdatabase
          Set view=dbsource.getview("LUSNum")
          Set nummer=view.Getfirstdocument
          If nummer Is Nothing Then
               N=9999
          Else
               N=nummer.Nr(0)
               nummer.Nr=N+1
               Call nummer.save(True, False)
          End If
          Set doc = Source.Document
          doc.Trans_NBR = Cint(N)
          Call doc.Save(True,True)
         
     End If        
End Sub


I was reading through the Lotus Developer Domain, but I haven't made it through all of the topics yet.  Would you recommend the sandbox first?

Thanks for your quick response!
0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
This COULD be the cause:

N=nummer.Nr(0)
nummer.Nr=N+1

If the field called "Nr" on the 1st document in the view is (still?) a text-field, then N (not DIMmed, so variant type) becomes text. Calculating N + 1 would (in such case) get you into this kind of trouble!

Make sure the sorting in that view is set to "descending", or you'll find yourself generating lots of documents with the same number!  ;-))
0
 

Expert Comment

by:awinemaster
Comment Utility
Well, yes and no.  Revising the code by Dim-ing the N seems to work to update the sequence number in the lookup view, but the form with the querysave is not getting the number.  Any further insight?

Thank you so much.

Sub Querysave(Source As Notesuidocument, Continue As Variant)  
     S=Source.fieldgettext("TRANS_NBR")
     If S="" Then
          Dim session As notessession
          Dim dbsource As notesdatabase
          Dim doc As notesdocument
          Dim N As Variant
          Set session=New notessession
          Set dbsource=session.currentdatabase
          Set view=dbsource.getview("LUSNum")
          Set nummer=view.Getfirstdocument
          If nummer Is Nothing Then
               N=9999
          Else
               N=nummer.seqnum(0)
               nummer.seqnum=N+1
               Call nummer.save(True, False)
          End If
          Set doc = Source.Document
          doc.TRANS_NBR = Cint(N)
          Call doc.Save(True,True)
         
     End If        
End Sub
0
 
LVL 9

Expert Comment

by:Arunkumar
Comment Utility
Okay the form with the query save, does it have a field called "TRANS_NBR" ???

The number may not be immediately available since we are setting the value at the backend. Try to reopen the saved document and look for the number.

If you dont find inside the document, you may have to look into the document properties for the field and its content.

Let us know.
Arun.
0
 

Expert Comment

by:awinemaster
Comment Utility
You're right.  I thought I has saved the document twice but I must not have.  I 've learned much in this function. Thanks for helping a beginner!
0
 
LVL 9

Expert Comment

by:Arunkumar
Comment Utility
Hey not a problem. Give me some points !

I know I am crazy but just to drive my buddies Crazy...

;-)
0
 

Expert Comment

by:awinemaster
Comment Utility
How do I give points?
0
 
LVL 9

Expert Comment

by:Arunkumar
Comment Utility
Ask a new question with the title as "Points for Arunkumar"  I will comment there and get the points from you.

Hi CRAK!
;-)
0
 

Author Comment

by:hreinart
Comment Utility
hi,

would be nice to open a new question...i receive a lot of
mails from ee for an old question (from my point of view).
0
 
LVL 9

Expert Comment

by:Arunkumar
Comment Utility
U can click on unsubscribe on each question you receive an email from.

It would stop your inbox being flooded.

:-)
Arun.
0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
Hi Arun,
Do me one favour please.... don't ask for more points in this question than the original amout that I got for answering it. You wouldn't be driving me crazy... I'd go mad!
0
 
LVL 9

Expert Comment

by:Arunkumar
Comment Utility
yea 20 more and i will match yours but i will wait for a while and ask 25 so that i can beat you !
0
 
LVL 13

Expert Comment

by:CRAK
Comment Utility
Grrrr
;-))
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
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.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

11 Experts available now in Live!

Get 1:1 Help Now