Newbies saad Posted August 10, 2007 Newbies Share Posted August 10, 2007 Hi, I have a customer table with customer ID (Number auto enter) as a unique key. I am writing a script which searches the customer table and brings the record with the MAX value of customer ID but this script doesnt bring any record. Below is my code for the script. Customer table is not related to any other table. Go to Layout["customer" (customer)] Enter Find Mode[] Set Field [customer::CustomerID; Max(customer::customerID)] Perform Find[] Sort Records[No Dialog] Go to Record/Request/Page [First] Enter Preview Mode [Pause] Enter Browse Mode [] Go to Layout [original layout] thanks Link to comment Share on other sites More sharing options...
Vaughan Posted August 10, 2007 Share Posted August 10, 2007 First of all, the CustomerID field would need to be of type number, not text, otherwise it'll sort alphabetcally (1, 10, 100, 2, 20, 200) and not numerically (1, 2, 10, 20, 100, 200). But since the CustomerID is autoentered serial number, the biggest one will be the last one when unsorted. Link to comment Share on other sites More sharing options...
Newbies saad Posted August 10, 2007 Author Newbies Share Posted August 10, 2007 customerID is a number not text. Max function doesn't bring any value so search doesn't work. if i replace max customerid with any number, say 10. the search brings the record matching customerId 10. so please help with sorting this max function problem. thanks Link to comment Share on other sites More sharing options...
Vaughan Posted August 10, 2007 Share Posted August 10, 2007 Why are you doing all that find and sort stuff? The record with the max value of CustID will *always* be the last record (unless there has been a import and the IDs are screwed up). Find All Records Unsort Go to Record/Request [ last ] But OK, let's sort out your script. The problem could be that the Max() function is being calculated in Find mode: there aren't any records available in Find mode. So do it in browse mode and put the value into a script variable: Go to Layout["customer" (customer)] Set Variable [ $CustID; Max(customer::customerID) ] Enter Find Mode[] Set Field [ customer::CustomerID; $CustID ] Perform Find[] Sort Records[No Dialog] Go to Record/Request/Page [First] Enter Preview Mode [Pause] Enter Browse Mode [] Go to Layout [original layout] Link to comment Share on other sites More sharing options...
LaRetta Posted August 10, 2007 Share Posted August 10, 2007 Max() - when used within the SAME table, will always only display the current record for comparison; so it can never show more than that ID' result. Max() works on related tables or repeating fields or many fields within the same record. But yes, you can simply unsort and go to the last record. But you can also find it by: Enter Find Mode [ ] Set Field [ yourID ; GetNextSerialValue ( "Customer" ; "CustomerID" ) - 1 Perform Find [ ] But why,oh why,are you doing this? I can think of NO SITUATION in which I've ever ever needed to know the last ID number! Not only that, your first script includes a sort. But why sort when there should only be ONE unique CustomerID in the Customer table? If you enlighten us a bit more, we might be able to save you some headache. LaRetta Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6231 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now