Auto Numbering

Just create a view with the first column having the number field on the form.

Then increement it in the form before saving.

I will give details , wait.........

U need to get the last number from the view:

x := @DbColumn(Class : "NoCache" ; "" : "" ; "Reference" ; 1);
y := @If(@Trim(x)="" ; "0" ; @Subset(x;-1));
z := @TextToNumber(@Text(y)) + 1;
a :=@Right("00000" + @Text(z);5);
a

do it in script. at saving

The above formual should be used for a field which is computed when composed. But, this will etch the number while u compose the document.
So, what u have to is, place this code in QuerySave event of form and assign the number like this:

x := @DbColumn(Class : "NoCache" ; "" : "" ; "YourView" ; 1);
y := @If(@Trim(x)="" ; "0" ; @Subset(x;-1));
z := @TextToNumber(@Text(y)) + 1;
a :=@Right("00000" + @Text(z);5);
@SetField("yournufield";a)


Note: You have to sort the first column in the view. Field should be number.

Any help needed, please post here.

Thanks.

It's actually a good idea to turn the code around :

- sort the first column descending (so it's the last one that's on top)
- keep the field text - since you turn it into something like 000101

- change the formula madheeswar gave to:

x := @DbColumn(Class : "NoCache" ; "" : "" ; "YourView" ; 1);
y := @if(@iserror( x ) ; 0 ;  @If(@Trim(x)="" ; "0" ; @Subset(x;1)));
z := @TextToNumber(@Text(y)) + 1;
a :=@Right("00000" + @Text(z);5);
@SetField("yournewfield";a)

The reason is quite simple  : in an extreme situation, the @dbcolumn will not return the last entry in the view (64K limit).  It's probably unlikely to reach the limit, but better be safe than sorry :-)
Another reason is when you change your Querysave event to Lotusscript (because you need other functionality for instance), you can use notesview.getfirstdocument(), and that's 'the' fastest way to access a document from a view.

cheers,

Tom

Bozzie, thats a very cool idea.
When I will learn these type of things.

Very long journey to go.........

Another way of working is that you create a form ex "NumberForm" with one Field ex "Number".
You create also a new view ex "NumberView" with one column (Formula >> Number)
The last thing you need to do is put the following code in your querysave event.
                Dim s As New notessession
      Set db=s.currentdatabase
      Set thisdoc =s.documentcontext
      
      '*******************************************************
      '* Check if the document has already a number *
      '*******************************************************
      If thisdoc.ProblemNumber(0) ="" Then
            Set view=db.getview("Number")
            Set doc = view.getfirstdocument
            lastnr = doc.Number(0)
            doc.Number=lastnr  + 1
            Call doc.save(True,True)
            thisdoc.ProblemNumber=lastnr
      End If
                ..........................   the rest of your code

The advantage of this is that you always have only one document (no problems with 64K limit)

Sloeber,
I think u adopted the same procedure which I and Bozzie told. The difference is only Script.

Am I right?
If makuletski has asked for script, we would have provided. Just we are waiting for response.

No, it's a different solution: he uses 1 document to store the value.

The problem in this setup is avoiding save conflicts : if 1 documents are saved at the same time, they will update the same (number) document.
The advantage is, like sloeber says, performance and speed.  No big view updates, so it will be a lot quicker.

You could also store the number in a profile document, but again, here are problems too, so I don't recommend doing that.

cheers,

Tom

Sloeber is right !

The reason being @DbColumn having limitations of 64k.  Do not use profile document or control document since profile is cached and control document may endup with conflicts.  

I have used control document in one application with lots of controls for the document though.  I have double locking documents for this control document to be unique and its a bit complicated for this simple request though.

;-)
Arun.

64k Limit for just numbers will take lot of time ...64k is a string long enough to fill 5 pages of a MS Word document , So not a big concern...so he can use madheeshwar and tom's suggestion

Anyways good morning bro ;-)

Partha

Dont say its a big concern, I have been hit by so many applications with that limitation.

Yeah 64k limit is a concern but mostly when u are fetching large values as output. But this is just numbers Arun...so shdn't be a problem...but i don't want to go against my Bro ... so better to use script and increment it...if you have large numbers to be fetched...


