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

Question

I have 2 tables&nbsp; Sites(parent) and Site Visits(child).Each site visit has an assigned Field Rep.In the parent record,&nbsp; I need fields that count how many of the site visits are assigned to each Field Rep,&nbsp; 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 mostHow might I accomplish this? ​------------------------------Jeff Peterson------------------------------

markshnier__you · 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 &lt; Site Visit &gt; Reps&nbsp;&nbsp;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.&nbsp; 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.&nbsp; It would be all reps or all reps with more than X site Visits.&nbsp; &nbsp;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------------------------------

jeffpeterson1 · Answer

Hi Mark!It might help if I explain what I'm trying to do here.I built an automation that is used to auto-assign work to a "preferred" rep.In the 'Sites' table,&nbsp; I have fields to hold a 'preferred 1' 'preferred 2' and 'preferred 3' rep for a given site.If a new child 'Site Visit' is created AND there is one or more Reps listed in the fields above,&nbsp; then that rep/reps will automatically get sent the job.Now,&nbsp; I'm where I need a way to mass populate these 'preferred 1', 'preferred 2' and 'preferred 3' fields automatically,&nbsp; based on how many completed jobs have been performed at each site.&nbsp; &nbsp;
This will basically allow us to automatically route work to our preferred techs with no other human action.------------------------------Jeff Peterson------------------------------

markshnier__you · Answer

OK,&nbsp; that helps.&nbsp;&nbsp;You can make a table of Site-Reps (representing all the unique combination of Reps who have every done a Site Visit),&nbsp; where the key field is a concatenation of the Site and the Rep.&nbsp; Populated this running a summary report of all the sites visits summarized by Site-Rep and then "More ... copy these to another table""&nbsp; ( you would update this manually from time to time, say monthly).So now we have can have the relationship were One Site has many Site-Reps.&nbsp; You can then have a summary of the Maximum of the visit count and then use that to get at the rep who had the most visits.&nbsp; Then repeat but exclude that winning rep to get the 2nd highest rep.&nbsp; Then repeat to get rep #3.​------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Forum Discussion

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