Create a unique tracking number for the document with this format (XXXXX-YY-MM)
this is to create a tracking number of the document in lotus notes. when the user save the document it will automatically create a tracking number for the document with this format:
XXX-YY-MM-DD
where
XXXXX - counter, # of documents saved (ex. 00001)
YY - current year the doc is saved
MM - month
DD - day
the counter will reset back to 001 every 1st day of the month.
can it be done?
this is currently my formula for the counter:
Counter1 := @Elements(@DbColumn("":"ReCache"; "":"" ; "($CounterLookup2)"; 2)) + 1;
@If(Counter1 < 10; FIELD Counter := "0000" + @Text(Counter1) ;
Counter1 < 100; FIELD Counter := "000" + @Text(Counter1) ;
Counter1 < 1000; FIELD Counter := "0" + @Text(Counter1) ; NIL)
and this is my formula for the complete tracking number:
@If((@Text(@Month(Dt_Received_EA))) = "1"; Mnth := "01"; (@Text(@Month(Dt_Received_EA))) = "2"; Mnth := "02";
(@Text(@Month(Dt_Received_EA))) = "3"; Mnth := "03"; (@Text(@Month(Dt_Received_EA))) = "4"; Mnth := "04";
(@Text(@Month(Dt_Received_EA))) = "5"; Mnth := "05"; (@Text(@Month(Dt_Received_EA))) = "6"; Mnth := "06";
(@Text(@Month(Dt_Received_EA))) = "7"; Mnth := "07"; (@Text(@Month(Dt_Received_EA))) = "8"; Mnth := "08";
(@Text(@Month(Dt_Received_EA))) = "9"; Mnth := "09"; (@Text(@Month(Dt_Received_EA))) = "10"; Mnth := "10";
(@Text(@Month(Dt_Received_EA))) = "11"; Mnth := "11"; (@Text(@Month(Dt_Received_EA))) = "12"; Mnth := "12";NIL);
Yr := @Text(@Year(Dt_Received_EA));
Count := @Text(Counter);
FIELD SIR_TrNo_EA := Count + "-" + Mnth + "-" + @Right(Yr;2) ;
im currently using lotus domino designer 7.
your help is badly needed..
thanks a lot!
;)
Software/Hardware used:
Software/Hardware used:
ASKED:
August 20, 2008 1:47 AM
UPDATED:
May 11, 2012 12:41 PM
RELATED QUESTIONS
Answer Wiki:
This answer is based upon the following assumptions:
<ol>
<li>The field <i>SIR_TrNo_EA</i> is a computed field on the form.</li><li>Dt_Received_EA is a Date/Time field, and it is available/populated at the time the document is to be saved. If not, then this code will not work.</li>
</ol>
Also...please see the explanation for the @DbLookup at the end.
Put this piece of code at the top of your formula. It keeps the <i>SIR_TrNo_EA</i> field from updating with every save:
@If(SIR_TrNo_EA != "";
@Return(SIR_TrNo_EA);
"");
month := @If(@Month(Dt_Received_EA) < 10;
"0" + @Text(@Month(Dt_Received_EA));
@Text(@Month(Dt_Received_EA)));
day := @If(@Day(Dt_Received_EA) < 10;
"0" + @Text(@Day(Dt_Received_EA));
@Text(@Day(Dt_Received_EA)));
yr := @Right(@Text(@Year(Dt_Received_EA)); 2);
key := month + "-" + yr;
lkup := @DbLookup("" : "ReCache"; @DbName; "($CounterLookup2)"; key; 2);
ctr := @If(@IsError(lkup);
"1";
@Text(@Elements(lkup) + 1));
trackNum := @If(@Length(ctr) < 4;
@Repeat("0"; (4 - @Length(ctr)));
@Right(ctr; 4));
trackNum + "-" + yr + "-" + month + "-" + day
<b>NOTE:</b>
For the @DbLookup to work, you want to either change the "($CounterLookup)" view's first column so that it is categorized and in the format of "MM-YY". (I guess that comes from the <i>Dt_Received_EA field</i>? It doesn't matter what's in any other column...just make sure that the second column is populated since you're trying to return the "values" found in that column (not really...the goal is to get the number of documents found). Or...you would want to create a new view whose first column meets that criteria, then change the referenced view in the lkup variable above. A better format for the first column would be "MM-YYYY" so that you don't have revisit the view every 100 years :-)
The key is that you look at the number of documents created/saved in a particular month. If the lookup fails (there were no documents created/saved in that month) it returns "1"; or else it adds 1 to the number, etc, etc.
Hope this helps.
To see all answers submitted to the Answer Wiki: View Answer History.
i need to create my own tack number tell you how to create it