Question

Excel Data Import - Table Associations

1

I import an Excel file that contains metrics for items into a staging table. I iterate through the staging table data. I check the Items table to see if the item exists. - If it exists, the metrics for that item is created in the metrics table - If it does not exist, a new Items table entry is created, the metrics for that item is created in the metrics table. How can I get the entries in the Metrics table to automatically associate with it's parent entry in the Items table? I want to load metrics on a monthly basis and do not want to manually associate the data each month.

asked 2013-04-29

Paul Roy

2 answers

Mike Kumpf · Answer 1 · 2013-04-29

An alternate approach - ExcelImporter will do this for you in one step. In the import template, you can define which Excel columns can be used to create references between the imported data and other entities. Further, you can indicate handling of these references so that the ExcelImporter will create a record in the referenced table if one is not found. You can find more info in the documentation found in the Appstore.

Erwin 't Hoen · Answer 2 · 2013-04-29

Paul,

Import the data into your staging table. Create a scheduled event that runs over the staging table with a microflow that checks if the data is available by retrieving the data in the items table based on some attribute comparison. Then check if it can be found and handle the true and false exits.