• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 520
  • Last Modified:

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

0
Wynleigh
Asked:
Wynleigh
  • 9
  • 7
  • 3
2 Solutions
 
Sjef BosmanGroupware ConsultantCommented:
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.
0
 
WynleighAuthor Commented:
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
0
 
Sjef BosmanGroupware ConsultantCommented:
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Sjef BosmanGroupware ConsultantCommented:
You might try this in line 10:

TXTVAL := "W" + @Right(@Text(@Year(@Today)); 2) + "-" + @Right("000"+@Text(NXTVAL); 4) + LOCATION;
0
 
WynleighAuthor Commented:
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'.
0
 
Sjef BosmanGroupware ConsultantCommented:
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...
0
 
WynleighAuthor Commented:
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
0
 
Sjef BosmanGroupware ConsultantCommented:
No problem.

By the way, is voting such a difficult adventure that you need a full day for it??
0
 
mbonaciCommented:
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:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/Lotus_SmartSuite/Lotus_Notes/Q_22631498.html


Mbo
0
 
WynleighAuthor Commented:
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


0
 
WynleighAuthor Commented:
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.
0
 
Sjef BosmanGroupware ConsultantCommented:
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?
0
 
WynleighAuthor Commented:
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'.
0
 
Sjef BosmanGroupware ConsultantCommented:
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.
0
 
WynleighAuthor Commented:
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.
0
 
Sjef BosmanGroupware ConsultantCommented:
Some tips:
- study the Designer Help database
- inabilities are forgiven if you start to reading the Help database; it contains everything (but it may sometimes be hard to find...)
- select Choose Design Elements ('coz that's what you're after)
- under Type, select Forms
- click the form you want to inspect, then click Add to make it appear in the Selected Elements field at the right
- OK

Ask, by all means!
0
 
mbonaciCommented:
It's easier to use DDSearch (it's free and it's great):
http://www-10.lotus.com/ldd/sandbox.nsf/ecc552f1ab6e46e4852568a90055c4cd/f090b24ea76b1c0100256c45003e6396?OpenDocument&Highlight=0,search


Or even this one (I've been using it for 5 years now - see the image), if you're willing to pay for it:
Teamstudio Configurator
http://www.teamstudio.com/new/products/configurator.html

Confy.gif
0
 
Sjef BosmanGroupware ConsultantCommented:
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...
0
 
mbonaciCommented:
I suggest equal split sjef_bosman & mbonaci.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now