Since it is a relational database, SubTracker should fairly easy to modify, though it may be best to plan how you want to do this before you enter your data. Look over the way it’s organized to see if you want to make any changes.
Modify Lists
I set up SubTracker to be fairly easy to modify using dropdown lists for many of the options. The lists are stored in tables with names that start with “xlist.” To make those easy to maintain, I set up the form xlistMaintainLists, which has subforms for each of these tables. Here you can see the existing options and the table where the ID is used.
- Submissions: Status
- Places: Type
- Places: Submission Method
- Titles: Mode
- Titles: Kind
- Titles: Genre
- Titles: Author
- Titles: Collection
You can change the name of any ID, so for instance, if you prefer “Declined” to “Returned” (following the nomenclature on Submittable), then you could replace “Returned” with “Declined” in the Status table. All submissions formerly marked as Returned would now show Declined. If, on the other hand, you wanted to delete one of the options, you could, but all the records that had that ID number would then revert to the default value for that table, which is usually 0. It is preferable to rename and keep the ID number, rather than removing them unless you haven’t used that number.
You can also add options at the end of any list, so if you submit in genres or kinds of writing that I haven’t anticipated, you can add those (or replace ones you don’t use). Or if an new submission method appears, as happened recently with Duosuma, then you can add it. Incidently, the submission method “online” is one I use primarily for and online form at the website for the place, rather than email submissions, Submittable, or Duosuma.
Having lists helps keep the database consistent and avoid errors. It also reduces the need to type these common responses, since you can select them from a dropdown list. And it helps you to be able to quickly change all entries for a particular ID if you change what you want to call it, for instance, if you like the term “e-form” better than “online.”
Modify Forms
If your submission process is different than mine, and you don’t use all the information I use or you want to include different information, it’s fairly simple to modify the Titles or Places forms without affecting their database tables. The easiest thing to do is to simply ignore some of the fields. The dropdown lists are required, though most have a default value. The text fields can all be left blank, so if you don’t care about recording the length, for instance, you don’t have to put anything in that field. If you do care about some other information, you could put it in a field that you know you’re not going to use for any Title or Place.
If you want to modify the form to change the labels on those fields, it gets a little more complicated, but can be easily done. Right click (or control-click) on the form name in the list of Forms, and then select Edit. Here you can see the form and click on elements to edit them. The tricky part is that the field and its lable are usually grouped, so if you click on one, they both are selected and you can’t edit the label. Right click the group and select Enter Group, then click the label (or the field) to select it. Right click again, and you can choose “Control Properties” to edit a lable or change the name of a field.
There are shortcuts for selecting part of a group. On a Mac you can Command-click on the label or the field to select it. On Windows or Linux, you can Control-click to select the label or field. Or you can use the Form Navigator to select labels, fields, etc. by name. If you want to change the look of the database, you can also move or resize fields in edit mode. As I said, it gets a little complicated once you start editing, so you may want to refer to LibreOffice’s documentation or search for online tutorials if you’re going to do much editing of forms. But changing the name of a label for a field would be fairly easy to do.
Modify Tables
If you’ve changed the names of your fields on the forms, you may want to change the name of the field in your tables as well. Changing a name won’t affect much, as long as you don’t change the names of the ID fields that are referenced in other tables and queries. But mucking around in the tables could cause unexpected issues, so I would suggest doing it before you add any data or at least making a backup copy of your working database before changing any tables.
If you find that your text is longer than what is allowed in the database, then you could change the setting for that field to allow more characters. This could happen with some of the comment fields, for instance, though I would consider whether it might be better to shorten the comments than to have more than 1000 characters!
Each record in a database reserves the full number of characters for every field whether they are used or not, so keeping this limit small helps keep your database smaller.
If you want to delete a field from a table, that’s possible to do as long as it’s an optional text field and not an ID field. Be sure to also delete the text field and label from the corresponding form, if you do this.
If you want to make more extensive changes to the database, it is certainly possible, but you are on your own. You’ll need to know (or learn) about LibreOffice or OpenOffice Base and create your own database using SubTracker as a starting point. That’s fine with me, though you may want to rename it and remove the SubTracker image from the forms.
Modify or Create Queries
Another way to make SubTracker work better for you is to change or create new queries. Let’s say you want a list of titles that have been Awarded a grant or prize. Right click on the Submissions_Accepted query and select Copy, then right click again anywhere in the Queries window (except on an existing query) and select Paste. Change the name to Submissions_Awarded, and your new query will be listed. Now right click and select Edit. Under the column StatusID, you’ll see that the condition is 2 for “Accepted.” Change this to 3 for “Awarded.” You’ll also see that under the column for Titles, it list sort as ascending. If you wanted titles to be listed in reverse alphabetical order, you could sort descending.
If you want to change the sort order of a query one time, you can also do this in the query itself. Just run the query and then change from sort ascending to sort descending. If you want to sort by date, though, you’ll need to remove or change the sort listing in the main query by editing it. The query sort is used first and then the sort you enter within the query, so once you’ve sorted by title, it can’t sort it another way. If you sorted by date and then applied a sort by title within the query, any submissions on the same date would be sorted alphabetically.
The only query I wouldn’t mess with is the Submissions_In query. That one is a little complicated with a query and a subquery. If you want to try tweaking it, I’d suggest making a copy and working on that, rather than editing the main query. The others are pretty straightforward.
Another way you can modify queries is to apply a filter. If you run a query that lists all titles, for instance, you could filter that by Genre or Kind, for instance, assuming those fields are included in the query. If you want to run that query often, then you might edit a copy of the query and add a condition in one or more of the columns.
If you want to get fancy with your queries, I’d suggest watching some tutorials and learning more about Base. But the queries that are include are a good place to start and will tell you most of what you want to know or are easy enough to modify. It’s best to make a copy and then modify that so you don’t lose the query that I created. Of course, you can always go back to a blank copy of the database, right click on a query, select copy, then switch over to your copy and paste it back into your queries to restore an original query that you’ve changed. You can do that with forms and tables, too.
Add Reports
One area of Base that I haven’t explored much yet are the Reports. These are used for printing information from the database. So far, I have copied information out of a query and then pasted it into a document or spreadsheet. A report could make this easier, though that will take some formatting, which I assume most people want to do for themselves. If I create some reports that I think will be useful, I may add them to SubTracker in the future. You can set up reports to export your data to a Writer file for printing.
Kendall Dunkelberg 