 | |
import data to multiple tables?
comp.databases.filemaker comp.databases.filemaker
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
import data to multiple tables? -
12-10-2005
, 07:44 AM
#2
| |||
| |||
|
|
It just occurred to me today that I'm not sure how one imports data from an Excel spreadsheet if the data to be imported is in more than one table. Filemaker only seems to give you the option of selecting fields from one table when you match the spreadsheet fields to database fields. |
multiple-table file (FM7) into the same (empty) file?
Essentially the same question but the source is different.
#3
| |||
| |||
|
|
It just occurred to me today that I'm not sure how one imports data from an Excel spreadsheet if the data to be imported is in more than one table. Filemaker only seems to give you the option of selecting fields from one table when you match the spreadsheet fields to database fields. |
"If you're importing from a Microsoft Excel file that contains more
than one worksheet or includes a named range, you see the Specify Excel
Data dialog box. Choose the worksheet or named range to import, then
click Continue."
You'll just have to do more than one import if you have more than one
sheet.
Matt
--
#4
| |||
| |||
|
|
May I add to this question: how does one inport data from a multiple-table file (FM7) into the same (empty) file? Essentially the same question but the source is different. |
your DESTINATION.
2. Under the File menu, select Import Records/File
3. Select the file from which you want to import.
In the import mapping dialog, you'll see on the right that the
DESTINATION is the table whose layout you're in from Step 1.
On the left, choose the table in the SOURCE file from which you want to
import.
4. Map the fields ("Arrange By"). In your case, selecting "Matching
Field Names" should be sufficient.
5. Import.
Matt
--
#5
| |||
| |||
|
#6
| |||
| |||
|
|
Apologies if I'm misunderstanding, but I think my question may have been a bit different. My problem is not doing an import from an Excel file that has multiple worksheets, but rather importing data from an Excel file where the data from one sheet belongs in two separate FM tables. When doing the import, FM only allows you to import to the table used by the current layout and in any case doesn't seem to allow you to tell it that some fields are in a different table. Thanks in advance, as always! |
each, import only the data fields you need from Excel for that FMP table.
If you want to end up with two related tables in FMP, you will need to
import into both whatever field you need to establish the relationship.
Bill Collins
--
For email, remove invalid.
#7
| |||
| |||
|
|
Bill, I think I understand what you mean by "import into both whatever field you need to establish the relationship", but supposing the tables are associated by a unique ID match field. First, I do an import into table 1, which creates the new institutions in table 1 and includes fields from the spreadsheet that belong in table 1. Fine so far. But when I want to import into table 2, the unique ID isn't in the spreadsheet since it's created at the time of import into Table 1. The solution I came up with is this: after doing the import to table 1, create a layout that displays the institution and unique ID from table 1, then add the unique IDs as a new field to the import spreadsheet before doing the import into table 2. But this isn't a great solution, particularly if I'm actually dealing with 2 separate import files that have slightly different lists of institutions. Is there a better way? It just seems clumsy that you can't tell Filemaker to do this in one step somehow, or to do it in a less manual way. Thanks! |
record in Excel, then import that serial number into both Filemaker
tables as part of the import. That way, you will have a common, unique
identifier for data from the same records, imported from Excel.
You should have defined beforehand in each FileMaker table a number
field for that unique identifier. It will be the primary key field in
one and the foreign key field in the other.
For the sake of discussion, call the tables A and B, with A the parent
and B the child. In A, define a number field kpAItemID, defined as a
serial number, next value 1, increment 1. In B, define a number field
kfAItemID, NOT a serial number, just a plain number field.
Define the relationship between the two table such that
A::kpAItemID = B::kfAItemID
In the Excel spreadsheet, call the serial number field ItemID.
When you do the import into Table A, import ItemID from the spreadsheet
into kpAItemID, along with the other fields you want in Table A. In the
setup of the import, leave the checkbox "Perform auto-enter options
while importing" unchecked. That way, the records will be imported
without generating a new serial number for each one, ItemID from the
spreadsheet will simply go into the kpAItemID field. After the import is
finished, you will need to update the Next Value in the field definition
for the kpAItemID field, so that it will be one more than the highest
value imported.
When you import into Table B, import ItemID from the spreadsheet into
kfAItemID, along with other desired fields.
Now the two tables are related by ItemID and nothing further is needed
to establish the relationship.
I am curious why you want to split the data into two tables. You seem to
end up with a one-to-one relationship between Table A and Table B.
Normally, that is best handled by leaving the data all in one table.
Relational arrangements are really best suited for one-to-many or
many-to-many schemes.
Bill Collins
--
For email, remove invalid.
#8
| |||
| |||
|
|
When you do the import into Table A, import ItemID from the spreadsheet into kpAItemID, along with the other fields you want in Table A. In the setup of the import, leave the checkbox "Perform auto-enter options while importing" unchecked. That way, the records will be imported without generating a new serial number for each one, ItemID from the spreadsheet will simply go into the kpAItemID field. |
Quote:
|
I am curious why you want to split the data into two tables. You seem to end up with a one-to-one relationship between Table A and Table B. Normally, that is best handled by leaving the data all in one table. Relational arrangements are really best suited for one-to-many or many-to-many schemes. |
was already split into these two tables. However, it's not necessarily
a bad thing, as table 1 has a gazillion fields in it already, and the
table 2 fields are all "grouped".
Thanks again!
Cheers,
John
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.