Solved

generate sequential numbering.

Posted on 2004-04-11
17
442 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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
  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

929 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

15 Experts available now in Live!

Get 1:1 Help Now