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
Solved

generate sequential numbering.

Posted on 2004-04-11
17
445 Views
Last Modified: 2013-12-18
experts,

i was assigned to create  an application for pc, software and hardware inventory, but i have a problem creating a sequential and formatted number for each document, example
for PC

comp-04-03-1042 = year-month-document number
H-04-03-0103=year-month-document number
S-04-03-042=year-month-document number

Comp is Code for computer
H is code for hardware
S is code for software

i want to issue this document number only if the document is being save.
and the document number is being reset every month.

please help.

thanks in advance.










0
Comment
Question by:lovemeto
  • 9
  • 7
17 Comments
 
LVL 19

Expert Comment

by:madheeswar
ID: 10803586
this can be done with simple logic.

I will give the solution, wait for a minute to type ...
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10803605
first on your original form, create a field with keywords as field type and choices are Hardware|H, Software|S, computer|comp
The above keywords generates alias names with the choices. name it as type_k

Now, create a seq., no field and make it computed.(name it as serno_x).This is a text field.

and the default value for this computed field is serno_x

In the querysave, paste this formula:

FIELD serno_x:=serno_x;
FIELD type_k:=type_k;
lookup := @DbColumn("" : "NoCache"; ""; "(Yourview)"; 1);
current := @If(@IsError(lookup);" "; @Subset(lookup;1));
next := @If(current="";1;@TextToNumber(current) + 1);
yr:=@Right(@year(@Today;2));
mnth:=@Right(@Month(@Today;2));
type_k+"-"+@text(yr)+"-"+@text(mnth)+"-"+@Text(next)
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10803655
Sorry, carry forwarding my above explanation,

create another field called srno_x and make it as text and computed and place the below formula.

lookup := @DbColumn("" : "NoCache"; ""; "(numberview)"; 1);
current := @If(@IsError(lookup);" "; @Subset(lookup;1));
next := @If(current="";1;@TextToNumber(current) + 1);
@Text(next)

create one view and name it as (numberview). In the first column it should be sorted and descending and the formula for that column is:
srno_x

and in the querysave, place the below formula instead of the above:
FIELD serno_x:=serno_x;
FIELD type_k:=type_k;
FIELD srno_x:=srno_x;
yr:=@Right(@year(@Today;2));
mnth:=@Right(@Month(@Today;2));
type_k+"-"+@text(yr)+"-"+@text(mnth)+"-"+@Text(srno_x)


Best for luck and if you have any queries, please ask.

-Thanks.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:lovemeto
ID: 10803927
thanks for your reply.
I tried your code but it didnt meet my  expected output,

The value of srno_x increment everytime i save the document (Right),
but i cannot see the year and month before the document number.

And also the document number continues regardless of its type, because it was a single view only,
but those 3 types have seperate numbering scheme therefore all document number must start on number 1

example:

comp-04-03-001....002...003.. and so on
H-04-03-001....002...003.. and so on
S-04-03-001....002...003.. and so on


