Searching for max values

[jscotts]

Sorry if this is an easy questions but I have tried to figure this one out.

I have a real estate database. I am trying to search by a max value. I have set up a drop down list with different price levels ($25,000, $50,000 etc). I have tried the max value script but its not working. I am sure its operator error. Any help would be greatly appreciated.

[Lee Smith]

From the Online Help.

Returns the highest valid value in:

a [color:blue]repeating field (repeatingField).

a field in matching [color:blue]related records specified by (table::field), whether or not these records appear in a portal.

several non-repeating fields in a record ([color:blue]field1;field2;field3...).

corresponding repetitions of repeating fields in a record (repeatingField1;repeatingField2;repeatingField3), if the result is returned in a repeating field with at least the same number of repeats.

several fields in the first matching record specified by ([color:blue]table::field1;table::field2;...). You can include fields from different tables (table 1::field A;table 2::field B...).

So, you would need to do a self relationship and then use the Max Function through the Relationship.

HTH

Lee

[LaRetta]

There are too many unanswered questions here. "I am trying to search by a max value."

Are you searching a table for all records with a value in a Price field UP TO the value you select from your drop-down list; or by records which fall within a range such as between 25,001 and 50,000? If Lee's suggestion doesn't fit the bill, provide more information about your needs. :wink2:

[jscotts]

I am searching a table for all records with a value in a Price field UP TO the value you select from a drop-down list. Thanks for the help.

[LaRetta]

Use a global number (named gPrice) and attach your drop-down to that. Then script would be something like:

If [ not gPrice ]

Show Custom Dialog [ "You need to enter a max price" ]

Halt Script

End If

Set Error Capture [ On ]

Enter Find Mode [ ] (uncheck the Pause)

Set Field [ Price ; ":less:" & gPrice ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ "No records found" ]

... do whatever if no records found

Else

... do whatever with your found set

End If

You can also establish a relationship from this gPrice in your main table to the Price field in another table (or the same table). It's a bit faster and might work better in a drop-down because the results could happen immediately after the drop-down selection (by using a portal) with no need to click a FIND button. Be sure gPrice and Price are same data-type. Join MainTable::gPrice :great: otherTable::Price. If your price is in the same table, create another table occurrence of it and use THAT as your otherTable. You can then use a portal to display the results (automatic) or script a Go To Related Record Records [ Show only related records from "otherTable" ] which switchs to a list or form view of the resulting data-set. Be sure to check box 'Show Only Related' and 'Match Current Record Only.'

You haven't explained how you will be utilizing this search so I can't suggest which method might work best for you.

:smile2: