Importing from Excel into FM9 relational environment

[JNM]

I am new to the design side of FM9. I have previoulsy worked a fair amount in FM5.5, but more on the layout side.

 

I am looking to have the ability to import from a single Excel doc. contact and account information. I would like to have a table for accounts and contacts at a minimum.

 

How does one go about having a single row of info (e.g. account name, account address, contact name, contact email, contact telephone) get divided into the separate tables as well as linked as being a contact within that account?

 

I am still int he design phase so I guess the next question would be what keys are connecting these database? I was planning on using serial (auto enter) for Account ID as well as Contact ID (for future development).

 

Please let me know if this doesn't make sense and I will try my best to clarify.

 

Thank you

[AHunter3]

Two approaches:

 

a) Import the excel data into a "holding table" in FmPro and then loop through the records, creating records in the relevant FileMaker tables for the imported data, and connecting them, usually by creating the parent record first then in a subloop creating the child recs. e.g., —

 

Import [restore order]

Set Variable [$OrigWindowName, Get(WindowName)

New Window ["Cuisines"]

Go to Layout ["Cuisine Data Entry"]

New Window ["Foods"]

Go to Layout ["Entree Data Entry"]

Select Window [$OrigWindowName]

Loop

..Set Variable [$Cuisine, ImportTable::Cuisine]

..Select Window ["Cuisines"]

..New Record/Request

..Set Field [Cuisine::Name, $Cuisine]

..Set Variable [$CuisineSerial, Cuisine::Serial Number]

..Select Window [$OrigWindowName]

..Loop

....Set Variable [$Entree Name, ImportTable::Entree Name]

....Set Variable[$Entree Price, ImportTable::Entree Name]

....Select Window ["Foods"]

....New Record/Request

....Set Field [Foods::Cuisine Serial Number, $CuisineSerial]

....Set Field [Foods::Entree Name, $Entree Name]

....Set Field [Foods::Price, $Entree Price]

....Select Window [$OrigWindowName]

....Go to Record [Next, Exit After Last]

....Exit Loop If [importTable::Cuisine ≠ $Cuisine]

..End Loop

..Go to Record [Next, Exit After Last]

End Loop

Comment ["This is typed freehand and is for illustration-of-concept purposes. May have errors. Unchecked."]

 

 

b) If it's a non-recurring import (just a one-time thing), you could instead either convert your Excel file into a FileMaker db and instead of importing from it, push data out of it with a script similar to the above, using the converted file instead of the ImportTable described; or import all records into the child table (with fields for the parent-table data as well as the child-table data), then sort and loop through the child recs, creating parent records for each unique value that would imply a different parent, snag the parent serial and stamp the live child rec with that value; then when you're done delete both the script and the parent-table fields that are in the child table.

[JNM]

Thank you very much for this. On a conceptual level it makes sense. Would you be able to shed some more light on the loop for a file with an account and names database only? Your example referneces a few different food examples.

 

This would help penetrate into my head a bit deeper.

 

Thank you agin for taking the time to reply.

 

Jeffrey

[AHunter3]

Each account has multiple names but each name one and only one account? Or each name has multiple accounts but each account one and only one name? Which one is the parent table? Or is it a many-to-many relationship where neither is the parent?

[JNM]

I am planning on having the Account be the parent. There could be several contacts within the same account. At some point it may be required to have a parent account (corporate parent), but that is not necessary now (or frankly could be left out). Does that clarify a bit?

[AHunter3]

Import [restore order]

