(UTC)
  Lantica User Forum › Archived Topics › Archived Topics › finding maximum of IDs in database
(Moderator: Forum Admin)
‹ Previous Topic | Next Topic ›
Page Index Toggle Pages: 1 Send Topic Send Topic Print Print
Hot Topic (More than 10 Replies) finding maximum of IDs in database (Read 1862 times)
FSGROUP
Full Member
***
Offline


No personal text

Posts: 179
Location: Edmonton, Alberta, Canada
Joined: Jan 14th, 2004
finding maximum of IDs in database
Mar 1st, 2004 at 5:25pm
Print Post Print Post  
I have a field named ID of type Number in a form. I'd like to find the maximum value existing in the database.  Clues on how to do this? I tried using @Max and @XListvalues but they only work on the current value of the field and on strings(respectively)

Thanx
  
Back to top
WWW WWW  
IP Logged
 
Bharat_Naik
Senior Member
Members
*****
Offline


Ever ready to learn and
share

Posts: 1202
Location: Chicago,  Illinois
Joined: Dec 16th, 2003
Re: finding maximum of IDs in database
Reply #1 - Mar 1st, 2004 at 5:55pm
Print Post Print Post  
var vNumber as Int


vNumber = @xlookupR (@fn, 9999999999, "Id", "Id")

WriteLn (vNumber)
  
Back to top
 
IP Logged
 
Justin_ICC
Junior Member
**
Offline



Posts: 95
Joined: Feb 5th, 2004
Re: finding maximum of IDs in database
Reply #2 - Mar 1st, 2004 at 7:59pm
Print Post Print Post  
I can't remember but is it possible to use multiple fields in the search paramters of this? ie I want to pull up the maximum Note ID for a specific client # (2 keyword)?
  
Back to top
 
IP Logged
 
The Cow
YaBB Administrator
*****
Offline



Posts: 2530
Joined: Nov 22nd, 2002
Re: finding maximum of IDs in database
Reply #3 - Mar 1st, 2004 at 9:22pm
Print Post Print Post  
Quote:
I can't remember but is it possible to use multiple fields in the search paramters of this? ie I want to pull up the maximum Note ID for a specific client # (2 keyword)?



See this thread:
http://www.lantica.com/Forum2/cgi-bin/yabb/YaBB.pl?board=gen_disc;action=display...
  
Mark Lasersohn&&Programmer&&Lantica Software, LLC
Back to top
WWW WWW  
IP Logged
 
FSGROUP
Full Member
***
Offline


No personal text

Posts: 179
Location: Edmonton, Alberta, Canada
Joined: Jan 14th, 2004
Re: finding maximum of IDs in database
Reply #4 - Mar 5th, 2004 at 10:35pm
Print Post Print Post  
If i use the following in On Form Entry and the largest ID in the database is currently 1620:

var vNumber as Int

if @IsNew or Id="" or @Add then
{
vNumber = @xlookupR (@fn, 9999999999, "Id", "Id")
Id=vNumber +1
}

then when I create a new record in Add Mode, I get ID=1621.  However, if i try to add another record, ID should now be 1622 correct? I keep getting ID=1621 however.  Does the new ID of 1621 not get saved to the DB the next time I execute the above code?

Thanx for any assitance.
  
Back to top
WWW WWW  
IP Logged
 
Bharat_Naik
Senior Member
Members
*****
Offline


Ever ready to learn and
share

Posts: 1202
Location: Chicago,  Illinois
Joined: Dec 16th, 2003
Re: finding maximum of IDs in database
Reply #5 - Mar 5th, 2004 at 10:45pm
Print Post Print Post  
Quote:
var vNumber as Int

if @IsNew or Id="" or @Add then
{
vNumber = @xlookupR (@fn, 9999999999, "Id", "Id") 
Id=vNumber +1
}


xlookup always gets value in text (i. e. string). You will have to convert it to number before using in calculation.

if @IsNew or Id="" or @Add then
{
vNumber = @tonumber (@xlookupR (@fn, 9999999999, "Id", "Id")) 
Id=vNumber +1
}


Please note the addition of @tonumber.
  
Back to top
 
IP Logged
 
FSGROUP
Full Member
***
Offline


No personal text

Posts: 179
Location: Edmonton, Alberta, Canada
Joined: Jan 14th, 2004
Re: finding maximum of IDs in database
Reply #6 - Mar 8th, 2004 at 5:15pm
Print Post Print Post  
thanx for the tip Bharat_Naik. does this code work for you when generating unique IDs?

