generate sequential numbering.

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.










lovemetoAsked:
Who is Participating?
 
madheeswarConnect With a Mentor Commented:
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
 
madheeswarCommented:
this can be done with simple logic.

I will give the solution, wait for a minute to type ...
0
 
madheeswarCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
madheeswarCommented:
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
 
lovemetoAuthor 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


 











0
 
madheeswarCommented:
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
 
madheeswarCommented:
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
 
madheeswarCommented:
u just try the above in the mean time and I will catch u within one hour.

Thanks.
0
 
lovemetoAuthor 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.
0
 
lovemetoAuthor Commented:
ignore the comments above i already found the error. its only "Computerview"
0
 
lovemetoAuthor 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.
0
 
madheeswarCommented:
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
 
lovemetoAuthor 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
0
 
madheeswarCommented:
u send me ur template to madheeswar at vmoksha dot com

mention what are the forms and views and fields

0
 
lovemetoAuthor 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
0
 
Bozzie4Commented:
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
 
lovemetoAuthor Commented:
im interested thanks for the help:

delvalle_jocelyn09@yahoo.com,

ill post new points for you after this. thanks.



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.