one more thing on your code have an error (insuficient argument or function ")" so i eliminate @right function.

FIELD serno_x:=serno_x;
FIELD type_k:=type_k;
FIELD srno_x:=srno_x;
yr:=@Right(@year(@Today;2));
mnth:=@Right(@Month(@Today;2));
type_k+"-"+@text(yr)+"-"+@text(mnth)+"-"+@Text(srno_x)


thanks


 











0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10803935
comp-04-03-001....002...003.. and so on
H-04-03-001....002...003.. and so on
S-04-03-001....002...003.. and so on

For the above scenario to work, u need three views each having the categories independently.

I will check my code and post it here.
Please give me sometime as i have a meeting now.

Thanks.
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10803952
FIELD type_k:=type_k;
view:=@if(type_k="H";"Hardwareview";type_k="S";"Softwareview";"Computerview);
x := @DbColumn(Class : "NoCache" ; "" : "" ; view ; 1);
y := @If(@trim(x)="" ; "0" ; @Subset(x;-1));
z := @TextToNumber(@Text(y)) + 1;
a := "000" + @text(z);
a

the above is for srno_x

create another 2 sets of fields. One is for year and another for month

name them as year_x and month_x (both as computedfordisplay)
and the defualt value for them is
@Right(@year(@Today);2) and @Right(@Month(@Today);2) respectively.



FIELD serno_x:=serno_x;
FIELD srno_x:=srno_x;
FIELD type_k:=type_k;
yr:=@Right(@year(@Today;2));
mnth:=@Right(@Month(@Today;2));
type_k+"-"+@text(yr)+"-"+@text(mnth)+"-"+@Text(srno_x)

0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10803954
u just try the above in the mean time and I will catch u within one hour.

Thanks.
0
 

Author Comment

by:lovemeto
ID: 10804045
FIELD type_k:=type_k;
view:=@if(type_k="H";"Hardwareview";type_k="S";"Softwareview";"Computerview);
x := @DbColumn(Class : "NoCache" ; "" : "" ; view ; 1);
y := @If(@trim(x)="" ; "0" ; @Subset(x;-1));
z := @TextToNumber(@Text(y)) + 1;
a := "000" + @text(z);
a

there was an error on this formula.
0
 

Author Comment

by:lovemeto
ID: 10804064
ignore the comments above i already found the error. its only "Computerview"
0
 

Author Comment

by:lovemeto
ID: 10804318
this formula dosnt work? return error: incorrect datatype or function ; text expected
@Right(@year(@Today);2) and @Right(@Month(@Today);2)

and also i cant see the generated sequential number, and also also the document number dosnt increment the value is always 0001.
 please check.
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10804326
Instead use this:
For year:
@Right(@Text(@year(@Today));2)
For Month:
@Right(@Text(@Month(@Today));2)

before u save, these 2 fields should have values.

make the view first columns as Ascending and try.

This logic should work.
0
 

Author Comment

by:lovemeto
ID: 10804408
FIELD serno_x:=serno_x;
FIELD srno_x:=srno_x;
FIELD type_k:=type_k;
yr:=@Right(@year(@Today;2));
mnth:=@Right(@Month(@Today;2));
type_k+"-"+@text(yr)+"-"+@text(mnth)+"-"+@Text(srno_x)

did i still need to used this formula above?
i still cannot see the generated document number,
and also the  srno_x didnt increment
thanks
0
 
LVL 19

Expert Comment

by:madheeswar
ID: 10804421
u send me ur template to madheeswar at vmoksha dot com

mention what are the forms and views and fields

0
 

Author Comment

by:lovemeto
ID: 10804452
sorry but i cant send you my template, please forgive me for that.

but u can send me a sample database, a very simple one. please.

delvalle_jocelyn09@yahoo.com

thanks
0
 
LVL 19

Accepted Solution

by:
madheeswar earned 500 total points
ID: 10804538
I have sent the template.


If I find time, I will explain u . Else, try to understand the forms and code in the fields.
0
 
LVL 15

Expert Comment

by:Bozzie4
ID: 10811865
Warnings first : There are some problems with sequential numbering, mostly that you can't be sure that you have unique numbers, especially if you use the application accross different servers.  1 good solution is to have 1 scheduled agent on 1 server that assigns the numbers periodically.

Now for a simple (not foolproof) solution (I assume your sequential number remains the same accross types).  NUMBER is the field name, it's text. HWTYPE is the field, text, for type.

- create a view TheView, with 1 column, sorted descending:
@if( NUMBER="";0;@texttonumber( @rightback( NUMBER; "-" ))

- now put in the QuerySave :

@if(!@isnewdoc & NUMBER != "";@return("");"");
res := @dbcolumn( "":"nocache"; @dbname ; "TheView" ; 1 );
res2 := @if(@iserror(res);1;@subset(res;1));
type := @left( HWTYPE;1);
length := @if( type = "C"; 4; type="S"; 4; 3 );
newNumber := type:@right(@text(@year(@today));2):@right("00"+@text(@Month(@today));2):@text((res+1));
FIELD NUMBER := @implode(newNumber;"-");
""

Or alternatively, in make NUMBER computed, text, and put it there (then omit the last "FIELD NUMBER :=" )

btw I have an application that does just that (inventory management), and where you can change the numbering per documenttype.  
drop me an email if you're interested.

Tom
0
 

Author Comment

by:lovemeto
ID: 10937540
im interested thanks for the help:

delvalle_jocelyn09@yahoo.com,

ill post new points for you after this. thanks.



0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to add a username for multiple users in Lotus 6 116
Lotus Domino Server Failure 1 187
lotus domino server console error 1 84
Lotus notes - Follow up notes mail missing 15 77
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
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…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 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