limiting find to first 5 records

[Robert Collins]

I am trying create a script that does the following

performs a find based on my criteria

sorts records based on 'qty sold'

then exports only the first 5 records to a csv file.

I have scripted the find, sorted based on 'qty sold' and the export to a file.

The only bit I don't know how to do is constrain the found set to only the top 5 records.

Any help would be appreciated.

thanks

[IdealData]

Look at the Omit Records script step.

[LaRetta]

Hi Robert:

Here is one approach ...

Perform your find (include error capture to test for no records found)

Sort (without dialog) descending on qty. sold

Go To Record/Request [ without dialog ; by calculation ; 6 ]

Omit Multiple [ without dialog ; by calculation ; Get ( FoundCount ) - 5 ]

Perform your export

[Robert Collins]

Hi Robert:

Here is one approach ...

Perform your find (include error capture to test for no records found)

Sort (without dialog) descending on qty. sold

Go To Record/Request [ without dialog ; by calculation ; 6 ]

Omit Multiple [ without dialog ; by calculation ; Get ( FoundCount ) - 5 ]

Perform your export

That works beautifully , thanks

[TheTominator]

Don't forget to handle the case where 5 or less records come up in the search.

[LaRetta]

Thank you for the catch!!

Robert, I would probably adjust the script like so (adding the blue) -untested:

Perform your find (include error capture to test for no records found)

Sort (without dialog) descending on qty. sold

[color:blue]If [ Get ( FoundCount ) > 5 ]

Go To Record/Request [ without dialog ; by calculation ; 6 ]

Omit Multiple [ without dialog ; by calculation ; Get ( FoundCount ) - 5 ]

[color:blue]End If

Perform your export

[jill]

I am trying to do about the same type of find, but need to find the top 4 scores in each school. (Each school may have 1-15 scores).

How do I sort by school and score, and then find only the schools with 4 or more scores, and only show the top 4 scores?

[Robert Collins]

Thank you for the catch!!

Robert, I would probably adjust the script like so (adding the blue) -untested:

Perform your find (include error capture to test for no records found)

Sort (without dialog) descending on qty. sold

[color:blue]If [ Get ( FoundCount ) > 5 ]

Go To Record/Request [ without dialog ; by calculation ; 6 ]

Omit Multiple [ without dialog ; by calculation ; Get ( FoundCount ) - 5 ]

[color:blue]End If

Perform your export

I will do that , thanks

as far as the export goes, I had it working fine on my pc (to a local folder) but as this script will be run on Server 9 I need to change the path so the exported file resides on my servers HD

this is the path that I have set, but it causes a script error

filewin://SERVER/Documents and Settings/Robert Collins/My Documents/Server documents/top5contract.csv

i have also tried:

filewin:/C:/Documents and Settings/Robert Collins/My Documents/Server documents/top5contract.csv

but that doesn't work either

any ideas what i am doing wrong?

thanks again

[LaRetta]

This link should explain it. Start at the top. :smirk:

[Robert Collins]

This link should explain it. Start at the top.

Ok, I see now that the export has to be either within the Filemaker Server Documents folder or a temporary folder - so with that in mind I tried this after having created a Documents folder in the FileMaker Server folder.

filewin://SERVER/Program Files/Filemaker/Filemaker Server/Documents/top5acontract.csv

and still no joy

I've also tried a variable

$path

with a value of

"filewin:" & Get (DocumentsPath) & "top5contracts.csv"

and then using the $path value as the export path.

This works with my client PC but fails on the Server 9 script

Edited December 14, 2009 by Guest

[LaRetta]

Hi Jill,

I don't know how you are structured but I will assume you have a table with school listed more than one and a score associated with it. Hopefully you also have a School table but it can all be handled from this single file, I believe.

Give this attached sample file a shot and see if it works for you. I'm sure there are more elegant scripting methods but this is what came to me today which seemed to have the lighest footprint.

