Prototype #4 ... if it works

Click here to load this Caspio Online Database app.

I just put the HTML into this blog, and I'm not at all impressed. When I switched back to compose view, it just said exactly what it says above. I expected the database to actually be ON the page in some form. I went back and read the HTML, and I was even less impressed. They threw in some javascript to make it look fancy, but other than that, I could have written that code without their help. It's incredibly basic.


I'm going to publish this post and see what happens with these links. I'll come back to report soon.

Oh dang. I take back what I said about that fancy javascript. That's what makes the table actually appear ... In compose view, it just says, "Click here to load this Caspio Online Database app." But once it's published, that javascript kicks in.

I didn't see a place in the HTML to change the width of the table. Obviously that's important. I assume the javascript would need to be manipulated, but since I'm basically illiterate in that, I can't tell. I take that back too ... The table width is fine now. No manipulation needed.

This search form is very similar to the one we created in class today. I changed it so that the drop-down menu would actually read correctly. Other than that, I left it the same. Prototype #4 is done now. Number 5 may be tricky. I'm going to create a Google map of my Caspio data. Thanks to Ben, avid reader of my blog and Caspio expert, I have a couple of hints on how to get started. I have to work on a paper before I can begin the map, but it should be done by the end of the week. 

(I just realized that the search form would work better if it actually gave me results. Any tips?)

Still working on Caspio

I haven't made a lot of progress over the break; granted, I haven't tried a whole lot. Right after our class Tuesday, my philosophy professor assigned a paper and two books for us to read over break, so I focused most of my energy on that and a Turkey coma. (I also succeeded in getting another job offer, so overall I felt successful even though Caspio has been trying to bring me down.)


I just posted to the discussion board about my progress, but I'll quickly talk about it here. (My mom is just finishing dinner, and that means I have minimal time to write.) I have made all of my tables, and I've input all the data into them. I suppose I've stopped there because the next step is more difficult … I don't know exactly what I'm doing.


All of the tables have to be connected into some sort of database … Obviously. I'm assuming one of those eight tutorials can help with that. I watched all of them, but since I wasn't working on a table when I watched them, I don't remember a lot of the information. I'm going to go through and figure out which one can help, but if anyone already has some information on this, I'm totally open to that. (I learn very poorly from video tutorials. Throughout college, they have been my least favorite form of learning.) Once I can figure out how to link these tables, we should have another prototype.


I also have another, slightly unrelated, question. Can a Google map be made from a Caspio database? Because that is the direction I want to go if possible. If no one knows, I'm going to figure it out once I've finished with the database.


I have other information that I want to put in here, but dinner is ready. I'll come back once I'm back in Durham … It will be later tonight. I'm assuming no one will read this between now and then anyway.

UPDATE: I'm back! Here is an e-mail from David Nobles that I wanted to share. This was his response to the phone message I left him. I'm not sure what all of it means, but he answered all of my questions so that's a start.

Chelsea,
The name of our vendor is Southern Software.  They are located in Southern
Pines (800 842 8190).  The product is called Sheriff-Pak. They would have to
be the one to tell you about different formats.  What I have been sending
you is from pre-designed reports built into the software.  Any extracts
other than those would probably have to be built by Southern and would have
to be limited as to the content, i.e., public vs. investigative information.
I don't know if that type of report has been requested from them in the past
or if it would have an associated cost.

The individual reports are e-mailed daily one at the time to Bob right now.
There is a media report that contains all of the reports for a specified
time frame but it is not as detailed.  

I'll see if I can find the UCR crime codes already listed somewhere so we
don't have to recreate the list.

David 

Caspio and failure

I've started using Caspio. I was doing really well for a while. Then I ran into a somewhat serious problem. For offenses, many of the reports list several offenses. As the table I made exists now, there is only room for one offense. I need to figure out whether I should add more fields or if I can put them into one field. Also, there are actually several codes that I need a code key for. Originally I put the addresses (including city, state and zip code) all into one field, but now I'm second-guessing that. And the way Caspio makes you enter the time is really pissing me off. You have to put both the date and the time (including the seconds), which makes the date field basically useless. Basically Caspio has been stopped until I find a couple of solutions for minor problems that are causing a huge headache.

