If you are using MetaLocator’s multi-dimensional features you might know that relational data can be imported in 3 files, a locations file, a related data file and a linking file. This can constitute a lot of spreadsheet management in the long term.
This 3-import file structure roughly mirrors MetaLocator’s internal table structure for related data. However, there is a simplified data file format that allows you to import multiple-table data in a single file. This is commonly done in the context of a physician finder, where doctors might be related to multiple locations and vice-versa. Similarly in a product finder, where products may be associated with multiple locations (and vice-versa).
The following example data file shows locations and related physicians in a single file.
To prepare for the import, your account must be Platinum or higher in order to access our relational features.
To create a related table, click Tools, then Tables as shown:
Then create a Physicians table as shown:
In this example file, we include a column for Physician ID. This column allows us to easily uniquely identify a Physician within MetaLocator to avoid duplicates where physicians might have the same name. This is created as an External Key, a special column type in MetaLocator that helps uniquely identify a record. After creating the table above, create a Physician ID field in the Physicians table as an External Key as shown below:
Notice in the sample data file that Physician Mark Z. with ID 321231 is included in the file twice, associated with Sample Location 1 and 5. Though only 1 Physician record is created and related to both locations. The presence of the External Key is what allows that unique single record to be identified and attached to both locations, as opposed to creating two records for Mark Z.
Note: The special Products table in MetaLocator already includes a default external key called SKU.
When importing this example file, with multiple tables configured in your account, the import column-mapping step will present a Destination Table column. There you can choose which table a particular column should be imported, as shown below:
When MetaLocator imports the data, it will assemble the linking table automatically based on the relationships already present in the imported data file.
Be sure to select “Update Existing and Insert New” when importing using this method as shown below:
During the import the system will add the physicians as linked to the locations indicated in the data file. To view these links in MetaLocator, create a field in the locations table as shown below:
Then, while viewing a location, a new tab in the editor will appear for Physicians at this location.
The above example supports a one-to-many relationship between locations and physicians.