can't get this summary data the way i want it

[Jrav]

I have tried at least 3 different ways and it keeps failing. I'm giving up and asking for fmforum brains. Thanks in advance for helping to solve this pickle.

3 relevant tables:   Visit, PI_Test, AllSpecies

Table: Visit
pk_visitID

Child table: PI_Test
fk_visitID
fk_SpeciesID

1:1 table: AllSpecies
pk_SpeciesID
SpeciesName

I need a summary count of all Species in the PI_Test for a single visit.
Species Name, Species Count per this visit

I need to be able to write the species name in a field, then the count for that visit in a field, ranked from most to least, excluding zeroes.

This can be done in scripting only, it does not need to be a live calc.

OK masters of quasi-relational databases, have at it!

 

as soon as I posted I realized I have this count as a summary field in PI_Test, and it works perfectly when I'm on that context.

So I'm going to script to that context then loop through the records and build the ranked summary block in var space, then parse that back in the context I need it in. 

Kinda clumsy but I think it will work.