Import: one fp7 db, 2 tables - relationships set - but can't get to table 2

[Hoib]

OK, I'm back again with another question.

I've been working on a solution that has one .fp7 fileset but multiple tables. I've adapted an FM StartUp solution that comes with FM - Research Notes. My Table1 - has data about social programs, and their descriptions. Table2 (which I had to create from scratch) has contact info, name of persons, addresses, phone numbers, email add. I have a Table1 pk (called id_notes) which in my relationship diagram is fk'd into Table2's id_notes field. Very simple.

Now I have my conversion; an exported spreadsheet (.csv) that has the Table1 and Table2 data in it. I can import my Table1 data using FM's neat little import field mapping gadget. But in that same dialog, in the upper right hand corner, I want to get Table2 brought in. If I drop down the target selector, I see Table2 as a Related Table but it is always greyed out so I can't get to it to match up those contact fields.

I believe I must have something wrong in my Relationships. But I've looked it all over and it seems correct but I know it's obviously wrong somehow.

Question: Is it even possible to import these data this way, into one table; then another? I can't see why not but... If it's not then I'm going to have to move my contact info/data into Table1 which isn't according to Hoyle as far as normalization is concerned. And maybe I'm not finding the right command sequence or set of controls in FM.

What do you good people think?

H

[comment]

If you are importing manually, go to a layout of the target table before trying to import.

[Hoib]

Wonderful! Something else I learned today -- thanks to you for showing it to me...

Hope I can return the favor.

Again, thanks!

H

[Hoib]

Rats! I'm still stuck!!!

No matter what I do, Table2 never appears as an active import option in that target-import box. I never get to choose anything but the table I'm in. I was so hopeful "consultant"s answer would lead me to it. But when I sat down to perform the import, Table2 still can't be reached.

When I open up Table2's layout all the fields are there, labels and so forth. And maybe that's where I'm actually really stuck here; I probably don't quite understand that what I'm looking at really isn't the target layout that consultant was driving at?

Is that because Table1 and Table2 are currently contained in FM's tabbed interface? I can see Table2's relationship keyed/pointing right at Table1 in the relationship diagram within this .fp7. I would think that because those two are connected there now, Table2 would be available to populate the layouts but sadly, it isn't. It's so brutally tantalizing too because I can see Table2 sitting there, but greyed...

What do I need to do to get those two connected?

(Sorry consultant - I still want to thank you anyway)

H

[comment]

Is that because Table1 and Table2 are currently contained in FM's tabbed interface?

I think we need to clarify the term "a layout of a table". When you are in Layout mode, select Layout Setup… from the Layouts menu. You will see that each layout is set to 'Show records from: [ some table]'. It doesn't really matter what's on the layout; you could show a portal to another table there, or nothing at all. Still, this is a layout of [the table selected in the layout setup].

Therefore, you cannot have "Table1 and Table2 contained in FM's tabbed interface". At least one of these tables is not the table of the current layout, and the behavior of fields on a layout is very different for fields from the table of the layout (tagged as "Current Table" when you double-click the field) and fields from other tables.

Each table should have at least one layout associated with it, otherwise you'll be very limited in what you can do with the table.

Note also that you cannot import into two tables at the same time.

[Hoib]

OK Consultant - I thought I had it again. I'm starting to get it... but... I'm trying to put the two tables together on one layout which has tabs.

Table1 and Table2 - again these are related through a primary/foreign key with Table1 being the driver and Table2 containing supplementary data like phone and addresses. I have my tabbed interface Tab1 and Tab2 let's say. I was hoping to have Table1 show up in Tab1 and Table2 show up in Tab2. I'm on a layout entitled "Record Detail" which would be used to input items into Tab1 and Tab2, typing them into Table1 and Table2 as needed. What you showed me was very clear: Layouts > Layout Setup. And I can go to the middle of that box and choose "Show Records from..." <pick a table>. That's another new thing I learned here. But when I go to Import from my .csv into Table2(Tab2), I've set "Show Records from Table2" on that Tab. They go in and I can see them. As soon as I switch back to "Show Records from Table1" (because that's the primary table), Tab2 doesn't show any of my imports. I have to shift back to "Show Records from Table2" in order to see Tab2. I'm trying to put the two together.

I thought I would be able to display Table1 and Table2 simultaneously (as I roll through Table1(Tab1) and have Table2(Tab2) keep pace because they are linked. I really don't understand why this doesn't work. Here's what I was hoping for. Pleae tell me if I'm all wet or not. I know Tab2 is not really a "portal" because in my mind it's a "tab" and isn't viewed until clicked. And that's the problem I can't seem to solve. I position myself on a record in Table1(Tab1), click to Tabl2(Tab2) and it's blank.

How do I get Table2's data to show up in Tab2? Or should I just surrender and put everything in one huge table and be done with it...?

H

[comment]

Let's use meaningful names for your tables, please - even if it's only the generic Parent and Child (linked by ParentID).

I was hoping to have Table1 show up in Tab1 and Table2 show up in Tab2.

You can have fields from the Parent table show in Tab1 and a portal to Child in Tab2. That would be on a layout showing records from Parent.

Or, you could have fields from the Parent table show in Tab1 and fields from Child in Tab2, placed on a layout showing records from Child. But in this case, when you move to the next record, you always move to the next record in the Child table. If you are looking at Tab1 and the next child record belongs to the same parent, you will see no change.

Note also that if you import into the Child table, you must also import (or populate in another way) the ParentID - otherwise the imported records will be orphans.

[Hoib]

OK, meaningful names - I got it. Sorry - I'm just learning... I really am trying to understand it... but what a struggle! Here's where I'm stuck again...

I backed up. Tried to do this step-at-a-time. Forgetting Parent/Child and portals for the moment. I decided to try to just set up one .fp7 with one table in it. Created the "hh_Members" .fp7. Using Windows Explorer, I double-click hh_Members.fp7. When first called up, the layout shows <Table Missing>. I go to Layout Mode > Manage Database > there's the single table called "hh_Members. It has 13 fields, 3502 records. I clear the layout completely off to restart again. Used the Field Tool to drag over fields from the "hh_Members" table on to the single layout, Layout 1. Again, this is the same thing I keep running into all the time (either with field establishments or importing): When I release the mouse, FM puts up the specify field box but with <unknown> in it, nothing on the dropdown except Manage Database. I hit that and it takes me direct to (you guessed it) hh_Members with 13 fields and 3502 records.

I looked at File > Manage> External Data Sources. results are -- Name: hh_Members, type: FileMaker, Details: FilePath:file:hh_Members.fp7.

Why in the heck is this table which is clearly there, not able to be brought in to this layout?

[comment]

I don't know. Why don't you try deleting the layout and creating a new one. See also if you can reproduce the problem in a new file.

[Hoib]

Problem solved! I just took a break, went back looked over everything. Created a new table, with new fields, linked it in relationships so that it would show in my import dialog. Imported 3502 records, went to the Parent layout, and saw that the new table was there to draw from. Created portal, swung in the fields. Done.

I wish I knew what exactly went wrong with what I'd previously done. I guess I'll never know.

Thank you consultant... You're more than patient...

H