Script with MAX function

[saad]

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

[Vaughan]

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.

[saad]

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

[Vaughan]

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]

[LaRetta]

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