Newbie Portal Problem

[LGJ001]

Hi - hoping someone can help here. I've created a number of Access Databases in the past, and now I'm trying to move off Windows onto Mac OS - so I'm trying to recreate my Access databases in Filemaker Pro 9.

So I'm starting with a simple relational database along the lines of Author --> Book. I've set up the tables and relationships, and I want a Data Entry / Display form. I've created a form Layout based on the Author Table, with a Portal to show related records fomr the Book Table.

But the Portal will not populate with records. If I enter a new record in the portal it creates the correct new record in the table with the correct index keys, but will not display in the portal when I return to the Author record.

I guess I must be doing somethign stupid, but I've read the documentation and searched the help, but I can't finad any clue as to what I'm doing wrong!

Thanks for any help or suggestions!

[bcooney]

There are two main ways to populate a portal.

The first is to allow creation of related records by checking the box to do so in the edit relationship on the relationship graph. This allows the user to click in the portal fields, and as they add records, any fields that are in the relationship will populate. So, if you have a relationship Author::Book by AuthorID, then _kF_AuthorID (foreign key) in books will populate.

The second method is to script the creation of a new child record. Turn off the allow creation setting, and add a New Book button above the portal. Your script would be something like (pseudo-code):

Set Variable $AuthorID, Authors::AuthorID   //capture the parent key ID

Go to Layout Books (based on a Book TO)  //a form view

New Record

Set Field _kF_AuthorID, $AuthorID  //set the foreign key

Now the user is in Books, creating a book record, with the Author foreign key already populated. To get them back to the Author Form, give them a button, "View Author" that does Go to Related Record Book::Author by AuthorID.

Having said all that, are you sure that the portal on the Author Form is based on the relationship from Author::Books by AuthorID?

[LGJ001]

Thanks for your reply! I think the tables and the relationships are set up correctly. (Of course I could be wrong on this!).

I can create new Book records in the portal, and the resulting records in the Book table have the correct Author_ID field to link them to the Author record. (I think this is what you mean by foreign key).

My problem is that when i move to a new Author record the Portal does not show Book records that had previously been created for the now-displayed Author record.

In the portal Set-up Dialog, I have set it to "Show related Records from ", and I added the fields I want. I kind of think its a Form Layout problem... The manual doesn't really go into much detail on this and none of the sample databases that came with Filemaker have an example that is close to what I want that I could copy...

[jstaphse]

See sample file. Note, Book4 has 2 authors(Both existing in the sample file).

book_author.zip

[LGJ001]

Perfect! That's exactly what I want to do!

Thanks!

[bcooney]

Actually, the sample file was not constructed properly. If an Author has many books, and a book can have more than one author, then you need a join table. See attached.

book_author.zip

[LGJ001]

Yes, I hadn't got as far as many to many relationships - I'm struggling to get the basics working!

I looked at the both the sample files, and I managed to copy the design to make my database work (which is good), but I don't really understand why (which is bad).

What I had done (and this is how I would have done it in Access), is create a Table called Authors and a Table Called Books. The Books Table would have a Numeric Field "Author_ID" and I would use that t establish the relationship between the two tables by making it equal to the "Author_ID" in the Authors Table.

This seems to work in Filemaker in the sense that when I create new records the fields populate with the correct values - its just the Portal on an Authors form doesn't display matching Books records. The only different is I can find is that this creates a one to many relationship between the an Author Record and Books Records, whereas the way its done in the Sample files created a many to many relationship.

[comment]

What I had done (and this is how I would have done it in Access), is create a Table called Authors and a Table Called Books. The Books Table would have a Numeric Field "Author_ID" and I would use that t establish the relationship between the two tables by making it equal to the "Author_ID" in the Authors Table.

That should work (for a one-to-many).

This seems to work in Filemaker in the sense that when I create new records the fields populate with the correct values - its just the Portal on an Authors form doesn't display matching Books records.

When you create new records in which table? If it's in Books, there will not be a related author until you enter the author's ID into the AuthorID field. The exception is entering books directly into a portal to Books on a layout of Authors: this way the book's AuthorID will be entered automatically (if you enabled the relationship to allow creation on the Books side).

Anyway, check that the match fields are of the same type, and if you still can't make it work, post your file.

[LGJ001]

Yes, I can get it working now... not sure what I was doing wrong before... I think I haven't grasped Forms properly... I suppose it doesn't help coming from an Access Background where they obviously work differently. The Manual doesn't really explain it very clearly - can anyone recommend a good source to help me get my head around the forms thing?

[bcooney]

Not sure what is confusing you. In FM, layouts are ways to design views in the data. If you have a layout set to "View as Form" then you see one record at a time. "View as List" lets you see multiple records, and "View as Table" is somewhat like List View, but cannot be embellished with nav buttons or formatting so are usually used for development only layouts (raw views into the data). Reports are typically List views, but optimized for printing.

A typical setup in FM is to have one layout be your Main Menu (sometimes a table with 1 record is what is the basis of this layout). Then a user clicks a button on the Main Menu, and goes to a List View layout. On the List View, each row has a button that goes to the form view. This is how a customer "drills down" to what they need. The List layout usually has a Main Menu, Find, and Show All buttons.

The Form view layout sometimes has portals to child records. It has can have Main Menu, View List, Find, Next and Previous buttons.

Have you read White Paper for Novices ?

[LGJ001]

I think my confusion stems from having had a legacy in Access... As I've spent more time on these problems today its starting to come clear. If FM were my first Database program I think I'd be finding it easier, whereas what I have been doing is starting from how I would expect Access to work and "translate" that into FM, which is not the way to do it because some of the underlying concepts are different.

Thanks all of you for your patience and help... I'm finding more and more that I can get what I want done, so I think I've turned the corner! I'll look at that white-paper too!

[bcooney]

It's like Steve Martin said of the French, "they have a different word for everything!" :

[jstaphse]

Hope my file helped, even if it was wrong. Did it in about a minute.

[LGJ001]

I guess the converse of that is George Bush: "The trouble with the French is they have no word for Entrepreneur" :