Summary count of records where date and person match

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
Tables involved -

Project Site Visits - Each Project Site Visit has only one Install Date and one Technician ( from Technicians table ) assignment among other fields.

Technicians > has many Project Site Visits

Needed field -

I need a field in Project Site Visits that is a count of all Project Site Visits that are for the same Technician as assigned in that Project Site Visit AND on the same date as the Install Date in that Project Site Visit.   Essentially, if a tech has 3 Project Site Visits with Install Dates on 12/27 then this field I need would simply be 3.

Do I need to set up a third table and if so what would that look like?  Or am I missing a reverse lookup solution?  I feel like I am making this harder than it needs to be but have so far been unable to get the result I need using summary fields.

Thank you for any assistance.
Photo of discobeef

discobeef

  • 114 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
So this is non trivial, and that is why you are struggling.

In order to get that summary total, you will need to have a new table with a record for every Combination of Project Sites, and Technicians and Dates.

So the concept would be to create a new table with a Key field in the format of [Related Site] dash [Related Technician] dash [Date]

Then if you did have that populated, then the Project Site visits could be summarized up to that record and then looked up back down to each Project Site Visit record.

But the question is how to populate that automatically.  My suggestion would be to set up a Webhook to create that record when the project site visits records are added or changed.

The Webhook can be created in such a way as to also work if the project site visits are edited in grid edit, although I can see situations where it could fail if there were multiple records created or edited in grid edit for the same technician for the same site for the same date on the same grid edit save.  But then there could also be a button on a project site visit to refresh the Key fields if one was missing.

So it's possible, but requires setup of a Webhook to do this.  Those are a bit tricky to explain how to get working, so you would need to contact me via the information in my profile for one on one assistance on getting this working, if you have not worked with Webhooks before.