Formula between two child objects?

Sorry if this is more of a Salesforce question. But I’ve been stuck on this for a while. Maybe I am missing something.

I have a parent object (Location) and a child object (Exposure). This means people can add payroll, sales, man hours, etc. to the location on a monthly or quarterly basis.

There is also a child object of location called “Incidents.” To put it simply what I want to do is be able to show the number of incidents that occurred at this location in the exposure period created. Ideally this would be a formula but I don’t think that’s possible.

The other two options I am thinking of are UI only fields, or building some sort of a process/flow that updates a field on the exposure object every time an incident is created/edited on this location.

Anyone have any better ideas?

It almost seems like you have to run an aggregate query on the incidents object for each row of your exposures model, where the dates are the conditions, and then return the results to your exposures model.

You could create a ui-only field on your exposures model, and build a custom renderer on it in javascript to do that… but it will be pretty slow, i’m thinking.

It looks like there could be a possibility of overlap between your exposure dates? If not, things get a little easier.

There’s a chance you could use a ui-only formula field with a lot of conditionals and MODEL_LOOKUP() functions… but I think you’re going to need javascript.

I honestly don’t think your data model supports what you are trying to do.  With Location being parent of both incident and exposure - there is no way a single incident knows its exposure date.  There is NO relation. 

Incident needs to have exposure as a direct relation and then your request will be trivial.