In other news, I've experienced my first real (temporary) failure in this class. I called David Nobles today. He was busy. I asked him to call me back. He never did. I sent him an e-mail. He didn't respond. I'm a little surprised because he was so helpful last time. Tomorrow I had planned to be in Boone all day; I want to visit Samaritan's Purse to keep my connections fresh. Even though I have a job now, I eventually want to move back to SP. Then Thursday is Thanksgiving, meaning it will be Friday before I can try him again. And with Friday being the day after Thanksgiving, I'm doubtful that David will find much time to talk. We'll see, I suppose.

Now that I'm home, blogging isn't really on the top of my list of things to do. Thus I'm ending a little quicker than I normally would. I just want to sit and watch the news with my dad. Then, once he goes to sleep, I have to read two books and write two papers before class begins again Monday. This break, the last one of my college career, is already busier than I want it to be.

A plethora of things

I'm hardly sure where to start. I've been talking to Bob High, the crime reporter, recently. I suppose I should give updates on that first. My concern was that when I started putting information into the spreadsheet (I finished one copy), I noticed that all of the reports from the sheriff's department only included the first page. My reports from the police department had both pages, so I e-mailed Bob about it. This was his response:

"I think the back page (page 2) was withheld by agency -- The front side is public, but the back page isn't."

I'm suspicious about the truth of that statement, so I told him I was going to check into it and see what I found. I realize I may be overstepping my boundaries a little, but it's time to cut the small town reporter silliness and get down to business. I haven't checked into it yet, but that's going to be put on the back burner while I sort through everything else.

Although this order doesn't make much sense, next I want to share the crime map that I made today. I'm just going chronologically here. It's based on the spreadsheet that I compiled, and although it's far from perfect, it's a start. Unfortunately I COULD NOT get the HTML code for embedding ... I even made another map and couldn't figure out how to do it. So you'll have to click on the link. I don't like the way the information appears in the bubbles. It looks disorganized. I'd like to find a way to include all of that information somewhere but just have the basics in the bubble. Is that even possible? Either way, prototype three is finished. Next we'll start on the hard parts.


After class, I called the Columbus County Sheriff's Office. I was transferred to Captain David Nobles, and he was fairly helpful. He sent me the media log details in an Excel Spreadsheet and as a snapshot file. The spreadsheet is a little difficult to read as is, but it's proof that this stuff does exist as a database, and that the people at the sheriff's department know how to export it correctly. I wish I could actually attach a file to a blog ... But I can't. While talking to David, he told me that they store all of their files in the records management system, which is the same thing that Edna at the police department told me.

He also told me that he and Bob High have been talking, and Bob basically only wants three parts of the information for online activity - location, incident and one other thing that he couldn't remember. Obviously I've gone beyond that, and obviously that's not what the newspaper wants. Should I just consult Bob and see what he wants? It's seems useless for me to find ALL of this information when it's just going to go to waste. I think that perhaps our class aspires higher than the newspaper, and that's not surprising. We go to Carolina. We typically try to do things just a little bit better than the guy beside us. In this case, I'm starting to question whether it's worth it.


So all of that was a lot less than I expected. I wish I could embed my map. That would make this post a lot cooler. But I can't, so we'll settle for just plain text ... again. I'm not exactly sure what direction to go in now, but I'm just going to keep plugging away at these prototypes until I hit a wall. If that happens, I'll just write a memo ... which sounds like it would be a hell of a lot easier. But now that I've started on the prototypes, I don't want to give up so easily. Persevere!

Prototyping is serious business.