Seems the code works for me, but only when adding the first new record. After I save, or press F10, it generates the same ID as before (ie., the maximum ID has not changed).  I tried saving the app. and reopening it and I still get the same ID when adding a new record.

Stumped,
  
Back to top
WWW WWW  
IP Logged
 
Bharat_Naik
Senior Member
Members
*****
Offline


Ever ready to learn and
share

Posts: 1202
Location: Chicago,  Illinois
Joined: Dec 16th, 2003
Re: finding maximum of IDs in database
Reply #7 - Mar 8th, 2004 at 5:31pm
Print Post Print Post  
Yes, this code works for me and I like it a lot. I am surprised that it does not work for you. Do you mind to give us a little detail as to the type of field ID is and how are you using it? What is the largest number of your ID at the present time?
  
Back to top
 
IP Logged
 
FSGROUP
Full Member
***
Offline


No personal text

Posts: 179
Location: Edmonton, Alberta, Canada
Joined: Jan 14th, 2004
Re: finding maximum of IDs in database
Reply #8 - Mar 8th, 2004 at 7:09pm
Print Post Print Post  
ID is of type Number. I'm using it as a relational value to link form to subform (hence being unique is essential). 

The largest ID currently is 1620. When I add a new record I get ID=1621 (which is correct), but after saving that record and adding another, I always get ID=1621. 
I thought perhaps the ID wasn't being saved, but if I do a search, the record for ID=1621 is found.
  
Back to top
WWW WWW  
IP Logged
 
Bharat_Naik
Senior Member
Members
*****
Offline


Ever ready to learn and
share

Posts: 1202
Location: Chicago,  Illinois
Joined: Dec 16th, 2003
Re: finding maximum of IDs in database
Reply #9 - Mar 8th, 2004 at 8:01pm
Print Post Print Post  
It should work for you. I even made one small application with two elements and placed exactly the same code and it works fine.  I am thinking of couple of things you could look into:

1. It involves x-family functions, so it will not work in preview.

2. If you have more than one form in the application, you will have to put form name in front of first Id, like "FormName!Id"

Please let us know if it works or not. If not I will send you test counter application to test it out for yourself.
  
Back to top
 
IP Logged
 
FSGROUP
Full Member
***
Offline


No personal text

Posts: 179
Location: Edmonton, Alberta, Canada
Joined: Jan 14th, 2004
Re: finding maximum of IDs in database
Reply #10 - Mar 8th, 2004 at 8:47pm
Print Post Print Post  
My main form has element named ID, but all the SFs that reference it are named CID, so appending the form name to the XLR
  
Back to top
WWW WWW  
IP Logged
 
FSGROUP
Full Member
***
Offline


No personal text

Posts: 179
Location: Edmonton, Alberta, Canada
Joined: Jan 14th, 2004
Re: finding maximum of IDs in database
Reply #11 - Mar 8th, 2004 at 8:49pm
Print Post Print Post  
My main form has element named ID, but all the SFs that reference it are named CID, so appending the form name to the XLR call is not the problem.  I tried it anyways, and I always got max ID=0. weird.

Also, I'm not working in preview.

Could u please send that test app? Thanx so much.
  
Back to top
WWW WWW  
IP Logged
 
Bharat_Naik
Senior Member
Members
*****
Offline


Ever ready to learn and
share

Posts: 1202
Location: Chicago,  Illinois
Joined: Dec 16th, 2003
Re: finding maximum of IDs in database
Reply #12 - Mar 9th, 2004 at 2:57pm
Print Post Print Post  
In what element and event are you putting this code? Place it in form entry event. In order for sending test app, you will have to send me your e-mail as I cannot attach file through e-mail message here.
  
Back to top
 
IP Logged
 
FSGROUP
Full Member
***
Offline


No personal text

Posts: 179
Location: Edmonton, Alberta, Canada
Joined: Jan 14th, 2004
Re: finding maximum of IDs in database
Reply #13 - Mar 9th, 2004 at 4:30pm
Print Post Print Post  
yes, the code is in my main form, on form entry. My address is hayley@fsgroup.ca

Thanx again.
  
Back to top
WWW WWW  
IP Logged
 
Page Index Toggle Pages: 1
Send Topic Send Topic Print Print
‹ Previous Topic | Next Topic ›
 
  « Board Index ‹ Board Top  

Lantica User Forum » Powered by YaBB 2.6.11!
YaBB Forum Software © 2000-2024. All Rights Reserved.

Valid RSS Valid XHTML Valid CSS Powered by Perl Source Forge