Back to Table of Contents * Go to Topic 45

Topic 44 - Importing and Exporting - Merging Data at the Regional Level

If you are creating a merged copy of the database at the regional level, use these instructions as a guide. Feel free to modify these recommendations to meet your needs.

Each quarter (or more often) you will create a new merged database with data from each school or program that provides data to you.

You will have several copies of the MassSTW2008 program on your computer, each in a folder with its own unique folder name. These might include the following. You can modify this list as needed. For example, instead of Q1, Q2, Q3 or Q4 you might use months or dates.

  • A blank database for merging 1st quarter data – name this folder MassSTW2008_Q1
  • A blank database for merging 2nd quarter data – name this folder MassSTW2008_Q2
  • A blank database for merging 3rd quarter data – name this folder MassSTW2008_Q3
  • A blank database for merging 4th quarter data – name this folder MassSTW2008_Q4
  • IF YOU ENTER DATA for some of the schools and programs, you’ll need an ongoing database folder for this. You can keep the regular name MassSTW2008 or give it a new name. For example, if you enter data for schools in "Mill Valley" and "South City" you could name the folder MassSTW2008MVSC.
  • NEW THIS YEAR – I recommend keeping another copy of the database for downloading files that schools/programs email to you – name this folder MassSTW2008_Imports.

How do you create all these folders?

  • You can use the SETUPMassSTW2008.exe setup routine to create additional copies of the program. Each time you run the setup routine, specify an appropriate folder name and name on the START menu, such as MassSTW2008_Q1 instead of the default MassSTW2008.
  • Or, once you have one blank database on your computer you can also copy-and-paste the MassSTW2008 folder to create extra copies. Then rename the folders to create suitable names. These copies won’t be on your START menu, but you can open these by opening the folder and double-clicking on MassSTW.exe or on STWMenu.skl.

How do you merge data? - Example: 1st Quarter

After the first quarter ends, the schools/programs will provide you with data. They might provide the whole folder on a memory stick or CD or they might email you the 4-7 files that you need for reporting. (See previous topic pages for details.)

  • If you haven’t already done so, create a blank copy of the database in a folder named MassSTW2008_Imports (or any other folder name is fine).
  • When people email files to you, download the files into MassSTW2008_Imports. When you download the files, give each file a unique name that specifies what file it is, what school/program provided the file, and what time period it is. For example, an Employer file from a high school called WVHS for 1st quarter could be named Employer_WVHS_Q1.skl. The STW file from WVHS could be named STW_WVHS_Q1.skl. Eventually, the MassSTW2008_Imports folder will have many different files. You can keep adding more files throughout the year.
  • If people give you their whole folders on a memory stick or CD, you can copy the folder to your Desktop or other convenient location, and rename the folder with a suitable name, such as, for a high school called MVHS, MassSTW2008_MVHS_Q1.
  • When you are ready to start importing, first, if you haven’t already done so, set up a new, blank copy of the database. This may be in a folder named MassSTW2008_Q1 (or something similar). OPEN that copy of the database.
  • From the Main Menu, click on the gray button for “Utilities * Backup * Setup Screen * Import Screens” and then click the gray button for "Import Files."
  • Use the buttons on the Import Files screen to import the files you have been sent, one by one. For example – click to import the STW file and click OK on the message box. In the Browse dialog box, browse to find the Employer file sent by WVHS for this quarter.
  • On the Import Field Mapping dialog box, be sure that “Matching Names” is selected in the “Arrange by” box and then click Import. In the “Import Fields” dialog box click “Import” again.

Repeat … continuing to import each of the 4-7 key files from each school/program. If you enter data for a school/program, be sure to import your data as well. The 4-7 files that you generally import include:

  • Activity.skl (required)
  • Employer.skl (required)
  • STW.skl (required)
  • MCASScores.skl (required)
  • AllPlans.skl (optional/recommended)
  • Contacts.skl (optional/recommended)
  • Scontacts.skl (optional/recommended)

Review your data as needed as you go along.

NOTE: In previous years, the import routine used to display the newly imported data each time that you imported a file. But, since you don’t really need to review the new data EVERY TIME you import, instead, you can occasionally go to the Main Menu and open the files to see if everything looks okay.

Once all of the data is imported, you can review for accuracy, make any necessary corrections and then run quarterly reports, lists or other reports as desired.

You should work out a routine with your schools/programs to let them know of corrections needed, so that they can clean their data before the next time they send it to you. There is no formal process for this, but generally the person merging data at the WIB provides a list of the general types of errors/omissions that happened (such as “please check all the start dates to make sure they are this year.”) and the person at the school/program makes sure to fix the data before the next time it is shared.

Occasionally, two schools/programs will have placements at the same employer site. We realize that when you merge data, you may end up with a few employer duplicates. This is okay -- you don't have to do anything special to remedy a few duplicates.

TROUBLESHOOTING: There are two messages have been popping up since we converted to the new version of FileMaker Pro.

(1) If you are ever importing a file and get prompted for some files that FileMaker Pro says it can't find, it's okay to bypass those messages by clicking "OK" and "Cancel" until you get to the "Import Field Mapping" screen. (Why? The new version treats related files differently and therefore we’ve started seeing these messages. The recommendation above – about creating a folder to keep all of the files that people send you – is a way around this issue.)

(2) If you are ever working with files and get a message saying that the file is in the wrong version of FileMaker Pro, it might be helpful to close down the database and start fresh. (But if you do have old files that you need to import, you can email them to me for assistance in converting.)