Creating Ultra Simple Tools To Improve Data Entry

[mki]

So, I have to create a ton of product descriptions or get a freelancer to create them.

The problem I was having is that, my admin interface is a rich text editor, so this creates various issues with paragraphs/line breaks and other text formatting issues.

So, the typical work flow was to create the description in Grammarly and then cut/paste the text into the editor, then fix all the formatting problems, which wastes time.

Since the template is basically the same every time, I figured it would make sense to jump into visual studio and create an ultra simple tool to make this process go much faster.

Shopify does let you paste in raw HTML, which is nice.

This project took me about 30 minutes and I'm sure will save more then that by the end of the week, so it was well worth it.

C# is also not really something I actively work with very frequently, I'm sure people who work with C# on a day to day basis could do this in 5 minutes.

I highly recommend trying to come up with something like this if you are doing repetitive data entry stuff. It will also really help out if you get a freelancer, since this makes the process relatively easy.

I wish I had come up with something like this for Wordpress just to fix the linebreaks/paragraphs. (If you've ever cut and paste text from a .txt file into Wordpress you know what I mean.)

I also wrote a .csv file converter today in php, can't even begin to explain how much time that saves me.

Edit: Now that I think about it, I'll probably add the ability to cut&paste the text directly in from grammarly and propagate the text boxes for ever more time savings.

Code for the Button: (Yeah it's awful... It works fine, so whatever...)

string doublebreak = System.Environment.NewLine + System.Environment.NewLine;
string ht = "";
string b1 = "";
string b2 = "";
string b3 = "";


ht = "<h4>" + textBox4.Text + "</h4>" + System.Environment.NewLine;
b1 = textBox1.Text;
b1 = b1.Replace(doublebreak, "</p><p>");
b1 = b1.Replace(System.Environment.NewLine, "<br />" + System.Environment.NewLine);
b1 = b1.Replace("</p><p>", "</p>" + System.Environment.NewLine + "<p>");
b1 = "<p>" + b1 + "</p>" + System.Environment.NewLine;
            
ht += b1;

ht += "<h4>" + textBox5.Text + "</h4>" + System.Environment.NewLine;
b2 = textBox2.Text;
b2 = b2.Replace(doublebreak, "</p><p>");
b2 = b2.Replace(System.Environment.NewLine, "<br />" + System.Environment.NewLine);
b2 = b2.Replace("</p><p>", "</p>" + System.Environment.NewLine + "<p>");
b2 = "<p>" + b2 + "</p>" + System.Environment.NewLine;

ht += b2;

ht += "<h4>" + textBox6.Text + "</h4>" + System.Environment.NewLine;
b3 = textBox3.Text;
b3 = b3.Replace(doublebreak, "</p><p>");
b3 = b3.Replace(System.Environment.NewLine, "<br />" + System.Environment.NewLine);
b3 = b3.Replace("</p><p>", "</p>" + System.Environment.NewLine + "<p>");
b3 = "<p>" + b3 + "</p>" + System.Environment.NewLine;

ht += b3;

Clipboard.SetText(ht, TextDataFormat.Text);

Code to toggle the window being on top of other windows.

if (this.TopMost == true)
{
	this.TopMost = false;
	button1.Text = "normal";
}
else
{
	this.TopMost = true;
	button1.Text = "On Top";
}

 

[yukon]

I have dozens of small apps like this that I've built over the years with VB.net.

I've got a few apps I've built that even upload content to an online host via FTP, login, create a new page, new URL, etc... from my offline desktop.

This is the way to go when you have a lot of repetitive work.

 

[Guest]

Not "Ultra Simple", but also not overly complicated - this took about 18 man-hours to complete. The solution saves reams of paper and about 6 man-hours a day... 

PART I

OK, a little background about the problem - in our EFL school, students take between 24 and 32 "Practice Tests" as part of their preparation for the University of Michigan ECCE and ECPE (Competency and Proficiency) exams. These cover Grammar, Vocabulary, Cloze, Reading and Listening tasks that are identical to the real exams. The exams use "Bubble Test" style answer sheets like this one:

The ORIGINAL Problem

The teachers and secretaries would score the exams by opening the Teacher's Book (with the answers) and go question-by-question to determine the score.

Back in the day, when we only had a few students preparing for exams at any one time, it was no big deal. But as the student population increased, we got to a point where roughly 50 students at a time were in preparation.  

Additionally, students who scored lower than 80 percent had to correct their mistakes on the exam, meaning it had to be scored a second time.

This, in turn, meant that we were manually correcting around 2,300 (not every exam needed to be scored twice) or so exams during three months in the fall and then doing it all over again during three months in the spring.

It's a very time-consuming task (about 7 to 10 minutes per exam) and from my perspective, unproductive, as mistakes were frequently made. It not only tied up two secretaries, but other teachers and ultimately, my wife and I taking the leftovers home and scoring them.

The FIRST Solution

I had the secretaries create answer sheets by coloring in the correct answers on the bubble sheets and then we laminated them in plastic. We punched holes in the correct answer bubble for each question, allowing us to place an answer sheet over the student's test and then daubing it with a red marker. Then the secretary just needed to count up the correct answers to get the score.

Now it takes them less than one minute to correct a test

PART 2

There is a time limit on these exams (30 minutes for Listening and 75 or 90 minutes for everything else depending on the level). Although we've tried a number of different ways to control this, none of them have worked satisfactorily.

PART 3

We have a third issue however, because the scores must be plugged into a spreadsheet on Google Drive. This includes the original score and the corrected score for each test.

In the spreadsheet, each student has a worksheet with his or her scores and there is an aggregate sheet that gives us an "At a Glance" view of how they're doing overall. Here's an example:

In a nutshell, the problem is that with different people doing this, information gets entered incorrectly or not entered at all.

To make matters worse, I'd developed a "Heat Map" spreadsheet just for Grammar (that's always the biggest concern). The secretaries must enter the incorrect answers by manually incrementing a number in the row for the test they took.

For example, Book 1, Exam 1, Question 1 starts at 0. If the first student got the wrong answer, she would change the zero to a one. If the next student on that test got the wrong answer for question 1, she'd increment it to 2 and so on. This is done for each grammar question on every exam (35 for the ECCE and 40 for the ECPE).

As you can see from the Heat Map above, the darker red or "Hotter" the color, tells us where students are struggling the most. My wife and I then go through the hottest questions on the exam and hold classes to revisit the grammar they're having trouble with.

This, by the way, is a mission critical tool for us, but, you guessed it, the data is not always entered correctly or not at all. We require this data to be more or less "Real Time", i.e.; they must have it all entered by the end of their shift and that doesn't always happen either.

The FINAL Solution (Might be a Freudian slip in that one, LOL)

Getting tired of listening to everyone bitch about the process, I decided to fix it - it took me three long days, but I wrote a script to automate the entire process

Even though the laminated answer keys worked well, that was only half the process. So, in thinking about how to resolve this, I realized I just automate it from cradle to grave

Firstly of course, the student logs in with his or her own password. Only those students who are in preparation can login to this system.

By the way, we have six study carrels with computers the students use to take their exams at.

Once logged in, the system tells them which Book/Exam they're working in and which part (Listening or GVR/GCVR).

When they click Begin Exam, a countdown timer starts and they simply work through each question, ticking their answers.

This of course solves the time limit problem as when the time expires, all the input fields are disabled and they can't change any more answers.

The time limit adjusts automatically for the level and duration of the exam.

That's not even the coolest part though - each time the student ticks an answer, all that data is logged to a database.

But wait, there's more

I've connected the system to Google Drive to update our spreadsheets automatically through Google's Drive API.

Eventually, I'll build in a little admin system for it and a reporting mechanism, but for now, the spreadsheets meet our needs.

Now that it's live and working, we benefit in a number of ways, starting with the saved time so our secretaries can focus on other tasks.

It's also a "Green" solution, in that we're no longer printing out answer sheets and wasting reams of paper.

I just thought of another benefit - we go through about four boxes of pencils a week with these exams. Not a big deal, but a pain in the ass because the students rarely give them back and we don't have time to chase them down.

I forgot to mention that when the student is in the correction stage, it looks like this:

For any answers the student got correct, they cannot change them. For those that are incorrect, indicated by the red X, they can correct.

You can't see it, but for corrections, they do not need to sit through the time limit - there is a button at the bottom they can click to indicate they're finished. We do still track how much time they spent on the correction