Importing to multiple tables?

[RDecker]

Thought I had this solution all wrapped up with all the new features of FMP7--and now I discover that one apparently can't import data into more than one table at a time. (Hope I'm wrong!) I've designed this with a separate table for contact numbers (phone, email, etc.). The data is now in an Excel sheet (flat file). How does one move such data into a multiple-table FM solution?

RDecker

[-Queue-]

Import the same file into multiple tables. Go to a layout associated with a particular TO. An import there will import into that table. Repeat for other tables containing your remaining fields.

[RDecker]

How does the data stay "matched up" in this scenario? How is the relationship established?

[-Queue-]

I'm not sure what you mean by relationship. Importing is unrelated to relationships. Regarding matching the fields, you will have a single Import Records step for each table's associated group of fields, and will specify the import order for each one. It will look like

Go to Layout ["A"]

Import Records [No dialog; "yourfile.xls"; Add]

Go to Layout ["B"]

Import Records [No dialog; "yourfile.xls"; Add]

etc.

[RDecker]

I'm not sure what you mean by relationship. Importing is unrelated to relationships. Regarding matching the fields, you will have a single Import Records step for each table's associated group of fields, and will specify the import order for each one. It will look like

Go to Layout ["A"]

Import Records [No dialog; "yourfile.xls"; Add]

Go to Layout ["B"]

Import Records [No dialog; "yourfile.xls"; Add]

etc.

Hmmm. It seems to me that would generate two groups of records that are not related in any way. But I need to assure that the names in the flat file data (Excel) that end up in the first table are linked to the correct phone number in the 2d table, esp. with the "Add" step (i.e., add new records).

(I have a separate contact # table to enable a "one to many" relationship--each name may have multiple phone numbers (& email addresses) associated with them. These numbers appear in a portal on a layout that shows the basic name/address info. The imported data will have only one phone number per name, but that will change over time with the new system which allows for multiple entries here.)

Thanks for the help,

RDecker

[-Queue-]

Each import step should import the key field for your relationship, so that the tables are automatically related once all the imports have occurred. It would be best not to use names, however, as names can change or have duplicates. A unique id linked to each name is the safest way to go. If you must use names, then each related table should have a field for it so that it can be imported from your flat file.

[RDecker]

That makes sense... and if I were doing all the importing, could be handled OK. (I'd have to add such a number to the Excel file first.) But how does one handle that when distributing a solution to those who have no idea of the concept? Most of the people using this won't know anything about RDBs and almost all the data that they will need to pull into this solution will be flat file of one sort or another (prob. Excel in most cases.)

I've been playing around just now trying an import into a (different) field in the same file that is not used by the import data (notes), and then adding a script step to move it to the correct field in the related table (loop:copy/paste). It's clumsy, but I have the basics working. (My auto phone format calc only works part of the time in this case, even when my dummy data all contains identical 10-digit numbers--but that's prob. another problem.) Is this a feasible solution or will I get into other problems doing it this way?

Any advice is appreciated!

[-Queue-]

This is why I would suggest you script the process. I wouldn't want anyone who doesn't know what they're doing running a manual import into my dbs. Who knows what confusion and trouble that could cause?

You could do as you're doing, though it would be simpler to use Set Field [relationship::field; tempfield] and wouldn't require you to jump through Go to Layout hoops to move the data back and forth.

I would still recommend that users have no manual interaction with the import though. You can leave the data source unspecified so that they can select the file to import. However, even this may be too much accessibility and can lead to major problems if the wrong file is selected. Usually, the best way to do this is to ensure that the file always has the same name and column order and lives in the same place on the network, so that once the file is in the correct folder, they only need press a button to have it imported to the necessary tables, with the correct import orders and import auto-enter options, etc. performed by the system.

[RDecker]

I would still recommend that users have no manual interaction with the import though. You can leave the data source unspecified so that they can select the file to import. However, even this may be too much accessibility and can lead to major problems if the wrong file is selected. Usually, the best way to do this is to ensure that the file always has the same name and column order and lives in the same place on the network, so that once the file is in the correct folder, they only need press a button to have it imported to the necessary tables, with the correct import orders and import auto-enter options, etc. performed by the system.

If life were that simple I'd be very happy! And if there were a regular import and consistent file format, etc., scripting it would be easy. Unfortunately, this will be used to standardize a very wide range of manual systems that have no coordination, consistency, etc. Thankfully, the import only needs to be done once in each situation and then all data will be maintianed in the solution. So I think I have no other choice than to allow manual import--with (hopefully) clear, complete, and accurate instructions which also discuss a number of "what ifs." And probably some advice on cleaning up the data first and trying to standardize it as much as possible. I suspect the data will be very "dirty" in many instance. The first file that I obtained to try importing has such anomalies as mailing titles in the same field as first name, etc. (At least the last name is separate!)

Thanks for the suggestions.

[LaRetta]

I also work with large volumes of inconsistent data being moved into a normalized structure. I allow 'wide-open' import into a pre-established intermediate file with consistent field names, data types, etc which is a duplicate of my 'real' file; except it contains several fields for phone number and all related tables (as fields in this table) as well. The User is then provided with several variations to check for duplicates and clean it up. This intermediate file is related to the main (and other tables) so that similarities can be compared via two side-by-side windows and duplicates can be manually deleted first or field data moved by point-click from one file to the other (if the record is the same but the new has additional info like email address, etc).

Once the data is correct (and yes, one never knows FOR SURE), only then is it moved into the main file (and split into multiple tables) according to the existance of data in the fields. Forcing a User to data-map to a consistent structure (but yet isolated from good data) adds a safety-net. It also allows them to back out (deletes all newly imported from intermediate file) and start again if necessary. It is not perfect. It never will be when working with flat-file messy data; but it protects the main file from garbage-in; and it protects from direct 'open' imports.

LaRetta

[RDecker]

I like this idea of an intermediate file for cleaning up dirty data. The final import could then be done with a script. How do you maintain the link between related data in separate tables when you do this? Assign a serial/key # when you do the first import and then script the copy of it also to both tables?

[LaRetta]

Essentially yes. The intermediate contains a serial (auto-enter) which is then transferred to the main (and related tables) and THAT relationship re-relates them all (and writes their real foreign keys once the Main ContactID is assigned) during the pulling process. Data from the Intermediate file is then deleted and the serial reset to 1.

We pull volumes of trash (company names, addresses, etc.) into our Contacts file. If this process wasn't in place, identifying duplicates and also allowing Users to import from various file formats would be a nightmare. Our data (knock on wood) actually stays pretty clean this way. Since this is a constant, on-going process, I felt having one extra text field (to hold this temp relation serial) a small price to pay.

Oh. One more thing I should mention. If any incoming data is date, I use text fields and convert it myself. It can come in various formats and importing directly into a Date field doesn't always work correctly. Just something to consider.