Jump to content

Script with MAX function


This topic is 6162 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

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

  • Newbies

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

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

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. :wink2:

LaRetta

Link to comment
Share on other sites

This topic is 6162 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.