Beginner's question that may or may not have anything to do with relationships

[mosessmith]

I'm a new FileMaker user, and I'm trying to figure out how to make a field that is a list of fields from select records in another table. Maybe a field is not what I need? I basically want to display a list of records from a table that are associated with a single record in the current table.

Hope that makes sense.

Thanks,

Moses

[mosessmith]

Oh, and in an ideal world, I would be able to add to the list in the table's layout, and have it automatically create a record in the related table. I basically want a field that can contain a vector of records from another table, and that can be manipulated from either table.

[mr_vodka]

You can create a value list to dispay a field of the related records, but you will not be able to edit that list directly.

You may instead want to go with a popup window method. You can have a button on the bottome of the window that coudl add a new record to the list.

Popup window

[aldipalo]

If I understand what you are looking to accomplish you have two tables and you want to view all RELATED records in table 2 that are RELATED to a record in table one. That's a one to many relationship.

You need to relate the tables by a specific "Key Field." Usually, that is a serialized ID field.

e.g.

Contacts::ContactID = addresses::fk_ContactID

The Contacts::ContactID is set as a serial number. In the relationship you check "allow creation of records" on the addresses side. You now have a relationship set and you can look at data from table one while in table 2 or vice-versa.

I hope this answers your question. If not please elaborate and we'll try to help you.

I would also suggest that you go to this link:

http://www.foundationdbs.com/downloads.html

And read the following:

White Paper for FMP Novices

Database Design for FMP

[mosessmith]

I'd like to have table one relate to a list of records from table two (via a Key Field, for example), and then display a list of name data for each corrosponding record in table two. I would like to be able to add records to table two by adding to this list in table one, if possible.

I'll check out those papers.

Thanks,

Moses

[aldipalo]

Take a look at the "Sales Orders" layout to see an example.

Sales_Orders.zip

[mosessmith]

I think the problem I'm having here is getting a field to display a list of items, but I don't want to be choosing one of those items as the value of the field. The value of the field should be the list, and I'd like to display the entire list. Is this possible?

[aldipalo]

I think you need to show us an example of what you want. Are you saying you want a field that shows all the data in a group of other fields? Or a field that list all the data from one field from all your records? There are ways to do either, but, you must be more specific.

Edited March 23, 2007 by Guest

[uhhhhhh]

Hey guys. I think I know what mosessmith is talking about. I'm just getting started with FM as well, and am making a test database just to teach myself. I think I'm stuck the same place he is, so I'll tell you about my database, and then we can solve the problem

My database keeps track of all of the jerks I know, and why they're jerks. I have four tables:

Jerks

 - First Name (text)

 - Last Name (text)

 - Jerk Rating (number; validated at only 1 thru 10)

 - Jerk ID (number; serialized)



Notes

 - Jerk ID (number;related to Jerks::Jerk ID)

 - Note (text)



Cause List (a list of reasons for being a jerk)

 - Cause ID (number; serialized)

 - Cause (text)



Causes (which jerks have which causes)

 - Jerk ID (related to Jerks::Jerk ID)

 - Cause ID (related to Cause List::Cause ID)

In the relationship graph, I checked "allow creation of records" so that the database:

• Allows creation of records in the "Causes" table via the Causes::Jerk ID and Jerks::Jerk ID relationship
• Allows creation of records in the "Notes" table via the Notes::Jerk ID and Jerks::Jerk ID relationship

The Cause List table looks like

1 - Low blood sugar

2 - Bad parenting

etc.

When I'm in the layout view for the Jerks table, I still only see First Name, Last Name, Jerk Rating, Jerk ID. (I haven't modified the default layout at all.)

Basically, my questions (that will also, I think, help out mosessmith) are:

How do I change the Jerks layout so I can add notes about this guy?

Is my Jerks-Causes-Cause List relationship chain the correct way to accomplish this? If so, how do I change the Jerks layout so I can add "low blood sugar" and "bad parenting" as reasons why this guy's a jerk?

Thanks,

uhhhhhh

[uhhhhhh]

I'm about halfway through that white paper. It's definitely worth a read.

Looks like I need to make a "portal" to dsplay the records, and I need to make a platform for adding/editing the records in the layout. I guess all I need is a big text box and an "ADD" button, which would need to run a script.

What would the script look like?

[aldipalo]

I think if you take a look at the Sales_Orders.zip file you'll see some of the applications and scripts.

I would also suggest that you attach a copy of your "JERKS" application. Lord knows we could all use such a system.

Kidding aside, we could then see what your relationships and layout look like. Don't worry, no one slams you for your design, but, you will get helpful hints on setting things up. Just make sure it is not an overwhelming file.

[uhhhhhh]

Here's the jerks database.

I'd really appreciate any help you can give me checking my relationships, and modifying the Jerks layout so I can add Notes and Jerk Cause records...

Jerks.fp7.zip

[aldipalo]

I'm really slammed today. If I can not get to it tonight I'll try tomorrow. Unfortunately, I'm traveling Wed. - Fri. so it's tomorrow or bust I'm afraid. Perhaps someone else will jump in as well.

[The Shadow]

Here's your Jerks file with the changes you made.

For the notes field, you just drop the related field on the layout, you can select the table when you drag a text field out in layout mode. (You need allow creation of related records, which you had set).

For the jerk causes, I setup a portal with a popup to let you pick the cause id, then have a non-editable field showing the textual reason from the cause list.

Hope this helps.

Jerks.zip

[mosessmith]

I ended up solving my problem by simplifying my tables and presenting things a little differently. Not exactly what I wanted to do but I solved my problem. I'll check out this stuff for future reference. Thanks for the help.