New Base for Submissions

One current project has been to finally get the data from my old submission tracking system transferred into LibreOffice‘s Base database program. As I wrote awhile ago, moving over 30 years worth of submissions from one platform to another is a monumental undertaking. Back when I moved from Hypercard to Supercard, there was a conversion program, since Supercard was wooing Hypercard users. Hypercard was Apple’s free software to create programs and databases on stacks of virtual index cards. When it was abandoned, programs like Supercard and Runtime Revolution filled the gap, though they were hardly free. To be fair, both are priced for developers, not really for hobbyists, and Runtime Revolution now has moved to LiveCode. Supercard still isn’t 64-bit and is too expensive for me to pay to upgrade for personal use, so I’m finally making the move over to a relational database for tracking my submissions.

This took writing a fair amount of code to export all the text in my Supercard stacks in a form that I could input into the database. Fortunately, I had done a lot of programming in SuperTalk, the scripting language of Supercard, so I knew how to write to a file and how to read what I wanted to read from each card. There was some trial and error to get everything to work right — I’ll spare you the details, since no one else will probably ever have to do this, and if they did, their Supercard stacks would undoubtedly be very different from mine.

Once I had three text files for Places, Titles, and Submissions, I could import those into LibreOffice’s Calc spreadsheets, and then from there copy them into Base. This is a good time to acknowledge that I’ve sent out 523 titles to about 550 places (there are 564 place records, but I know a few are ones I’ve never actually sent to yet), resulting in 3,413 submissions sent out in 923 groups. Since I’m a poet, I usually sent out 4-5 poems in one group, so I thought it might be helpful to track those groups together. One group, for an issue of a magazine I guest-edited, had 55 translations, but most are 5 or less and a few are 6-10. I created a table that lists the groups of submissions by date, though I’m not sure if I’ll need to use that.

*Geek alert*

If you haven’t zoned out already, here’s where I get even more technical. Copying the data into Base was deceptively easy, but I did learn a few things that might be helpful for anyone doing that. What I realized was that some of the lines from my Calc spreadsheet didn’t copy in, so I was missing some data. It took awhile to figure out where the problems were, so I’ll explain what I found.

First, there was very little help on how to import data into Base, but The Frugal Computer Guy’s tutorials were invaluable. I plan to go back and watch more once all my data is imported and I’m ready to start working with it. He showed me how to copy a Calc spreadsheet into Base, which I probably would never have discovered on my own, at least not without a lot of searching in the online Help, which like for most open source programs is okay, but wonky, hard to navigate, and missing some key info. For instance, no one tells you how to format the spreadsheet for importing boolean data. The documentation calls the data ‘yes’ or ‘no’ (and that there’s an option for how to display an empty field). But it doesn’t say what the Calc file should look like.

It turns out that boolean fields in Calc need to be right justified and say either TRUE or FALSE (or empty). I only learned this by creating my table in Base and then copying it back into Calc after I had changed the info for a few rows to Yes and No (checked and unchecked). I also had to be sure to format the cells of this column to Boolean in the Calc file. Similarly, I had a problem with importing dates until I formatted the Calc column as a date.

Still, I had issues with the import of text. One thing I learned was that I couldn’t have any quotation marks in my text or Calc went haywire. I replaced all the quotes in my comments fields with asterisks, though I could have just left them out, I suppose. Thank you Replace All! I also thought that Base had an issue with the string ” / / ” so I replaced all of those, which were indicating line breaks in my original comments. Now I’m not sure if that was really necessary.

The main issue that caused loss of data was that the length of certain fields (Calc columns) needed to be adjusted. I eventually set the length of my Comments field to 1000 characters after checking the character count of some of my longest comment fields. I’ve added comments to individual submissions now, so I can track what was said better, and the comments from each Place won’t get so long, though I doubt I’ll go back and move the old comments. I might do that for some recent ones, though. I had another field set to be 1 character long (from when it was going to be a boolean value), so I changed that to be 10 so it would accommodate any word I want to use for how Places accept submissions. Testing the lengths and looking at the data to see what was coming in and where it wasn’t working took awhile, but I finally have all my Places in. Now I just have to fix my Titles, and I’ll be okay.

One other trick I learned in Base is that to get a blank copy of a table (which is where you put your data — because Base indexes every new record with a number, you need to start with a blank slate every time you import new data in (unless you’re adding to what you have), so I did this a lot while fixing my import). To get a blank copy, right-click on the table and choose Edit. This will open the interface where you can add fields or change the length or format of fields. Do that if you need to (and save), then click Save As and give the table a new name. That will create an empty copy of your table where you can import data, starting from 0, the first record in any Base table.

End Geek Zone (sort of)

Once my import is complete (and fixed), I will have a database table of Places that lists the name, address, and other information for each publisher, magazine, prize, etc.. I’ll have another table of Titles with their information, and I’ll have a table with an entry for each submission that links a title with a place and has a field for when it was sent, when a response was recieved, and what the response was.

Then I can create a Form for Places, Titles, and Submissions, which I can use to view and enter the data in my tables, and the forms for Places and Titles can have subforms that display the submissions for each Place or Title by reading the Submissions table, so I can see what has been submitted where and what the response was by going to the Place or Title. I may even be able to add the response from this subform without a separate Submissions form. That’s something I’ll be working on — how to enter new data.

I can also create a Query to show me all submissions that are currently Out or all submissions sorted by title, so I can see which titles aren’t out anywhere. I’ll be working on a way to run a Report to show me the titles that are in, but if I can’t do that, I can always get that information from the full report by sorting or filtering it. And I may be able to filter out the titles that I’ve marked as Accepted or Retired (some that I no longer send out anymore).

All in all, Base will make a good home for my submissions, and the bulk of the work in transferring it over is done. Then it will be a question of learning how to work with the data in a database. And the good news is that if I ever have to do this again, I can copy a Base table into a Calc spreadsheet and export it from there without all the programming that I had to do to extract my data from Supercard. Here’s hoping that LibreOffice stays around for awhile, though, and I don’t have to do this again.

Once I have things set up pretty much the way I want them (at least the basics), I may post a blank copy of my database that someone could use to track their own submissions. It’s not quite ready for prime time, but it’s getting close. Check back in the new year!