We help IT Professionals succeed at work.

generate sequential numbering.

lovemeto
lovemeto asked
on
Medium Priority
511 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.










Comment
Watch Question

this can be done with simple logic.

I will give the solution, wait for a minute to type ...
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)
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.

Author

Commented:
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


 











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.
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)

u just try the above in the mean time and I will catch u within one hour.

Thanks.

Author

Commented:
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.

Author

Commented:
ignore the comments above i already found the error. its only "Computerview"

Author

Commented:
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.
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.

Author

Commented:
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
u send me ur template to madheeswar at vmoksha dot com

mention what are the forms and views and fields

Author

Commented:
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
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Bozzie4IT Architect

Commented:
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

Author

Commented:
im interested thanks for the help:

delvalle_jocelyn09@yahoo.com,

ill post new points for you after this. thanks.



Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.