how to figure out # of child records by a value and create a ranked list

Question

I have 2 tables Sites(parent) and Site Visits(child).

Each site visit has an assigned Field Rep.

In the parent record, I need fields that count how many of the site visits are assigned to each Field Rep, up to 3 and ranked by number of site visits.

So the result needs to be something like:

[rep1]= rep with most site visits
[rep2]= rep with second most
[rep3]= rep with third most

How might I accomplish this?

------------------------------
Jeff Peterson
------------------------------

MarkShnierYou · Answer

Jeff,There is a relatively easy solution if you are willing to be viewing a site and push a button to see the results.Your relationship are probably like thisSite < Site Visit > Reps  You could set up the User Focus Technique to click a button on a site (or on a site visit, which is probably your "Ticket"), and that would set the current User's Focus on that Site.  Then there would be a summary field of the # of Site Visits for each Rep but limited to only the Focus Site.The you can have an embedded summary report on the Site Visit "Ticket" of the ranked list of reps.  It would be all reps or all reps with more than X site Visits.   There would be a form rule to only display the results of that ranked report if the site was in focus.Contact me directly by email if you wanted to work though that User Focus setup.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Sign up

Login to the community