Find the largest value in a list of records

[yknot]

I would like to find the largest number/value in a long list of fields such as the latest date in field "date". This value is then to be used as a criteria in a find request. I know I can sort and then go to the last record but there must be something more elegant out there. Any suggestions?

[Reed]

What about using a relationship and using the max() function to find the maximum related value?

I've attached a simple example file.

Dana

Untitled.fp7.zip

[Søren Dyhr]

Dana have you considered a found set of records??? If the issue is to find the max value in a set of records gathered by a previous find, must a summary field be defined as the max of dates and a constant as a breaker, to make GetSummary[ have the correct number of parameters.

If [ IsEmpty ( Get ( ScriptParameter ) ) ] 

         Sort Records [ Speci__ed Sort Order: respectsFS::constant; ascending ] [ Restore; No dialog ] 

         Perform Script [ "New Script"; Parameter: GetSummary ( respectsFS::theSummaryFunc;respectsFS::constant ) ] 

Else 

         Enter Find Mode [  ] 

         Set Field [ respectsFS::aDate; Get ( ScriptParameter ) ] 

         Constrain Found Set [  ] 

End If 

--sd

[Reed]

I guess I didn't read the post carefully, I was considering the related records in a portal to be a "found set".

It seems the original question is even easier, and that simply defining a summary field as the max of the date field will return the latest date in the found set, no sorting required.

Dana

[Søren Dyhr]

no sorting required.

But it requires a Commit Statement to update if you're in the date field, and if you need to single it out via a script is it going to be done this way - since copy/paste harms your clipboard:

Commit Records/Requests 

If [ IsEmpty ( Get ( ScriptParameter ) ) ] 

     Perform Script [ "New Script"; Parameter: fff::theSummary ] 

Else 

      Enter Find Mode [  ] 

      Set Field [ fff::aDate; Get ( ScriptParameter ) ] 

      Constrain Found Set [  ] 

End If 

--sd

[yknot]

Thanks for the help guys. I tried Dana's suggestion and it works as long as I don't enter a new record with a higher date, but the moment I do that all the data in summary field "maxdate" disappears. I will try SD's routine but don't quite understand it. How about a little more explanation SD?

Regards, yknot

[Søren Dyhr]

it works as long as I don't enter a new record with a higher date, but the moment I do that all the data in summary field "maxdate" disappears

This is exactly why I have Commit[ in first line, which is similar to a click outside a field.

What I took notion of, was that you wrote:

This value is then to be used as a criteria in a find request.

This what the script performs somewhere along the line of a series of requests, my guess was that you've made a search for something specific, and wished to know which records in the found set holds the max value, here the newest.

You could cheat and let the script do it like this:

Copy [ Untitled::theSummary ] [ Select ] 

Enter Find Mode [  ] 

Paste [ Untitled::aDate ] [ Select ] 

Constrain Found Set [  ] 

...but only the careless developers are tempted by this, since it harms the users clipboard!! So I use a simple recursion (the script calling itself) in the script to pick the value in the summary field in first cycle when the paramter is empty. The second time around is the value put into the datefield and the found set is constrained. Contrain is Filemakers phrase for an AND search!

--sd