Not that I would know. I haven't actually started besides that one I drew with a pink highlighter in class. However, I'm pretty sure it's serious business because I had originally intended to start as soon as class was over, and I have yet to begin. These past few weeks have been very busy. Besides the job search (which is going well ... I'm pretty certain I have a job offer), I've also been crazy because of the magazine I edit. I just proofed it today, and 1,200 copies should be printed by Friday. So, now that the job search and the magazine have wound down, it's time to focus on school. And unfortunately the prototypes do not have nearly as tight of a deadline as other things. Now that I've made excuses, here's the plan for the next few days.

First things first, I'm going to put all of the reports I've received into a spreadsheet. All of them. The other prototypes are going to be worthless without that one aspect. I'm hoping that the spreadsheet itself counts as one prototype.

The next prototype will be a crime map. Once I get a chance to really explore the options, I'm going to pick one service and use it to plot all of my points. Prototype three, done. (Well, not done, but you get the point.)

Next, I'm going to create multiple pages on my original spreadsheet. I'm not doing this originally because this is a step that will only happen if I have time. And let's be honest; it's the middle of November. We're pressed for time.

The next step is to create a database out of the spreadsheets. I realize this should ideally be done before making a map. However, I want to get the map done. That's going to be the most impressive thing that comes from this semester probably.

After the database is done, I'll probably create another map with another mapping service. Prototype number whatever, done.

And finally, I still want to draw out what I want the final product of the website to look like. I just need another big sticky note, and I'm having trouble getting one of those right now. If worse comes to worse, I'll draw it on a sheet of notebook paper with Sharpie pens. Yes, I said Sharpie pens. I bought some for my birthday, and they're amazing. But I rarely get to use them. And, I hope, that will be prototype number seven. Unfortunately I feel like I could have an entire semester and not get all of that done. Unless it were my job ... I could so do it then. It's just that with everything else going on, I feel like I'm pushing these prototypes to the back burner. Perhaps because I am ...

There's not much to say

I don't have much to say. Nothing has changed since my last blog post. However, there's something interesting about the digital formats of the files I received. They're viewable in Microsoft Snapshot Viewer, and here's what Wikipedia has to say about it. (Of course Wikipedia is the most reliable source I can think of.):

"The Microsoft Snapshot file format provides a way of storing a static snapshot of reports from Microsoft Access versions 97 through 2007. It enabled Windows users without Microsoft Access to view an electronic version of an Access report. The viewer, which is free but proprietary, is available from Microsoft."

Assuming Wikipedia is not lying (and I feel confident in making this assumption because when I downloaded Snapshot Viewer it said it was part of Access), there is a database somewhere that houses all of the crime reports. So, if that's the case, our work is basically done. We don't have to worry about transcribing and all of that fun stuff. We just have to take this database and put it online. So, the question now is ... WHERE IS THIS DATABASE? No one seems to be able to tell me. Maybe I'm not asking the right questions. Here's what Bob High, The News Reporter crime reporter told me when he sent the files.

 They came from the sheriff's office, not the police department. I guess I just assumed those were the same things because I've only talked to Edna at the police department. Is the sheriff's office something different? The files were forwarded from a lady named Barbara Milligan. I suppose I should try to figure out who she is too.


If we can somehow get access to this database, all we will have to do is create a crime map straight from the database. We'll also have to figure out some way to create a search engine from the file, but I assume that won't be too hard. Then, the ultimate goal, will be to figure out a way to automatically update the website when the database is updated. And that's another question ... How often is the database updated? I have reports from as recent as the day before I received them, so I have to assume it's updated daily.


That's all I have for crime reports for today. My other, more pressing, concern is about my grade in this class. We have one month left, and I have an F. I'm trying to graduate in a month, and without this class, I can't. I know I have to make more prototypes. But as for my other grades, I really don't know what to do. I'm concerned.

I have a vision

I finally know what I want the crime part of the website to look like. I need one of those big sticky notes right now. It actually looks similar to the one I already drew, but it's been refined. The map is an integral part. In fact, besides a small search bar on the side, it's the ONLY part. Simple. Clean. And yet behind the scenes, incredibly advanced.

