I've got a client database working, and I need to have a field called 'ClientNo' that needs to get a sequential number put into it.

The behavior I want is this:

User opens the "CLIENTS" form in Add mode, and so 'On-Form-Entry' I want to put a global value 'gvClientNo' into the 'ClientNo' field on the form.

Then, when the user finally saves the record 'On-Form-Exit' I want the programming to use XLOOKUPR to determine the highest value for 'ClientNo' in the database, to be sure that another user hasn't already saved a record with the same value. If there's a problem I would bump up the ClientNo by one, and then save and update the globalvalue.

The problem I'm running into is the @XLOOPUPR() function. Everytime I use it, I only get zero back. I'm using the programming from the one-sheet on numbering, which can be found here: http://www.lantica.com/Support/sesame_onesheet_11.html

// Only assign the number if
// this is a new record which
// has been changed.

var vNextNum as Int

If(@IsNew)
{
	If(@Modified)
	{
		vNextNum = @ToNumber(@XLookupR(@FN, "9999999", "Key", "Key")) + 1
		If Not @Error
		{
			Key = vNextNum
		}
	}
} 

So my programming looks like this:

var vNextNum as Int
vNextNum = @ToNumber(@XLookupR(@FN, "9999999", "ClientNo", "ClientNo"))
		If Not @Error
		{
			ClientNo = vNextNum
				Write("The new ClientNo is: " + vNextNum)
		}
 

But when I run it, all I get is '0' as the value of vNextNum.

What am I doing wrong with XLookupR?

The ClientNo field is a Number field, yes. I tried it without quotes, and it still doesn't work. I do have other forms in the application, so I tried declaring the external key and the source as "CLIENTS!ClientNo" both with and without quotes, and for some reason it won't work.

Quote:

This is a copy of real working code (except for the form name for clarity). You do not want to use the form name in the source parameter - just the LE name.

RecordNo = @TN(@XLookupR(@Fn, 999999, "MyFormName!RecordNo", "RecordNo")) + 1 

Also, if the applicaton is secured, you need to provide an "X User" name and password.

Bharat_Naik wrote on Mar 23^rd, 2008 at 7:39am:

That is not correct. An XLookup to the same application will still fail if there is no "X User" info, and the app is secured.


(I didn't mean that to appear like a scolding, I just wanted it to be clear.  )

Thank you, everyone, for the input. When I go into Security Settings and put my Admin settings (username/password) in as the 'X User' then it starts to work. I'm trying to use both methods because I figure it's even more secure. I would prefer to use a globalvalue, because then I could adjust the numbers if needed, but figured using both methods might be worth trying.

I see your point Bob, I'll have to take another look at it.