Help

Getting Count when two conditions are met

Topic Labels: Data Formulas Views
306 1
cancel
Showing results for 
Search instead for 
Did you mean: 
QPBoard
4 - Data Explorer
4 - Data Explorer

Hello, I'm creating a fairly simple base to track all of the violations for an HOA board. I'm trying to create a field to calculate the total number of times a house has gotten the same violation in the past year.

The Base has 3 tables, "Homes" with address and owner, "CCRs" using 'CCR ID' as the name of the violation, and "Violations" that collects the date of the violation, the 'Home', and the 'Violation'

So, I basically need to have another field that counts how many times the same 'Home' and 'Violation' are both in the same row over the past 365 days.

I got this to work by using grouping. In the attached picture, the highlighted number is the number I need to get to populate in its own field. Thanks in advance for any help!

QPBoard_0-1725551592849.png

 

1 Reply 1

Try creating a table where each record represents a single Home and Violation?

1. Create a formula field that combines the Home and the Violation, e.g. `{Home} & " - " & {Violation}`
2. Create a linked field to the new table
3. Copy the value from the formula field and paste it into the linked field to the new table
4. In the new table, create a Count field

This'll show you the number of times that home has had that violation