I previously wrote about keeping track of records requests in a spreadsheet, something that I encourage every journalist to do. That post has links to sample spreadsheets and various ways of keeping track of requests.

Public records are not the only thing that you can easily keep track of with spreadsheets.

Really, the options are limitless.

For personal use, you can keep track of accounts and user names. You can record all your serial numbers and item descriptions in case your house if burglarized or your stuff is otherwise stolen.

Having serial numbers at the ready makes it much easier for police to recover your stolen property because of national databases.

But working as a journalist, and more specifically as a cops/courts reporter, there are a few specific instances where I use spreadsheets to make my life that much easier.

Tracking criminal cases

Rio Arriba County has a high crime rate, as illustrated by the cases I’m following.

When it comes to being a cops/courts reporter, I keep a bunch of spreadsheets to track everything going on in my beat and, often, to keep track of what went on.

I have one spreadsheet I use to track all the criminal cases I’m covering.

As you can see, Rio Arriba County has a lot of ongoing and serious criminal cases, although one or two of those may be northern Santa Fe County.

Although not shown in the picture to the left, this spreadsheet currently has three sheets: “open,” “closed” and “master.”

The cases pictured are only the open cases. Everything goes into the “master” case list, is then copied to open cases and, when a case hits final adjudication (either through sentencing or the appeals process) it gets moved from open to closed.

The spreadsheet currently has the following sorting columns:

  • Type
  • Defendant Name
  • District  case number (higher court)
  • Offense date
  • Status
  • Victim
  • Ancillary case number (such as the pre-trial detention case number, a civil lawsuit or restraining order)
  • Weapon
  • Magistrate case number (lower court)
  • Report (if I have police reports for the case)
  • Appeal case number
  • Date filed (district court)
  • Date filed (magistrate court)

As shown in the screen shot, type refers to what kind of crime was committed.

I include fields for the date of incident as well as the date filed because the date of incident is a field that moves with court filings as they wend their way through the judicial system. That makes it a necessary number when sorting through court records.

While this does take a small, to moderate, amount of time to set up and initially populate, it’s well worth the time savings later on, the brain-wracking when you can’t remember which case is supposed to go to trial in a few months and from potentially missing important hearings.

While I’ve still missed my fair share of court hearings, keeping a spreadsheet makes it easier for me to check on statuses of everything I’m still covering.

Tracking lawsuits

As the cops and courts reporter, I also cover lawsuits against all our public entities, including the various school districts, our college, the city (and its police department/officers), the county (and the Sheriff’s Office) and even State Police.

Keeping track of all those cases requires a little bit of data entry, but it prevents me from forgetting about cases entirely.

Moreover, spreadsheets can have formulas which means you can keep a running tally for how much a specific entity has paid out in settlement agreements and court decisions.

It also means you can make a new “sheet” or tab in your spreadsheet, dump in all the lawsuits that name a specific person (think of individual officers named in civil rights lawsuits, or all of the lawsuits arising under a specific chief or president’s tenure, or all the lawsuits that are whistleblower claims) and create a simple formula to add up all the amounts.

Keeping a spreadsheet of lawsuits (I break mine down into groups, such as “city,” “county,” “state” and “schools”) also serves the same purpose as my spreadsheet keeping track of criminal cases: it helps me not forget about cases.

Tracking death (overdoses, suicides, etc.)

Once a year, I go to the Office of the Medical Investigator to photograph all of the autopsy reports for Rio Arriba County for the previous year.

It usually takes a few hours to take the pictures and then quite a few more to sort everything.

Each year’s overdose spreadsheet has lots and lots of sub-sheets. I didn’t even mention pivot tables, and the massive things you can do with them.

Once all the reports are sorted into their respective folders and sub-folders,  I start adding all the names to a spreadsheet, along with the day of death, the year of birth, age at the time of death, agency that handled the investigation, the place of death and a bunch more fields related to alcohol and prescription drugs.

I also have one column for all the drugs the person overdosed on. I also break these out into another sheet, with one drug per row.

That spreadsheet helps me with a couple of different aspects of reporting the story.

When I’m writing it, it’s pretty easy to crunch the numbers of how many deaths involved what drug or what drug combinations (as seen in my story on the number of deaths in 2014).  In 2016, for 2015’s deaths, I did the same thing:

Heroin continued its trend of being involved in a majority of overdoses. It was involved in 17 of the 24 deaths, or 70 percent. Three deaths were caused by heroin, alone.

Opiates garnered an even larger portion, being involved in 20 of the 23 deaths, or 87 percent. Heroin is an opiate.

The third most-involved drug was alcohol, contributing to 13 deaths, or 54 percent. Alcohol-only overdoses were not counted.

Prescription drugs, including the opiate methadone, were responsible for nine deaths, or 37 percent, while cocaine was involved in six, or 25 percent.

Having those spreadsheets handy also makes it easier for me to figure out what police reports I still need to request for which deaths.

I can also sort the sheet by age, by drug, by date of death or by location.

