Importing Data into SubTracker

The easiest way to get your data into SubTracker, if you don’t have a ton of submissions yet, is to use the forms to do it manually.

Start with the Titles form and enter information for each of your titles. Note that most of the drop-down lists are required fields. (Collections can remain empty.) Select the Mode as Active unless the title is already Published or Retired (you’re not sending it out anymore). Then choose a Kind (individual poem, story, essay, etc. OR book, chapbook, group, etc.), and a Genre (poetry, fiction, CNF, etc.). If you don’t see a Kind or Genre that you use, you can add or change this list on the xlistMaintainLists form. Other fields are optional. If you want to leave them blank or use them for other information, you can do that. As long as you enter the Title, Mode, Kind, Genre, and Author initially, you could always go back and fill in more information later.

Once you have your Titles entered, go to Places and enter information about the places you’ve sent them. You only need the Place name and the Type (magazine, publisher, contest, grant, etc.). The only other required fields are Submission Method and Active. Submission Method will default to “unknown” if you don’t enter anything right now, and the default for Active is True. The address fields, website, email, contact, deadlines and comments fields can be entered later if you don’t have all that information handy, or you can leave those blank or repurpose the fields.

Once you’ve entered titles and places, then it’s time to enter your submissions. Depending on how many you have to enter, this can be done in the newSubmissions form or in either the Titles or the Places forms. For quick entry, I would suggest going to Titles or Places, depending on how your submission records are organized. For each title or place, you add submissions by adding the place or title, the date sent, the date returned (if you know that — it can be blank if you don’t), the status (Out, Accepted, Returned, Lost, Withdrawn, etc.), and any comment you wish to add for the submission. If you submit more than one title to a place at a time (such as with poems or flash), then I would suggest choosing one title where you enter the comment, unless you want the comment to show on every Title entry.

If you enter your data manually this way, it will all be in the database, and all the ID fields will be referenced correctly. When you enter a Title in a Submission, what actually gets stored in the submission record is the TitleID, not the text of the title. The same is true for the Place, which sends a PlaceID to the Submission table. That takes up less space in the database, and if you change your title or a place changes their name, you still keep the submission record linked to it, unless you decide to make a new Title or Place record. If you change a title, you may want to note those changes in your Comments on the Titles record.

Importing Many Titles and Places with Calc

If you have a lot of titles or subumissions, then importing Title and Place information might be done more efficiently using a Calc spreadsheet. One way to get the proper Calc spreadsheet is to copy the table by right-clicking or control-clicking on the table name under Tables and selecting copy. Open a new Calc spreadsheet, then Paste. Row 1 will have the correct column headings in the correct order.

  • Columns for Places: PlaceID, Place*, TypeID*, Contact, Address1, Address2, Address3, email, web, SubMethodID, Simultaneous, Needs, Deadline1, Deadline2, Amount, Comments, Comments2, Active
  • Columns for Titles: TitleID, Title*, KindID*, GenreID*, AuthorID*, ModeID*, CollectionID, Copyright, Length, Subject, Comments

Notes:

  • Leave the PlaceID or TitleID blank. Base will fill those in, starting with 0.
  • Some fields must be the right format.
    • Any ID field must contain an integer that corresponds to the option you want.
    • Simultaneous and Active (in Places) are boolean fields and should be either TRUE or FALSE. The format of boolean fields should be set to boolean and the field should be right justified.
    • Deadline1 and Deadline2 should be date formats and can be entered as 7/15 or July 15
  • Text fields have a specified maximum number of characters. You will get an error if you exceed these. These are set to 100, 150, 250, or 1000 (for the large Comments and Comments2 boxes).
  • If you leave an optional field blank when you enter, it will be left empty or the default value will be entered. The columns with asterisks are required and do not have a default value. You must put a value in the columns with an asterisk or you will get an error.
  • Avoid using single or double quotation marks in any field, as they will cause errors when you import. You might find and replace any quotation marks with a character like an asterisk before you attempt an import.

To import data into a Calc spreadsheet, you can use a CSV or Character Separated Values text file. If you use commas in your comments or elsewhere, you can use a semicolon as the separator. As long as your file is set up so that each column is separated by the character, it should import correctly. Empty columns have nothing between the separators, not even a space, but do have a separator to mark where the column would be.

If you have a submission system you can export as a CSV file, you could import it into Calc and rearrange the columns to fit the format and order for SubTracker. This should work well for importing Titles and Places. (See below for thoughts on importing Submissions.)

Once your Calc spreadsheet is ready, it can be pasted back into the table by right clicking the table name under Tables and then selecting Paste. You will then have a series of screens that guide you through how to paste the data. Here is a tutorial from The Frugal Computer Guy on how to import tables to Base from a Calc file. I recommend that you have Base create the PlaceID and TitleID when you import, though it is possible to skip that step and create your own PlaceID and TitleID as long as you start with 0 and use integers incrementally, not skipping any and also not repeating any. New entries will start with the next integer.

Importing Submissions

Importing Submissions is a little tricker, but works about the same way it does for importing Places or Titles.

  • Columns for Submissions: SubmissionID, TitleID, PlaceID, StatusID, DateS, DateR, Comment

The tricky part is that you need to match the right TitleID and PlaceID for each submission, so I would recommend starting with a Calc spreadsheet or CSV file and entering your Title, Place, Status, DateS, DateR, and Comment

  1. SubmissionID can be left blank to let Base add this on import.
  2. Use text for the TitleID and PlaceID columns for now
  3. The StatusID column can also be the text of status for now
  4. DateS and DateR should be formatted as Date columns using the format MM/DD/YYYY
  5. Comment can be blank or up to 245 characters

Once you’ve added all of your information with the text names for Title, Place, and Status, you’ll need to change those to the right integer IDs. That’s where it could get complicated and tedious. For each Title, search and replace on the correct TitleID for that title. If you have typos in your titles, you may need to find those and get the correct TitleID. Do the same for each Place. Find and replace the place name with the matching ID. Status is easier because there are fewer options. You can find those (and create your own) in the xlistManageLists form.

Because it might take a long time to find and replace every title and every place with their TitleID and PlaceID, I recommend entering submissions manually, even if you import your Titles and Places. If you have a lot of submissions, though, it might be worth the effort of importing them. That’s what I did, though I also figured out a way to export my submissions with the right TitleID and PlaceID and avoided finding and replacing them after the fact. That got even more complicated and took several tries.

Expect there to be issues when you import. Keep a blank copy of SubTracker and work in a copy until you get it right. Each time you have to fix an error, go back to a copy of the blank SubTracker, so that the ID fields for PlaceID, TitleID, and SubmissionID start from 0 in their respective tables. (If you do mess up your only blank SubTracker copy, you can always extract a new one from the Zip archive.)

Once you have a Calc file that imports the way you want it to, keep that one and move to the next table. When you have imported everything successfully, you can name that database file your SubTracker (mine is named SubTrackerKD) and/or delete the unsuccessful copies.