This is a guest post courtesy of database design expert and author Tim Dietrich, an honorary Gadgetopedia MVP. To check out the second part of this post, click here.
FileMaker users who are exploring Gadgetopedia often ask me whats involved in migrating data from FileMaker to Gadgetopedia. So Ive created this guide to show whats involved.
In Part 1, youll learn how to migrate data from FileMaker to Gadgetopedia. Youll also learn how to configure Gadgetopedia tables with the best possible column types, so that you can get the most out of Gadgetopedia.
When migrating databases that contain multiple related tables, its important that you be able to reconnect related records. Youll learn how to do that in Part 2.
The Sample Database
In this guide, well migrate data from a simple FileMaker database. Its a database of prospects, being used by a team of salespeople. It includes basic information about each prospect, and that data is stored in a Prospects table.
The database also contains information on prospect activity. When a salesperson interacts with a prospect, they add a record to the Activity table via a portal, indicate the type of interaction that they had (email, phone call, meeting), as well as notes or comments.
Below are a few screenshots of the database.
The tables are related using a Prospect_UUID field, which is the primary key of the Prospects table. A field with the same name serves as the foreign key in the Activities table.
While this is a basic FileMaker database, the steps involved in migrating data to Gadgetopedia will apply to any database, regardless of its complexity.
If youd like to follow along, a copy of the FileMaker database is available here for you to download.
The FileMaker database includes sample (fake) data. There are 500 records in the Prospects table, and approximately 200 records in the Activities table.
Export Data From FileMaker
We have two tables to migrate from FileMaker to Gadgetopedia. Lets start with the Prospects table.
First, make sure that you are on a layout that is based on the parent table. You also want to be sure that you have all of the tables records in your found set. The easiest way to do that is to select Records > Show All Records.
To export the records, select File > Export Records. The Export Records to File dialog will appear.
Next, select a location and a file name to export your data to. For the file Type, select Merge.
Note: Gadgetopedia supports importing CSV files. However, youre going to use a Merge file (instead of FileMakers Comma-Separated Text option) because it is very similar to a CSV. The important difference between the two file types is that the Merge file will include a header row with the name of each field.
Click the Save button to continue. The Specify Field Order for Export dialog will appear. Be sure to select the Current Table option. Otherwise, only the fields that are actually on the current layout will be exported and you might end up missing a few fields that are in the table but not actually on the layout.
Select all of the fields that are in the Current Table. You can simply click the Move All button to so.
There is one last important thing that youll need to do before exporting the data, and that is to set the field export order so that the Full_Name field is the first field in the list. Scroll down to find the field, then drag it to the top of the list. (The importance of this step will be explained shortly.)
Finally, click the Export button to export the records to the file.
Import Data Into Gadgetopedia
Now that you have the data exported from FileMaker, youre ready to import it into Gadgetopedia. In this example, Im going to import the data into a brand new Gadgetopedia database. Later, Ill show you how to import a table into an existing Gadgetopedia database.
Before you continue, you need to rename the file that you created earlier. Change the files extension from .mer to .csv. This will make it easier for you to select the file for importing into Gadgetopedia.
One additional change that you need to make and this one is a little tricky is to replace a special character that appears in the merge file. The character that you need to replace is the ??. When creating the merge file, FileMaker substituted this character in place of the return character that you would normally find in a field that has multiple values. Thats a good thing, because had FileMaker used return characters, it would be difficult to know when one record ended and another began.
You need to replace the ?? characters with commas. Its a relatively easy change to make, and you should be able to make it using any text editor. (I use TextWrangler, and you can see how Ive made the change in the screenshot below.)
Next, log into Gadgetopedia. From the Gadgetopedia home screen, click the New Base icon. The New Base window will appear.
Select the Import a spreadsheet option. The Import Spreadsheet dialog will appear.
Click the Choose a .CSV file button. The Choose File dialog will appear.
Next, either drag and drop the file that you created earlier on to the window, or click the Choose File to locate and select the file. Gadgetopedia will immediately create your new database, as well as a table for your data, and load the data into the table. When it is done, a new icon will appear on the Gadgetopedia Home screen.
By default, the new database will be named Imported Application. For this example, Im going to name it Sales Prospects.
You can also select an icon that represents the contents of the database. For this example, Ill select the $ icon.
And finally, you can also change the databases button color. Ive chosen green for the example database.
Exploring the Gadgetopedia Database
Lets take a moment to explore the new Gadgetopedia database. To use the database, click on its icon.
As you can see, Gadgetopedia has imported the data that you had exported from FileMaker. The records are presented in a spreadsheet-like interface, with columns and rows. Take a moment to scroll through the records.
Cleaning up the Gadgetopedia Database
The data has been loaded, but there are a few things you can do to improve the new database and take full advantage of Gadgetopedias features.
Notice that when Gadgetopedia created the new database, it created a table to load the data into. By default, that table is named Imported Table. Lets change the table name so that it is more clear.
To rename the table, click on the down arrow next to the table name, and choose Rename Table. The tab for the table will change to a text field.
Enter the name for the table and click return or enter. For this example, lets change the tables name to Prospects.
Next, take a look at the columns. The column names are okay, and thats because you imported the data from a merge file (where first row included the column names).
However, notice that all of the columns are set to Gadgetopedias Single line text type. (You can tell a columns type by looking at the icon to the left of the column name.) Thats the default column type that Gadgetopedia uses when it imports data. And while the Single line text column type works, its not ideal for several of our columns.
For example, the example database includes a Budget column. It contains numeric values, and youll likely want to refer to those values as numbers instead of text (to get totals, for example). Similarly, the Business_Overview column will likely contain more than a single line of text. There are other columns that you can optimize as well. Lets work our way through them.
The email address column works okay as a Single line text column. However, Gadgetopedia supports an Email column type, and youll get additional functionality if you change the column to that type. To change the columns type, click the down arrow next to the columns name, and select Configure. Next, click on Single line text. A menu will appear, showing all of the column types that Gadgetopedia supports. Select Email from the list, and then click save. Notice that the values in the column are now underlined, indicating that they are links. If you click one, your default mail application should open, with a new message pre-addressed for you.
You can make a similar change to the Phone Number column. Follow the same steps that you used to change the Email Address. However, for this column, select the Phone number column type.
Change this columns type to URL. Like the Email Address column, it will act as a clickable link.
While you dont necessarily need to change the Lead Source column, you could use a Single select type to present a list of options instead of plain text. This is similar to Filemakers value list concept. This will help to streamline the entry of additional records, and help ensure that the values are consistent. To make this change, change the column to a Single select type. Notice that Gadgetopedia will automatically build the list of options (again, the value list in FileMaker terms) based on the values that are already in the records. This is another convenient feature of Gadgetopedia its done all of the work of setting up the value list for you!
Like the Lead Source column, the Industry column would also work best as a Single select column type.
Change the Budget column to a Currency column, and select one of the precision options. Im going to select the precision that does not include a decimal value. Notice that when you change this columns type, Gadgetopedia automatically displays the sum of the columns value in the footer area. You can click on that value to get other summary values as well, such as the columns average, median, min, max, and so on. A very cool feature!
While the content in the Business Overview column is text and the Single line text column type works, its less than ideal. You really want to be able to see and work with values that potentially contain a great deal of text. So lets change this column to a Long text type. Notice that the cells in the column behave a little differently now. If you click on one, an Expand cell icon will appear. Click on it, and the cell expands into its own modal window.
Several of the records contain multiple Interests values. For example, some contain Services and Software, others contain Software and Hardware, and so on. So for this column, instead of changing it to a Single select column, youll change it to a Multiple Options type instead. (This is similar to configuring a field in FileMaker so that it uses a Checkbox set control style.) Once again, Gadgetopedia will do most of the work for you. It will automatically build the list of options to present to the user (based on existing values in the column), and automatically update the column, selecting the correct options for each record.
A prospect will only ever have one Status value (Active or Inactive). Therefore, you can configure the column as Single select type. However, it might be nice to color-code these values to make it a little easier to identify inactive prospects. Notice that when you configure the options for a Single select column, you can indicate the background color to be used for each option. (This feature also applies to the Multiple Options column type.) You can set the color of Active to green, and the color of Inactive to red. This is another convenient and helpful feature of Gadgetopedia.
Finally, the State column should also be configured to be a Single select type.
Gadgetopedias Primary Column
Gadgetopedias primary column is a concept that many FileMaker users struggle with. Theres nothing quite like it in the FileMaker world.
Every Gadgetopedia table includes a single primary column. Its purpose is to give users a way to identify a record in the table. This becomes particularly important when relating records.
To learn more about the primary column, click here.
Earlier, when you exported the data from FileMaker, I stressed the importance of setting the export field order so that the Full_Name column was the first field in the list. The reason that this is so important is that, when importing a file into Gadgetopedia, the first column in the file is used as the primary column. So you want to be particularly careful when you exporting data from FileMaker. You want the first field to be something that will serve well as the primary column.
In this guide, youve learned how to:
Migrate data from FileMaker to Gadgetopedia.
Configure an Gadgetopedia table with the best possible column types.
And you also have a good understanding of the Gadgetopedia primary column.
[Note: In this version of the post, instances of Name column have been replaced with Primary column.]