AnsweredAssumed Answered

Reports for criminal justice data (think #metoo)

Question asked by lgw on Feb 11, 2018
Latest reply on Feb 12, 2018 by lgw

I'm trying to figure out how to generate reports--"subsummary reports"?--that would allow me to analyze and report on my criminal justice data. I have about 5,000 records split into 5 different tables, with the same unique ID in each record. I'm a complete rookie; eventually I hope to have academics check our work, but I need to be able to know what we have first.


Here's my situation: I have criminal justice (court) data from State X. I have a decade's worth of information on every single sexual assault case, including the sentence, demographic info on the perpetrators, and victim age and relation to perpetrator (if any). I have it broken into five tables: crime, jurisdiction, sentence, offender, and victim.


I created a simple layout and used the field picker to add fields from each of the tables, and when I spot check it against my original Excel file, it all seems to be matching up. I used the Relationships tab in Manage Database to link the Unique_IDs in each table (I am hopeful that's why they are matching up, so they will remain together when I sort!).


I think a simple report to start with would be comparing "type of sentence" (probation, jail, prison) to County. In other words, I'd like to see how the 87 counties compare in terms of how often they give probation vs. prison for sexual assault. Obviously, there are scores of other other interesting questions to examine, but this should be an easy starting point.


What I (think I) know so far is that I should be able to COUNT each of these three occurrences (probation, jail, prison) by county using a "calculated value" that counts the number of incidences of each. But here is where I am stuck. I'd like to do this without having to script.


Do I go to Manage Database and make these two fields (county, sentence_type) a "calculated value", checking the Specify button and choosing "Count"? (Note: "probation," "jail," and "sentence" are three options found in the field "sentence_type")? Or is that not the right place to tell Filemaker I want these totaled in reports?


Or do I go to Manage Layouts and create a new form or report and flag these as calculated fields there?


Looking at youtube videos and reading posts online, I get the basic concept--that non-numerical fields can be subtotaled by group. But I'm really confused on what to do next.


Will be happy to share some of the results as I go, if anyone is interested.