This answer is based upon the following assumptions:
<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>
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 != “”;
month := @If(@Month(Dt_Received_EA) < 10;
“0” + @Text(@Month(Dt_Received_EA));
day := @If(@Day(Dt_Received_EA) < 10;
“0” + @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);
@Text(@Elements(lkup) + 1));
trackNum := @If(@Length(ctr) < 4;
@Repeat(“0”; (4 – @Length(ctr)));
trackNum + “-” + yr + “-” + month + “-” + day
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.