Partha

The 'separate document' approach has it's limits also, you have to protect yourself from save conflicts.

And by getting the first document from the @dbcolumn, you can circumvene the 64 k limit.

The 'separate document' approach is very usefull if only 1 process assigns new numbers (create a queue of new documents, assign the number by a scheduled agent on a single server, replicate changes around).
Make sure to check the 'merge conflicts' form option...

cheers,

Tom

I'll weigh in with a few things...

Bozzie is right that .getFirstDocumen is really quick.  But s is .getastDocument -- they have almost exactly the same performance profile.  However, for @DbColumn, you are better off with @SUbset(...;1) than @Subset(...;-1), for which there is a small performance penalty.

I've never been much of a fan of the single document approach, because it can:
1) cause conflicts, as Bozzie says
2) is not self-repairing (if the doc gets lost, an admin has to recreate it, whereas the "find last assigned document" approach never breaks
The idea really comes out of relational design, where it makes a lot of sense, since you usually have a single data store, are extremely unlikely to lose a full table, have locking avaialble, and have to execute a new query to find "last number" (instead of reading an existing index)

To bullet-proff autonumbering systems, I usually have a cleanup agent remove al documents older than X period every X*2 periods, e.g., nightly remove all docs more than two days old.  It removes them by flagging them as no longer needed for number serial number calculations, and having the view key off that flag.

Believe it or not, @DbColumn for a small view in the current DB can be quicker than a retrieval of a single document. I'm not saying it WILL be qicker, just that it sometimes is.

Yes, the cleanup agent is a very good way to go.
And indeed, getlastdocument is only marginally slower then getfirstdocument.  


If you use a numbering view, where you sort the actual documents on their number, you could create an agent that sets a field, 'includeInNumbering' for your actual documents.

Create the numbering view, with formula IncludeInNumbering = "1"

The documents need a field IncludeInNumbering, computed when composed, "1"

Now the agent shouldn't delete the documents (since it are your actual documents), but reset the field to "0".  It should also checki if it's the LAST document left, since you obviously shouldn't remove that from the view.
But maybe that's what qwaletee means :-)


cheers,

Tom

Actually,  I'd rather work it the other way around... one less thing to maintain. Let the view EXCLUDE the documents that have IncludeInNumbering = 0 (or RemoveFromNumbering = 1)

Bozzie's way has a slight performance benefit, because the view could key off the flag field alone, instead of a combination of form and flag field.  But I normally prefer maintainability over performance.

Hey, Madheeswar, is this yours?

http://www.groupcomputing.com/dominoproforum.nsf/0/78D122D3ED17124687256D4F0032D8DE?OpenDocument

Yes. Its mine.
Any problems bro?

Nope.  Just noticed the name, and figured...

My name is unique in the universe. (As per I know).

I checked a lot whether there anyone have the same name.
But untill now I did not got any.
If u find any messages with this name, u can consider that its me.

What is the origin of your name?

Since U asked me, I am telling th truth. Don't let anyone know it.

My Mom thinking to keep a name for me in my childhood. First she thought of Madhu(this is very common name in India, especially in my sate)Madhu means Honey and also we can use it in lot of occassions and it changes its meaning.
And when she is sleeping she got a dream and in that dream she saw Lord Easwar (God in India).
Since she like Madhu and she got a dream of Easwar, she named me as Madheeswar.

Thats the interesting big story behind.

I think now, u got all info of mine.

Anyway, their is a sad news. I may stay in EE for just another 3 months. Later I am going back to India. If I get job its OK. Else, I need to think what should I do.

Best of luck guys.

I used the code below and i tested generating 2 numbers, on the suppossedly third number, the second number still appears....what could have gone wrong?

FIELD casenum := casenum;
x := @DbColumn(Class : "NoCache" ; "" : "" ; "Number" ; 1);
y := @If(@Trim(x)="" ; "0" ; @Subset(x;-1));
z := @TextToNumber(y) + 1;
a :=@Right("00000" + @Text(z);5);
@SetField("casenum";a)

tnx in advance

Where did u placed this code?
Place the below code in casenum field. Let the field be ComputedWhenComposed

x := @DbColumn(Class : "NoCache" ; "" : "" ; "Number" ; 1);
y := @If(@Trim(x)="" ; "0" ; @Subset(x;-1));
z := @TextToNumber(@text(y)) + 1;
a :=@Right("00000" + @Text(z);5);
@SetField("casenum";a)


Once u save this document, u have to see this document in Number View. This view should store all the documents created by this form.
And the first column should have casenum and ascending.

If u need more help, please let us know.
-madheeswar

It lives!!!!!!!!!

I placed it in casenum field but this error occurs. " Field casenum. Tha fieldname specified in @seffield must be declared prior to use". I think thats the reason why i added the FIELD casenum := casenum in the beginning of the code.

Ok try this
Field casenum:=casenum;
x := @DbColumn(Class : "NoCache" ; "" : "" ; "Number" ; 1);
y := @If(@Trim(x)="" ; "0" ; @Subset(x;-1));
z := @TextToNumber(@text(y)) + 1;
a :=@Right("00000" + @Text(z);5);
@SetField("casenum";a)


and let us know.

same thing happens...the second number still appears instead of the third..but the error message is gone, though.

Delete those 2 documents and recreate from scratch.

What are the numbers r thier in the view and which no, u have to get?

While creating a new number are u able to see the third no?

Refresh the view and try to create a new doc.

If the above doesn't work for u then send me ur db to reddy@stoneforest.com.sg

-madheeswar

i tried that before replying...i deleted the existing 2 documents

the number in my existing view is 00001 and 00002, the next number should be 00003

i wasnt able to see the 3rd number coz what appears is still the 2nd number

and also, in the code, there's only 5 characters, if the number exceeded 99999 there might be a problem, so how will the code be edited in such a way that, the number starts with 1 only (not 00001)so there's no limit as to how many characters it may get

i will send it now....thanks a lot!!

i already sent the database. pls inform me if u already received it. thanks thanks!

and for continuos addition:
Field casenum:=casenum;
x := @DbColumn(Class : "NoCache" ; "" : "" ; "Number" ; 1);
y := @If(@Trim(x)="" ; "0" ; @Subset(x;1));
z := @TextToNumber(@text(y)) + 1;
@SetField("casenum";z)

what was the cause of the problem? i noticed the only thing that change in the code is the 1 in the @subset which was -1 before.

it worked out ok now...however when i reached casenumber 10, since the view is categorized, it fall under case number 1...how will i prevent this? or shall i just use the prior code but adding more 0's instead of just 5?

tnx again!

In the Number view, the first column u have kept is descending. For that u should use 1.

U used -1 which is for ascending.

U can keep long no's and keep 0 in front

i used the prior code and added more 0's coz if i use the 'continous addition' code, when it reaches 10, it fell right after 1, in a descending view, the last document will be 9, so the next number will be 10 again

tnx so much madheeswar! i really appreciate your help!! im adding 235 more points to show my appreciation =) (and also bec i know i still have more questions to come with regards to the database i sent u, since u already have it, i may just post the question here, specially for u *lol* )

by the way, what happened to your work? you went back to india or what?

to all the other guys who posted their comments, thanks a lot as well!

Hi,
FIELD casenum:=casenum;
x := @DbColumn(Class : "NoCache" ; "" : "" ; "Number" ; 1);
y := @If(@Trim(@text(x))="" ; "0" ; @Text(@Subset(@text(x);1)));
z := @TextToNumber(@Text(y)) + 1;
@SetField("casenum";z);


use the above code to generate without 0's and u can have infinite no.

And in your Number view in the first column, add the below coder
@TextToNumber(casenum)

And change in the querysave event also.

Hope it helps.

My India venture did not succeeded.

Where are 235 points lol.

Anyway since u got the answer close this questin with Grade "A".

Use this final version code:

FIELD casenum:=casenum;
x := @DbColumn(Class : "NoCache" ; "" : "" ; "Number" ; 1);
y := @If(@Trim(@text(x))="" ; "0" ; @Subset(x;1));
z := @TextToNumber(@Text(y)) + 1;
@SetField("casenum";z);

Thanks Makuletski