Link to home
Start Free TrialLog in
Avatar of Wynleigh
Wynleigh

asked on

How do I modify Formula Language code to select the max number from a view instead of the first number.

Hi all
I am a Java programmer and very new to Formula Language so I am struggling a bit. This code selects a quote number from the top of a column then adds 1 to it to produce a new quote number. The problem is that the numbers have gone all haywire after it reached 228, this year and last year, so now it is really a hashup! I would rather have it select the highest number in the column and add 1 to it. The field is a text field and the result it currently receives is: W09-9D which should be:  W09-229D. W = Wynleigh, 09 = year; 229 = Quote number; D = Location.
Any idea Why it dislikes W09-229D??
Looking forward to your expertise.
Wynleigh.
SRV:= @Subset(@DbName; 1);
DB:= @Subset(@DbName; -1);
VIEW:= "Quote Sequence";
VAL := @Subset( @DbColumn("":"NoCache"; SRV: DB; VIEW; 1);1);
NXTVAL := @TextToNumber(@Right( VAL; "-")) + 1;
LOCATION := @If(
@Contains(SRV; "OnTheBeach"); "D";
@Contains(SRV; "Team"); "J";
"L"+ @Left(@Right(@Name([CN]; @UserName); " "); 1));
TXTVAL := "W" + @Right(@Text(@Year(@Today)); 2) + "-" + @Text(NXTVAL) + LOCATION;
TXTVAL

Open in new window

Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

Is your view "Quote Sequence" properly ordered, i.e. by quote number, decreasing?

Is the view a hidden view? For if it's not, the user can temporarily alter the sort order to a different column.
Avatar of Wynleigh
Wynleigh

ASKER

The quote number column view is not hidden. The Quote Number was in order but because it came up with it's own strange numbers after W09-228D, it is no longer in order. It came up with W09-5D to W09-8D which is now at the top of the list. These are not repeated numbers as the first ones were W09-005D to W09-008D. The big problem is when it gets to W09-99D and then wants to go to W09-100D which already exists. Hope this is not confusing. This is what it looks like.
W09-8D
W09-7D
W09-6D
W09-5D
W09-228D
W09-227D
W09-226D
W09-225D etc.
I got all these quote numbers in a list, can I concatenate the three numbers after the hyphen and get the max number from these? I can change the documents with the single number format. I do not want the user to be able to select the quote number, this must be automatically generated.
I know I can just change the ones at the top and it should work fine but i do not want this to happen again, hence the max number selection and then add 1 to it.
Thanks
Wynleigh
You know this method to determine a unique key/value/code for any document is doomed to fail, sooner or later? Are there any replicas of this database? If there are, and two users create a new document at the same time, two new documents will get the same code. It is even possible that that happens in a single replica, depending on the moment you calculate the field.

Another reason for failure can be that there are Reader-fields used in the documents. If someone cannot read all documents, the formula above fetches the highest code he can see.

LBNL, Why not use a formula that always generates a key of the same length?? Like W09-0001D, etc.
You might try this in line 10:

TXTVAL := "W" + @Right(@Text(@Year(@Today)); 2) + "-" + @Right("000"+@Text(NXTVAL); 4) + LOCATION;
It gave me an Error. The quote number is used by a field called 'Subject' when creating a new quote.
Error says: 'Subject line layout error has resulted in a quote number calculation error. Proposal cannot be saved'.
Oh, but that's an error message of your own making. Apparently, during validation or so, that quote number is checked. Try with a 3 instead of a 4, so:

TXTVAL := "W" + @Right(@Text(@Year(@Today)); 2) + "-" + @Right("000"+@Text(NXTVAL); 3) + LOCATION;

All that modification does is it creates W09-001D instead of W09-1D, so the order will be correct all the time. The order in the column is text-based, asa character-by-character comparison, so W09-9D is shown before (higher) than W09-111D. The problem will disappear once you change the quote number to W09-009D.

I hope you don't get more than 999 quotes per year...
Yes I saw the validation that was causing the error. Sorry I did not write the code so still exploring it. I will give this a try on Thursday, gotta go, we are voting tomorrow so I will only be back on Thursday. Thank you very much for your help, I appreciate it. Get back to you on Thursday.
Wynleigh
No problem.

By the way, is voting such a difficult adventure that you need a full day for it??
I don't know how important is to you that the numbers are unique and sequential, but sequential numbering is not well supported in Lotus Notes.

Here is the thread that goes through all pitfalls:
https://www.experts-exchange.com/questions/22631498/Lotus-Designer-6-5-increment-value-as-unique-reference-within-one-DB.html


Mbo
Oh yes, voting can be quite a process. It did only take me 15 minutes but there were people still waiting in line at 9pm last night in the more remote areas. 23 million voters, lots of remote areas.

I did change the 4 to a 3 but still giving same error, i will have to try to understand and change the validation/translation code on that field. 'Subject' is the name of the field the quote number is displayed in on the UI.

'Quote Number Field' (Where the number is generated)
The Input Translation is as follows:
-------------------------------------------------
ThisField := QuoteNumber;
VAL := @Left( Subject; " ");
@If( ThisField = "" | ThisField != VAL;
VAL;
ThisField)
--------------------------------------------------

The Input Validation is as follows:
-------------------------------------------------
ThisField := QuoteNumber;
@If( ThisField != "" ;
@Success;
@Failure("Subject line layout error has resulted in a quote number calculation error. Proposal cannot be saved."))
------------------------------------------------
The validation looks pretty straight forward to me, I think the problem may lie with the Input Translation. I did read up about what that is. I may have to read it again!

'Subject Field' (Where the number is passed to the UI)
The Default is as follows:
-------------------------------------------------
QuoteNumber + " "
--------------------------------------------------

The Input Translation is as follows:
-------------------------------------------------
@ReplaceSubstring(@Trim(Subject); @Char(13); " ")
------------------------------------------------
No validation on the 'Subject' field.

Hope this is clear enough mbonaci.
Wynleigh


Thank you mbonaci. I read the thread and i see it is not as simple as it would be with Java.
I will try to implement your valued solution as well today.
Apparently, the field Subject should start with the contents of the field QuoteNumber, so if QuoteNumber contains "W09-001D", Subject should be like "W09-001D abacadabra". Where are the contents of field Subject generated?
The Default value for Subject field is:
QuoteNumber + " "

There are no other formulas for this field.
So I am assuming it gets the value from the field we have been working on - 'Quote Number'.
The value of Subject could be calculated elsewhere, in any formula in the form, or in the QueryRecalc or PostRecalc events on the form. It might play hard to find, so you could try to use the Synopsis in the Designer. Select only this form, get its contents, and search for Subject.
I do not know how to use the Synopsis.
I select 'Define Content', 'Forms' but when I click 'OK' it says I must select at least one item. What item??? Everything seems to be selected. Forgive my inabilities.
ASKER CERTIFIED SOLUTION
Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
To close this question would indeed be the correct thing to do, Wynleigh. I cannot comment on my own answers in this case, they seem correct but only you know if the posts really helped you solve the problem.

@cs97jjm3: please don't assume...
I suggest equal split sjef_bosman & mbonaci.