cascading relations

[Rob 7 Collins]

I have some tables to keep track of family informations: a parent table, a child table, and a mapping table that relates parents to children. Each have only one occurance in the relations graph.

The relations graph has equal relations between the parent table's key field and the map's parent.key field, and the child table's key field and the map's child.key field. Both these relations have the "Allow creation of records" and "Delete related records" set on the map side of the relationship ... but when I add a new parent or child it does not add a family map record.

Obviously I've made some terrible blunder or else I'm just not undertanding how those relations options work. Please help me out!

[RalphL]

You have a many to many relationship with mapping table being the join table. You will have to supply the data to the mapping table. I use another relationship based on Parent Key = Parent Key AND Child Key = Child Key. This is a one to one relationship.

I have a sample if you are interested.

A side note: what happens when the children have children? I would put all in one table and use the join file map them.

[Dave Graham]

In your example, mapping records are not automatically created when a parent/child record is created; this is the expected behavior. Alternatively, you could also script it such that a mapping record is automatically created when a parent/child record is created. Once you begin entering data into a related field, the related record will automatically be created and linked (i.e., the primary key will be inserted into the foreign key field in the mapping table). In a many-to-many scenario like this, you will want a portal to the mapping table from each of the parent & child layouts.

[Rob 7 Collins]

You have a many to many relationship with mapping table being the join table. You will have to supply the data to the mapping table. I use another relationship based on Parent Key = Parent Key AND Child Key = Child Key. This is a one to one relationship.

I have a sample if you are interested.

A side note: what happens when the children have children? I would put all in one table and use the join file map them.

I want a many to many, I think. That's why I'm using a mapping table. This way, if there is only one unit in the family (one child/a parent who did not fill in the information about their child) they are still listed in this table. After adding a child, and then a parent, you can go to a layout and relate the two records in the map table, creating a family. you know?

could I use an and condition, as you suggest, and have an entry in the child and parent tables that has no key value (a default entry, so to speak), to get around this?

(The parent and child fields in the map are not required values, BTW.)

inre the note: this is for local, public schools. Once they have graduated they are no longer a child in the system. If they have a child who attends public school at the same time they do, they would become a parent and we collect different information. But that's a rare occurance because you cannot attend public schools past the age of 21, and a child rarely starts kindergarten before 5 -- you would need the parent to have given birth while 16, and not have graduated until the year they were 21, with a gifted child, for this to occur. If you assume the parent graduates before turning 20, they gave birth at age 14!

[Rob 7 Collins]

In your example, mapping records are not automatically created when a parent/child record is created; this is the expected behavior.

Why is this the expected behavior? How do cascading records work in FileMaker?

...btw, the idea about the (addtional view) portal from family member layouts is great! How I came to this chronologically in the build, I was dreading doing that because I didn't want that view to be the only layouts from which a family relationship could be built -- you've got to have a listing of either parents or children so that anyone in the relationship can be used as a starting point to build the relationship. But as suppliment, those views are definitely still worthy!

[RalphL]

I want a many to many, I think. That's why I'm using a mapping table. This way, if there is only one unit in the family (one child/a parent who did not fill in the information about their child) they are still listed in this table. After adding a child, and then a parent, you can go to a layout and relate the two records in the map table, creating a family. you know?

could I use an and condition, as you suggest, and have an entry in the child and parent tables that has no key value (a default entry, so to speak), to get around this?

(The parent and child fields in the map are not required values, BTW.)

When you create a parent record it should have the ParentID auto entered. When you create a child record it should have the ChildID auto entered. Then from either the Parent or the Child you should be able to create a record in the map. If you use the AND relationship when you create a related record both of the foreign keys will be filled in.

What do you mean by, "The parent and child fields in the map are not required values"? The ID fields are necessary for this to work. Combined they are the Primary key for this table.

There is no way for FileMaker to create a Child Record from the Parent table or a Parent record from the Child table.

[Rob 7 Collins]

thanks Ralph.

I'm not worried if parent or child records cannot be created automatically by adding a parent or child. I want the creation of a parent or child record to create an entry in the map table.

"The parent or child fields in the map are not required values": In the map table, the fields for the foreign keys relating to family member records, have no validation settings. There is a third, autogenerated field in the map which is the only required field. It is the key I've created for the map table. All this means is, in theory, a map record can associate a child to a parent, a child to no one in particular, or a parent to no one in particular. (It can even associate no one to no one else, but why bother? *smirk*)