Set Variable [$OrigWindowName, Get(WindowName)

New Window ["Account"]

Go to Layout ["Account Data Entry"]

New Window ["Names"]

Go to Layout ["Name Data Entry"]

Select Window [$OrigWindowName]

Loop

..Set Variable [$Account, ImportTable::Account]

..Select Window ["Account"]

..New Record/Request

..Set Field [Account::Name, $Account]

..Set Variable [$AccountSerial, Account::Serial Number]

..Select Window [$OrigWindowName]

..Loop

....Set Variable [$Full Name, ImportTable::Full Name]

....Set Variable[$Name Tel Number, ImportTable::Full Name]

....Select Window ["Names"]

....New Record/Request

....Set Field [Names::Account Serial Number, $AccountSerial]

....Set Field [Names::Full Name, $Full Name]

....Set Field [Names::Tel Number, $Name Tel Number]

....Select Window [$OrigWindowName]

....Go to Record [Next, Exit After Last]

....Exit Loop If [importTable::Account ≠ $Account]

..End Loop

..Go to Record [Next, Exit After Last]

End Loop

Comment ["This is typed freehand and is for illustration-of-concept purposes. May have errors. Unchecked."]

[JNM]

Regarding this line: Set Variable [$OrigWindowName, Get(WindowName)

 

Does this line of code refer to the "holding table"? If I were to name that table "Raw_import_data", would this read?:

 

Set Variable [$OrigRaw_import_data, Get(Raw_import_data)

 

And I assume after the data has been integrated I would delete the contents of this table? This is a process that would be used quite extensively. Does this loop still allow for Filemaker's built in de-duping capabilities?

[AHunter3]

A $variable is a temporary holding tank for data that you only need to preserve until the end of the script. It isn't in any table at all. You're telling FileMaker, "Hang on to this until I ask you for it, mmkay?"

 

Set Variable [$SomeVariable, Table X::SomeField]

Go to Layout ["Totally Different Place" (Totally Different Base Table)]

Set Field [Totally Different Base Table::FieldName, $SomeVariable]

 

... lets you take data from one spot, go somewhere from which vantage point you can no longer directly reference the original data in its original spot, and ask FileMaker "OK, that data I told you to hang on to for me? Need it now. Here. Thanx"

[JNM]

I have tried to follow your code to the T but I am unable to replicate some of the items you wrote for instance "Set Field [Names::Full Name, $Full Name]" In my version I am unable to enter a comma and another value.

 

Please see the jpeg below. This is the closest I could come and the outcome was not good. It created 33,000 records of nothing.

 

PLEASE HELP!

[AHunter3]

I am unable to replicate some of the items you wrote for instance "Set Field [Names::Full Name, $Full Name]" In my version I am unable to enter a comma and another value.

 

a) Select the "Set Field" script step from the lefthand side of possible script steps and click the ">Move>" button so that it appears in the list of script steps on your right.

 

b) Once you've done that*, two buttons will be available at bottom right, both of them reading "Specify": "Specify Target Field", and "Calculated Value". Click the "Specify Target Field" version of "specify". Find the field "Full Name" in the list of fields from the table "Names". click "OK".

 

c) Now click the "Calculated Value" Specify-button. A box will pop up into which you can freehand type whatever you want. (You can also pick from a list of field values at upper left, or from functions at upper right, but ignore that for now). Type "$Full Name" in that box. (without the quotation marks). Click "OK".

 

Once you have DONE all that, you will notice that the line in your script, when you look at it there in your scriptmaker, looks like this:

 

Set Field [Names::Full Name; $Full Name]

 

 

...which is why we write it out that way. You're new. You'll learn.

 

 

* If you've already created the Set Field script step within your script, you can regain access to the two Specify buttons at any point by clicking directly on that script step within your script.

Edited March 12, 2013 by AHunter3

[JNM]

Thank you that explanation was great and in fact I was able to modify accordingly.

 

My next 2 problems is:

Set Variable [$OrigWindowName, Get(WindowName)] - I found the "get" commmand, but am I suppose to be writinig "origWindowName" or does that refer to a window I should already have? I guess the better question is, what is a window? and why are we getting it?

 

This non-understanding goes the same for the Select Window [$OrigWindowName] - I cannot get this to happen. It forces a [Name: $OrigWindowName; current].

 

Thank you for your patience. I am getting closer, but it still is not functioning, it just creates blank records.

[AHunter3]

Thank you that explanation was great and in fact I was able to modify accordingly.

 

My next 2 problems is:

Set Variable [$OrigWindowName, Get(WindowName)] - I found the "get" commmand, but am I suppose to be writinig "origWindowName" or does that refer to a window I should already have? I guess the better question is, what is a window? and why are we getting it?

 

A window is, umm, a window. The one you're staring at right now is named "Importing from Excel into FM9 relatiional environment - Page 2 - FileMaker Pro Online Community". In FileMaker, it's useful to set a $variable to the name of the current window, so that later you can select that window by name, and thereby ensure that your focus is where you want it to be.

 