It makes reporting the story much easier.

Sorting salaries

Every October, we run the salaries for all the entities we cover.

That means going through whatever document has been provided and updating/adding to our salary list document from last year with new salaries, positions and deleting employees no longer there.

Doing this is easy when the salaries are provided as a spreadsheet.

Doing this is hard when the salaries are provided in a PDF. There are a few different solutions, depending on the layout of the document you were provided, but the point is to get them into a spreadsheet for future reference.

Even if your paper doesn’t run salary lists, it’s still a very good idea, once or twice a year, to request them.

There are a lot of things you can do with that data, including looking for pay discrepancies, but they are really important as reference documents.

Not sure how to spell that employee’s name? Salary list.

Someone got put on admin leave with pay? Salary list states how much he makes and you can then calculate how much he is being paid not to work, per week, per month, etc.

Someone got fired? Salary list states how much he made.

Tracking award entries

If you’re anything like me, your boss comes into the newsroom and says that you have a month to get him your contest entries for the state’s annual newspaper contest, and you then scramble to figure out what you’ve written in the last year that warrants entry.

My contest entry spreadsheet is tailored to New Mexico’s better newspaper contest and the way my paper keeps its archives. None of these entries won during the 2016-2017 contest period. I won second place for news writing.

There is a better way!

Instead of scrambling in June or July, and looking through old PDFs, paper copies and your pre-production text documents, you can keep track of everything you want to enter in a simple spreadsheet.

You only need a few columns to keep track of everything important:

  • Headline
  • Date (run)
  • Content (story vs series vs photo)
  • Continuing
  • Other dates
  • Location (in paper)
  • Collaborators
  • Contest category

The most important part of keeping track of contest entries is to put them into the spreadsheet shortly after the story runs so, at the end of the contest period, you can go back and cull what you think are the best entries to submit.

In the case of continuing coverage, it’s very important to update your entry with the new story dates because if you don’t, you’ll be scratching your head and asking your editor who has the better memory for all the stories that ran as a result of whichever broader story.

This post is licensed under a Creative Commons Attribution-Only 4.0 license.

If you’re a journalist like me, you have a lot of records requests pending at any one time.

If you’re not already keeping track of them in a spreadsheet or similar fashion, this article is to show you how it can be easily done, offer up a template, examples, and some organizational tips.

When I first started out, I didn’t know much about record requests.

That all changed when I started working at the Rio Grande SUN. Now, filing record requests is second nature.

A spreadsheet for open records requests makes it so much easier to keep track of everything.

Since I’m in New Mexico, it’s a lot easier than most states. New Mexico has a very requester-friendly open records law that allows for free inspections of records and offers few exemptions for public officials to withhold records.

That does not preclude me from filing FOIA, or Freedom of Information Act, requests with public agencies, although those requests compose just a fraction of my work. (My FOIA spreadsheet is just a copy of my IPRA spreadsheet that I modified.)

Keeping track of all of those requests, their statuses, when they were filed and all of the other important information can be somewhat easy, but it takes a little bit of organization, a spreadsheet and the determination to keep the spreadsheet updated.

As an example, I currently have 148 (as of June 4, 2017) open records requests. Keeping track of all of those demands some sort of organization.

I’ve only come to the helpfulness of spreadsheets in the past two or three years. Before, I always thought spreadsheets were for things that dealt with numbers. For everything else, I would use a normal text document.

For records requests, I used to just not do anything, other than a search through my email. Oh, how wrong I was!

So, let’s dive straight into the spreadsheet.

(Personally, I use Sheets in Google Docs, mostly because it is available wherever I log into my Google account. Attached at the end of this post is also an Excel version of my template as well as the example spreadsheet.)

Rows upon rows

Before I go any further, please remember, spreadsheets are a somewhat personal thing. I suggest you use a spreadsheet to keep track of your requests, but you should format the spreadsheet’s rows or columns to match what information you want to access.

The first row I use to sort requests is agency. This could be replaced with date, depending on your personal preferences.

Next is the date the request was sent. This is very important because it dictates deadlines and how late a request is in being fufilled. Plus, it’s a great row to sort by.

Everyone needs the first four rows (date, agency, documents requested and status) for a records request spreadsheet. Everything after that is what data you need to help keep track of your requests.

As an aside, when I send requests, I put the date I’m sending the request at the end of the email subject line. I use this as a sort of master reference point when trying to find the string of emails attached to the request.

I also make sure to put a very brief summary of the content of the request in my subject line. This, just like a date, adds another reference point.

An example of a subject line, which I condense for the spreadsheet:

IPRA – May 8 incident reports, supplemental reports, investigative reports, audio/video from incident & communications from — 5-28-2015

The next row is the request’s status, along with the last date  of the last status, if it exists. If an agency sent the three-day letter (mandated in New Mexico law) on May 1, 2017, then I write: “3-day letter 5-1-2017.”

(more…)

It’s already 2017 and I’m not much closer to finishing Jake Highton’s five-year reading plan than I was when I started back in the summer of 2013.

