Summary field based upon two relationships
Within our database, Work Orders (WO) could have more than one Site Visit - so if a Work Order requires visits over multiple days or technicians from different Deployment Partners (DP), each day and each DP will get a different site visit. The WO will then potentially have multiple invoices (if different DP were used). We house our WO, site visits, check Ins/Outs, DP Companies and Technician each on their own table with various relationships to share information from one to the other in look up or summary fields.
We have a checkbox field on the Site Visit that is called Cancelled and when checked produces a field that requires you to pick a reason (Tech No Show is one). So we could roll this up to the WO showing how many visits are cancelled because of no show, but we will need it to be specific to each DP, not just a count for all site visits on the WO. Similar to relate to the DP or to the invoice - it will need to show that this is a cancelled site visit for this DP, this invoice, and this WO.
For the late check ins, there is a Duration Formula field called Technician Delay to show the difference between the start time for the visit and the actual check in time within the check ins/outs table. Again, we can roll this up to the WO showing how much total time the technicians were late but it would need to be specific to the DP and this invoice.
It is possible that within one WO, there could be two DP used, but one or both could have penalty causing issues as documented on their check in or site visit. So currently, the PM has to go to the different tables to check this - was the site visit cancelled from a No Show? was the technician late? - and then carry this over to the Invoice and enter this information manually to justify a penalty. We would like this to automatically appear on the invoice for them to review and track in one place.
Is there a way to add to a relationship a summary field that says "Count the number of Cancelled visits when the DP on the visit matches the DP on the invoice and the WO number matches the WO on the invoice". We've considered doing embedded reports that show this information (list all the site visits on the WO with the field to indicate if the visit was cancelled and why and another field to list all the check ins and any check in delay) but again this would require a manual review to see if the DP associated to the visit is the one that cancelled or was late.
We have a checkbox field on the Site Visit that is called Cancelled and when checked produces a field that requires you to pick a reason (Tech No Show is one). So we could roll this up to the WO showing how many visits are cancelled because of no show, but we will need it to be specific to each DP, not just a count for all site visits on the WO. Similar to relate to the DP or to the invoice - it will need to show that this is a cancelled site visit for this DP, this invoice, and this WO.
For the late check ins, there is a Duration Formula field called Technician Delay to show the difference between the start time for the visit and the actual check in time within the check ins/outs table. Again, we can roll this up to the WO showing how much total time the technicians were late but it would need to be specific to the DP and this invoice.
It is possible that within one WO, there could be two DP used, but one or both could have penalty causing issues as documented on their check in or site visit. So currently, the PM has to go to the different tables to check this - was the site visit cancelled from a No Show? was the technician late? - and then carry this over to the Invoice and enter this information manually to justify a penalty. We would like this to automatically appear on the invoice for them to review and track in one place.
Is there a way to add to a relationship a summary field that says "Count the number of Cancelled visits when the DP on the visit matches the DP on the invoice and the WO number matches the WO on the invoice". We've considered doing embedded reports that show this information (list all the site visits on the WO with the field to indicate if the visit was cancelled and why and another field to list all the check ins and any check in delay) but again this would require a manual review to see if the DP associated to the visit is the one that cancelled or was late.