This map has pinpoints, just like we created in class. Those pinpoints give simple information.  Type of crime. Address. Value of property. But CLICK on a pinpoint. Then you get the full description. Everything on the crime report that one might want to know is on the page that you just clicked to. All the details. In one place. And wait. It gets better.

The OCA is linked to all of the other OCAs that are the same. So all related reports are linked instantly. Similarly, you can view all reports of "larceny" or whatever else.

The best way to do the above, although it can certainly be done through linking, may be to search. The search bar is key. What if you don't know where the crime you're looking for happened? For browsing, the map is great, but for something specific, you want to search.

Don't worry. You can. Type whatever you want into the search bar. It will look through all the records to pull out anything that's related. Do you want to be even more specific? You can search by category. Yes, we have that option. From a drop-down menu, you can choose whether you want to search by date, incident, victim, property, offender, suspect or perhaps even something else.

I have no idea how to get from the point I'm at to this point. But I think it's a cool idea. I know there are a thousand stops along the way, and I should probably be focusing more on how to get this data transcribed than how to make the website look. I just wanted to throw it all out there before I forgot. It's been a long, tiring day. I don't want to blog anymore. But, I do have a Facebook addiction. So, you will be able to find me there if you want to throw around some thoughts.

The next steps

I keep starting to write this blog, and then I get distracted. So, this is getting done now. I don't have much to report. Bob High sent me a lot of reports ... like he totally blew up my inbox. I have arrest reports and incident reports, and they're in digital format. Things are looking good.

Now I have to figure out what to do next. First things first, I need to figure out how to combine arrest and incident reports into one spreadsheet. They have a lot of different categories; they aren't really similar at all. I know we basically did the same thing in class, but I struggled then too. I don't want 5,000 categories of information when most of it isn't even necessary. I just need to sit down with both reports and decide what can be combined and what's most important.

The next thing is that I need to figure out how to transcribe this data. The fact that it's in digital format is beautiful. I had to download Snapshot Viewer to be able to see the reports (I hate not having a Mac), but after that, I was able to see everything and it was typed. I assume that will make transcribing easier. I'm not sure exactly how much we're supposed to do. I guess that after I get the spreadsheet made, I will try transcribing a couple of them myself and see how long it takes. After that, I'll need the police blotter to see how many reports a person would have to do each day, and then I can calculate how much time it would take and how expensive it would be to hire a transcriber. Honestly, that's a little more math than I want to do. I'm glad I have calculator or I'd have already called it quits.

Really, that's all I have to report for the weekend. I feel like this blog was really short and uninformative, but I don't have anything else to say right now. I feel like patience is key with everything we're doing.

I hit the jackpot

Even I am surprised at how optimistic I've stayed through this entire process. (And for those of you who think I haven't been optimistic, well, you don't know me. I'm a glass half empty type of girl.) This evening, I finally found what I've been looking for. And most of it came not from Edna, who I am now on a first name basis with, but from Bob High, the crime reporter at The News Reporter.

Bob told me everything I wanted to know. There are, in fact, digital forms of the crime reports. And, as Edna does not seem to entirely understand what I want, Bob offered to e-mail them right to me. He is going to send an incident report and an arrest report in an electronic format. How exciting! First, however, he wants to get permission from the sheriff, and for whatever reason, that cannot be done until Thursday or Friday. So, he is confident that I will have these records, as am I, but I will not be obtaining them immediately. Patience is key.

I then made a quick call to Edna about the police blotter. She said the only way I could get that would be to come in and ask for it, and then she would need to know specifically what I want. Sometimes she's unclear, but I think I may be too. I think we just don't understand each other sometimes; it's hard for me to say whether she's giving me good information or things that really don't pertain to anything. Anyway, she said that they index their files both as paper copies and as digital copies. (And then I wanted to scream, "Well then why can't you send me a digital copy?!?!," but she's been so nice that I couldn't do it.) If I come in, I can look at the blotter and then decide what I want. Apparently I can't just say, "Give me everything."


So, that's all I have to report for now. This week is from hell, but it's been a day of success.