Search Involving Summary Field

[McG]

Results of a sort in a layout with 14 records displays the following:

Team, Division, Min. Roster, Team Members

Bulls, A, 3, 5

Eels, B, 2, 4

Sharks, A, 3, 2

Hornets, A, 3, 3

 

Team members is a summary field (sorted on team field) which counts the items in the sub-summary.

I now want to display only the records where the team members is < min. roster (in this case the Sharks information woth 2 being less that the required 3).

I've used a calculation involving GetSummary function to display "Yes' or "No' to show who has a full roster.

Of course, then doing a search for "No" produces no results because the summary field value is not retained once the search is invoked.

 

So.  How do I search to find the appropriate information here?

 

All help much appreciated.

 

 

 

 

[comment]

As you have noticed, a summary field summarizes the found set - so searching a summary field is meaningless.

 

Is there a reason why you cannot produce this report from the Teams table?

 

Also, what (and where) is Min. Roster?

[eos]

Do you have a Teams table? That would the place to search for sub-par teams.

[McG]

Min. roster is the numeric field that shows the minimum amount of players on a team.  It's the value that we are comparing the number of players against.

I haven't done the search in the teams table (where I could put a simple count function of all the related players in the teams table) because I do not want to count all the players.  There is an additional criteria (let's say that players have to be either a Guard or a Small Forward).

If I can do it in the teams table I would, but isolating the players who are part of a specific team and are also are either a Guard or a Small Forward is my area of difficulty.

[eos]

isolating the players who are part of a specific team and are also are either a Guard or a Small Forward is my area of difficulty.

 

You can use

ValueCount ( FilterValues ( List ( TeamMembers::position ) ; "Guard¶Small Forward" ) )

to count the players in these positions and compare the result against your nominal value.  

 

That being said, you could also keep a counter field and update its value whenever you add or remove a player in a pertinent position; since that value (or the sub-par flag based on it) can be stored, a search would be faster.

[comment]

If you want to produce the report from the Players table, you will have to use something similar to the FastSummaries method to omit the non-qualifying teams:

Go to Layout [ “Report” (Players) ]
#
# FIND THE ELIGIBLE PLAYERS
Perform Find [ Restore ]
#
# SORT BY TeamID
Sort Records [ No dialog; Restore ]
#
Go to Record [ First ]
Loop
# IF GROUP DOESN'T MEET THE CRITERIA...
If [ GetSummary ( Players::sCount ; Players::TeamID ) < Teams::Min.Roster ]
# ... OMIT THIS GROUP
Omit Multiple Records [ GetSummary ( Players::sCount ; Players::TeamID ) ]
Else
# JUMP TO NEXT GROUP
Go to Record [ Get ( RecordNumber ) + GetSummary ( Players::sCount ; Players::TeamID ) ]
End If
#
Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) and GetSummary ( Players::sCount ; Players::TeamID ) ≥
Teams::Min.Roster
or
not Get ( FoundCount ) ]
#
End Loop

Note: Players::sCount is a summary field counting the players, i.e. what you named Team Members.

[McG]

Gents 

Thanks for your help on this matter.  The task is part of a high school assignment which is unlike any students have been presented with before.  I'll look at seeing how your solutions can be utilised to make things easier for the the students.