We're setting a $variable named $OrigWindowName to the value of the name of the current window. That make sense?

 

 

a) Add the "Set Variable" script step to your script.

 

b) in the "Name" blank, type the literal string $OrigWindowName. That's the name you're giving to your variable.

 

c) In the "Value" blank, type the expression Get(WindowName). That's the value you're setting the variable $OrigWindowName to.

 

 

This non-understanding goes the same for the Select Window [$OrigWindowName] - I cannot get this to happen. It forces a [Name: $OrigWindowName; current].

 

 

a) Add the "Select Window" script step to your script

 

b) click the "Specify" button at the bottom right to specify what window to select.

 

c) click the option "Window Name", not "current window"

 

d) In the blank, manually type out $OrigWindowName

 

e) click "OK"

[JNM]

I have been able to get closer - I have been able to get my code to look more like yours. A problem I faced with now is when I run the script it creates 40,000 blanks records!

 

Your first line states Import(restore order) When I add Import to the script it requires I define a source. I assume this should point to the "holding table"?

 

Thanks for your patience, again!

 

I am hoping to have this completed before I die.

[AHunter3]

Yes, you need to specify the source in order to be able to set up what columns match up and get imported into the FileMaker fields.

[jamesafoster@mac.com]

Two approaches:

 

a) Import the excel data into a "holding table" ... OR ...

 

b) If it's a non-recurring import (just a one-time thing), you could instead either convert your Excel file into a FileMaker db and instead of importing from it, push data out of it with a script similar to the above, using the converted file instead of the ImportTable described; or import all records into the child table (with fields for the parent-table data as well as the child-table data), then sort and loop through the child recs, creating parent records for each unique value that would imply a different parent, snag the parent serial and stamp the live child rec with that value; then when you're done delete both the script and the parent-table fields that are in the child table.

 

Please forgive my rampant newbish-ness, but...the second option above sounds like what I want to do. But I have NO idea what it means. Supposed I have an excel spreadsheet with three columns, A B and C. I want to create a FMP database with two tables, T1 and T2. T1 has fields A and B; T2 has fields B and C.

 

How do I get the data from Excel into T1 and T2?

 

I import the whole excell spreasheet into a big holding table, say T, which has three fields: A, B, and C. Then I create T1 and T2 as above. Now, how do I get the records from T[A,B] into T1[A,B] and from T[b,C] into T2[b,C]

 

Thanks!

[Techphan]

1) Create your new database in FM with tables T1 and T2 or add them to your existing FM application.

 

2) Define fields A and B for T1 and B and C for T2.

 

3) Choose Import Records (File -> Import)

 

You will be asked your source file name, location and type of file (choose Excel).

 

4) Then you will need to map your fields (Old Excel to New FM): Excel A -> FM T1::A, Excel B -> FM T1::B. Then it becomes a matter of completing the import process.

 

5) Repeat steps 1-4 for Table T2. You certainly can import the entire spreadsheet into FM, then from that master table populate tables T1 and T2. You would have to use two separate looping scripts, one to populate T1 and a second for T2. This is a good way to jump into writing scripts.

[jamesafoster@mac.com]

4) Then you will need to map your fields (Old Excel to New FM): Excel A -> FM T1::A, Excel B -> FM T1::B. Then it becomes a matter of completing the import process.

 

5) Repeat steps 1-4 for Table T2. You certainly can import the entire spreadsheet into FM, then from that master table populate tables T1 and T2. You would have to use two separate looping scripts, one to populate T1 and a second for T2. This is a good way to jump into writing scripts.

 

I imported all my data from excell into one large table, then created the smaller tables with the right fields. When I get into the Import screen to populate the smaller tables, every table except the main one is grayed out. I can't select the smaller tables in order to map my fields. Why?

 

Also (again, a very naiive question) I don't see where I would go to enter a script. How do I do that?

 

thanks again for your patience and great clarity!

[jamesafoster@mac.com]

Progress!

 

I went into the Scripts/ScriptMaker tool, added Import for each small table, selected the big table as a "data source", changed the "import order" so that the source fields in the big table matched the target fields in the small tables.

 

Then run the script! This populated my little tables.