What I mean is; if I broke my key fields for the parent and child tables, so that they each had a single record stored that contained no key value, but then returned them to normal so no further such records could be created ... and I made the relationship between the child/parent and map table an AND relation, with record creation and deletion properties set on the map side of the relationship ... Would FileMaker add a new map record immediately as a consequence of a new parent? Likewise, as a consequence of a new child?

[RalphL]

First of all FileMaker does not create a related record when the parent is created. It can be setup to create a related when data is entered into a related field, usually in a portal. In a many to many you have two foreign keys in what you call the Map. Leaving one blank as you suggest gives you garbage. The primary key for the join table is the concatenation of the 2 foreign keys, and like any primary key it should be unique.

What I am suggesting is that you use another relationship to create the records in the Map. I use a global field in the table for the primary key of the other table. I make an AND relationship using the primary key and the global to the foreign keys in the join table. This relationship is set to allow creation of related records. When you enter data into a field based on this relationship you will create a new record in the join table with both foreign keys filled in.

I use a value list to select the value to enter into the global field. I filter the value list to show only unique choices.

[Rob 7 Collins]

Eke! I am so very bad, at following forum discussions! I'm sorry, I'm sure I've misinterpreted you some: at first, I thought you were telling me I cannot use many-to-many relationships and that I needed to remake the whole setup with only one-to-one relationships. (!)

Please bear with me a little here, I want to ensure I understand...

First of all FileMaker does not create a related record when the parent is created. It can be setup to create a related when data is entered into a related field, usually in a portal.

So fileMaker's behavior is:

(when Parent.table's primaryKey.field is related to Map.table's foreignKey.field)

a new Parent.table record will not create a Map.table record, regardless of the type or options of the relationship

editing a Parent.table record's primaryKey.field will change the Map.table's foreignKey.field, regardless of the type or options of the relationship

a new Map.table record can be created from the Parent.table layout by having a portal on the Parent.table layout that shows the Map.table, regardless of the type or options of the relationship. but for this to work the user must enter data in the portal to the Map.table.

Do I have it, at least the gist of it? *snap-my-fingers* Ok then - but what is the purpose of these options in a relationship: "create a new record in this table when a new record is created in the other table" and "delete related records in this table when a record is deleted in the other table"?

In a many to many you have two foreign keys in what you call the Map. Leaving one blank as you suggest gives you garbage. The primary key for the join table is the concatenation of the 2 foreign keys, and like any primary key it should be unique.

(Ah!) The map table is a join of the parent and child tables. It is a full outer join, though. Of course, it doesn't map every child possible to each parent, nor every possible parent to each child: by default it should only map a child to <undefined>, and an <undefined> to a parent. But only if the user chooses, it will hold the child to parent relations.

This does give me a whole lot more data possible in the Map.table ... all those child to <undefined> and parent to <undefined> . But it really isn't so bad, since I'm never automatically adding real child AND parent entries in the Map.table -- leaving that to the user.

Let's say I made both child and parent foreignKey.fields within the Map.table required values. Then it's an exclusive join table. Here's the problem:

My user adds a list of children to the system. Later, she adds another list, of parents. Most often she get's a list with both parents and their children, but those two previous lists exist too. Once a month my user checks to ensure no child is left unassociated to their parent in the database (due to the "No Child Left Behind" act).

Let's say the work load on this database is real low, she only has a couple hundred children... and three hundred parents. She goes to the child view, sorts by those not listed in the Map.table through some bit of script-magic I'd have to write. Then, she switches to the parent view, and runs that same type of script on the parent list, which I'd have to write. Going back and forth like this she finds children and parents who should be related in the database ... any time she sees the relation should be made, she can immediately add it through a portal on the parent or child layout. But she's got to flip back and forth a whole bunch to do this. That's a pain.

She'd rather have a single layout that lists in portals all the parents, and all the children, at once. From here you can go to the layout of the parent or child and view all the details, with a click on a "go to layout" button in the portal. You can use the standard FileMaker Find mode to limit either portal records. You can associate two records by simply highlighting them and clicking an associate button, which activates a script I'd have to write... still, that's not so bad of a script if you think about it.

But how from a single layout can I show all the parents and all the children in their portals? If make the layout work with the parent or child table, the other table's data is unrelated. If it were related, like if I put in a family.code field in the parent and child tables and worked from there, I'd still have to list only one parent at a time or one child at a time, whichever the layout works from... the simultaneous lists of both tables is what's important. --Thus a Map table, one that can have two foreignKey fields either of which may be filled in.

[Rob 7 Collins]

Would you post your example file? I guess I'm not seeing it.

[RalphL]

Just posted it at

http://www.fmforums.com/threads/showflat...true#Post160276