Forum Discussion

JeffPeterson1's avatar
JeffPeterson1
Qrew Captain
4 years ago

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

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...
reports and charts
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
4 years ago
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 this

Site < 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
------------------------------
  • JeffPeterson1's avatar
    JeffPeterson1
    Qrew Captain
    4 years ago

    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,  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,  then that rep/reps will automatically get sent the job.

    Now,  I'm where I need a way to mass populate these 'preferred 1', 'preferred 2' and 'preferred 3' fields automatically,  based on how many completed jobs have been performed at each site.   

    This will basically allow us to automatically route work to our preferred techs with no other human action.





    ------------------------------
    Jeff Peterson
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      4 years ago
      OK,  that helps.  

      You can make a table of Site-Reps (representing all the unique combination of Reps who have every done a Site Visit),  where the key field is a concatenation of the Site and the Rep.  Populated this running a summary report of all the sites visits summarized by Site-Rep and then "More ... copy these to another table""  ( 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.  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.  Then repeat but exclude that winning rep to get the 2nd highest rep.  Then repeat to get rep #3.​

      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------