Updated attached file using GetSummary() instead ...

SchoolScores.zip

Edited December 14, 2009 by Guest

[comment]

The problem with using a self-join is that ignores the found set - which might not include some of the related records.

I believe it would be better to use GetSummary() to count the records in the current group.

[LaRetta]

Of course! Thank you for bringing that to my attention, Michael.

I have corrected the file using GetSummary() and I've removed the beginning Show All Records and this also eliminates the self-join.

[jill]

That looks like pretty close to what I need.

Would there be anyway to exclude the schools that don't have at least 4 scores?

[LaRetta]

Hi Jill,

I still don't know your setup nor how many records etc. But I really dislike searching on aggregate calculations so I decided to still use this process only eliminate the school's group if less than 4 as I looped through them. And keep in mind this is still based only upon your found set so you can restrict to a date range, year or show all records before you begin.

The only changes to the script are what to do if less than 4 records for the school (it now just omits the entire group) and I've changed the test for exiting the loop. See how you go with that. Others may have ideas as well.

UPDATE: File replaced with REV3 which protects from all schools having less than 4 scores (thanks to Comment's input in posts below).

SchoolScoresREV3.zip

Edited December 15, 2009 by Guest
Replaced file with REV3

[comment]

This may be a bit more complex than it seems:

First, there are 3 possibilities:

Group count > 4

Jump to record #5 in the group and omit the excess records;

Group count < 4

Omit the entire group;

Group count = 4

Jump to the first record of the next group (which may not exist).

The hard part here is when to exit, since the last group's count could be 1 (and thus needs omitting) - so just being on the last record of the found set is not enough.

[comment]

Upon further thought:

I think you may exit if you are on the last record of the found set AND the current group's count is 4.

[LaRetta]

I see that I didn't account if exactly 4. I've modified the file; I appreciate the feedback. One thing I couldn't duplicate is:

The hard part here is when to exit, since the last group's count could be 1 (and thus needs omitting) - so just being on the last record of the found set is not enough.

UPDATE: Yes, I need to make further adjustments.

Edited December 15, 2009 by Guest
Added update

[LaRetta]

Okay, I LISTENED to what you said and yes, adding the test for 4 allows the loop to exit properly. So I changed the file to exit loop on:

Get ( RecordNumber ) = Get ( FoundCount )

and

GetSummary ( SchoolScores::s_countSchools ; SchoolScores::School ) = 4

In this way, if the final school group is one, it will continue the loop again and omit it before finishing. Great fun!!

[jill]

This is perfect -- thanks so much. It's been awhile since I've done some complex scripting and my brain is a little rusty. Thanks for all the help!

[comment]

And now I know what bugged me before:

What if none of the groups has 4 or more records? You will get an infinite loop. So it really should be:

Get ( RecordNumber ) = Get ( FoundCount )

and

GetSummary ( SchoolScores::s_countSchools ; SchoolScores::School ) = 4

or

not Get ( FoundCount )

[LaRetta]

WOW!! I didn't consider that at all!! I'll change the file again and how thankful I am that you were around to catch it; I'll try to consider ALL possibilities in the future. Infinite loops are evil things.

[jill]

Ok, my little find feature here on this database got a wrench thrown in it. Now I need to find all the schools with 2 or more scores, and then find up to the top 4 scores of each of those schools.

So I need to search through all the records, omit schools with only 1 entry, and then omit all but the top 2-4 scores for the remaining schools.

Initially I thought I could just change the 4's in the script, but realize that's not going to work.

Any quick suggestion?

[comment]

The logic is still the same, only the numbers change:

Group count > 4

Jump to record #5 in the group and omit the excess records;

Group count < 2

Omit the entire group;

Group count 2...4

Go to the first record of the next group

[jill]

Got it. I was having problems on the exit step, but figured out I had to put in tests for the last group having 2, 3 and 4 records, then it worked.