Bob, I use this code all the time and it works for me. As a matter of fact I have been using this for last several years for counters.

1. In Sesame you have to use @tonumber even though field is Number type because xlookup value is always string (text)  type.

2. If the ID# has exeeded 6 digits then you will have to use more "9"s.

3. If you have more than one form in the application, you will have to use the Form name in front of first ID.

4. You have to save it into .db and test .db as preview would not work with x-family commands.

5. Use with following code on Form entry event
If  @IsNew and ID = "" Then
ID= @Tonumber(@XLookupR(@Fn, 999999, "ID", "ID")) + 1


Even after doing this, if it does not work for you, I will send you one of my applications with this working properly.

The formula you use seems to be all right.  

I misunderstood you.  I thought, it did not work for you. I did not realize that you were pressing the issue about incorrect FAQ.

In Q&A, it would work as it is given in FAQ.  In Sesame, xlookup value is always in string so, we have to use Typecasting function, @tonumber to convert into number. 

I agree with you whole-heartedly that FAQ should be amended. Once again, I apologize for the misunderstanding.

OK. Yes. You're right! - The FAQ is wrong!  And it's my fault.  I didn't check it in Sesame.  'Spose it's Guantanamo bay for me   Never mind, they say it's nice there this time of year.

The reason for the discrepancy is, as you say Robert, the strict typecasting of Sesame.  Lookups and XLookups ALWAYS return text, in Q&A as well as Sesame.  It's just that Q&A has the ability to be able to mix'n'match them and make an educated guess as to what you want, and gets it right some of the time. Sesame applies strict rules. It's more rigourous, and it's more logical.

The following will work, if the LE "Number1" is bound to a Number field..

IF Number1 = "" THEN Number1 = @TN(@XLR(@fn,99999,"Number1","Number1"))+1 

Notice that it's the XLookupR that has the @ToNumber wrapped around it. The "+1" is outside the parentheses.

The field being incremented MUST be a number field.  If you use a text field it will seem to work but will, eg in the example I used, get stuck at 10.  This is because Sesame is not sorting the text values as numbers and determining the highest value.  It sorts 9 higher than 10 because it starts with a 9: it's sorting left to right.

I'll get the FAQ changed forthwith.

A question: would you like programming examples to use the full names of functions, or abbreviations. These two are the same...

IF Number1 = "" THEN Number1 = @TN(@XLR(@fn,99999,"Number1","Number1"))+1 

IF Number1 = "" THEN Number1 = @ToNumber(@XLookupR(@fn,99999,"Number1","Number1"))+1 

My view is that the full version is recognisable to more users and is preferred.

BTW - don't forget that you can't check this is preview mode. XLookups, and all the X family (XUserSelect etc), do not work in preview mode.  This includes Lookup & @Lookup (internal lookup table), as it's actually an XLookup to another Sesame database so is an XLookup under the bonnet.

I like the FULL name to please.