(Read the original post here and read the revised, shortened post here.)

I’d just been laid off from the Nevada Appeal, along with a part time person. I had the least seniority in the newsroom, so I was the one to get the ax and go on unemployment.

It was summer, I had a kegerator in the house and I lived next to the river in Reno.  I rode my bicycle up to the university to visit with my former journalism professor, Jake Highton.

Highton gave me two columns he wrote for the Sparks Tribune outlining the list of books, movies, music and plays he thought young journalists should consume, set to a plan of five years.

While I haven’t been doing much to finish off the list, I have made some progress.

At least two years ago now, I read Rachel Carson’s Silent Spring. Considered a masterwork in its time, I found a more critical reading of it took away some from the bluster it is normally buoyed with.

Although it is about environmentalism, chemicals, and the havoc we’ve been wreaking on the planet, I was really worried about some of Carson’s claims because they did not represent the whole truth.

I think the best example is her bemoaning of the havoc certain chemicals had on the lowly earthworm.

We all love earthworms, right? They’re fantastic. They do all sorts of things for the environment.

Except. Well, most of them are not native to North America, the place Carson was writing about. They vastly change the ecology, and according to the Minnesota Department of Natural Resources, they’re really bad, especially in forested areas.

Farad Power Plant in California on the Truckee River

But Carson never acknowledges the lowly earthworm as an invasive species, or that it changes the ecologies of the places it is introduced to, or anything else. She only talks about how bad it is that chemicals are killing them.

While I certainly agree that the widespread use of chemicals is a bad thing, her complete and total lack of either understanding or acknowledgement of their invasive nature casts her entire book into doubt, at least for me.

As a critical reader, I now question every single premise she puts forth. I think to myself, what else is she holding back? What else is she ignorant about?

In short, her credibility is severely damaged for me, and as a result, so too is her book.

There are more examples, but, alas, I have lost or given away the copy I annotated.

Nonetheless, the only answer is to keep on, keepin’ on through Highton’s five-year list.

Since I haven’t updated the blog in quite a while, I thought it would not hurt to cross-post my guide on looking up federal search warrants on PACER, in New Mexico.

This was orginally posted, as part of a work-in-progress cops and courts guide, here: http://nmcourts.wheelerc.org/.

Looking up federal search warrants on PACER

Searching New Mexico federal search warrants requires a PACER account and a willingness to shell out some cash for unneeded documents.

But, maybe we can change that. I’m currently hosting (on google drive) all of the federal search warrants (the dockets, the reports and the affidavits/applications) I download when looking to see if anything has been filed for my jurisdiction. If anyone has a better way to archive and share them, please let me know. I would like to make it as open and easy as possible.

Here’s the link. Want to add more? Please email them to me. As will be explained below, the report should be saved (into a PDF) and the date it is run should be listed on the document name. (More warrants are unsealed as each month progresses.)

(LINK: On Google Drive)

If you’re using pacer, you should have RECAP installed. (It’s PACER backwards). I find it works best on Chrome, although it also works on Firefox. It automatically uploads the document to the Internet Archive so whenever someone else looks at case file/docket you’re looking at, they can bypass PACER and download, for free, from the Archive. This also includes you, if you lose the document or can’t find it on your computer.

There are two ways to search for search warrants. The first is the easiest, but requires running reports, which could (technically) cost well above the normal $3/30 page ceiling for charges on PACER.

The second merely requires the knowledge that search warrants use the two middle letters of “mr”. Example: 15-mr-439

As an example, a civil case is usually numbered by: the last two digits of the year (15) – CV for civils – the number of the case here, such as 125. Example: 15-cv-00125.

Criminal magistrate cases are MJ. Example: 15-mj-00125 or 15-mj-125.

Criminal district court cases are CR. Example: 15-cr-00125 or 15-cr-125.

The first method (for illustrated first method, see below).

1. Log into pacer and go to the NM District Court.

2. Click on “Reports” at the top of the page.

3. Under criminal reports, click on “Criminal Cases.”

4. Under “Office” select “Albuquerque.” Under “Case types” select “Miscellaneous Criminal.”

5. Click on “Run Report.”

6. Choose which warrant to view. The report should only show unsealed search warrants.

The second method

1. Go into PACER, NM District Court, and search for the year of search warrants you desire, using the middle letters of MJ. It should for 2015, start at 15-mj-00001.

The first method, illustrated

1. Log into pacer and go to the NM District Court.

2. Click on “Reports” at the top of the page.

Fed SW Step 1A

 

3. Under criminal reports, click on “Criminal Cases.”

Fed SW Step 2

4. Under “Office” select “Albuquerque.” Under “Case types” select “Miscellaneous Criminal.”

Fed SW Step 3

5. Click on “Run Report.”

6. Choose which warrant to view. The report should only show unsealed search warrants.

Fed SW Step 4

 

This guide, website and all the information contained therein is distributed under a Creative Commons 4.0 Attribution-Only license.
0 comments