Puzzled: Parent FK remains in Children Records when Parent is deleted

[dmontano]

Puzzled here... Is this the correct and expected behavior...

First I created a recursive one-to-many table that builds a hierarchy. Since hierarchy implies ownership and destroying the parent record should destroy children records - I ENABLED creation AND deletion of related records in the relationship options. This work as expected: Delete record and its children records are deleted. So far - so good.

Then, I wanted to create an Aggregation structure. Since Aggregation implies no ownership and destroying the parent record should NOT destroy children records - I ENABLED creation but DISABLED deletion of related records in the relationship options. This "appears to work": Delete record and its children records are still there with no parent - sort of.

What puzzles me is the children records still have the parent key value in the parent key field. The Parent is gone - and there is no indication that the previously deleted relationship exists - except for this parent key value left behind.

When I try to make sense out of this I get this conclusion:

The Parent record is gone along with its PK. So there is no way the relationship exists. So does that mean setting the parent key in the child record leaves a STORED copy of the parent key it that field? Maybe this is obvious to others - but I did not expect that, and having a value in the field after deleting the parent would make one believe that it HAS a parent. So if you wanted to find all records that had missing parents - you would not be able to ascertain this fact be checking to see if that field were empty across all records.

So... that leads me to believe that something is amiss. I prefer to have an empty value in all of the children records where the parent was destroyed.

I suspect a few answers to this problem may be to "script the deletion" - but I would rather not. I am trying to understand why this is the behavior and how to correct this so parent values in children records are destroyed.

Any insights appreciated.

[comment]

does that mean setting the parent key in the child record leaves a STORED copy of the parent key it that field?

Yes, that is exactly what happens. It is the child's job to remember who its parent is, while the parent couldn't care less. The other part is of course that what happens in the parent table, stays in the parent table (as far as affecting stored data in other tables).

[bruceR]

It seems you expect Filemaker to read your mind, in a very personal and particular and anthropomorphic way; and that expectation is quite unrealistic.

What other data that you have entered with full intention into a field should FileMaker decide to change or delete? I don't think you'll find any data system anywhere that operates the way you expect.

How does FileMaker know that you won't populate the parent table tomorrow with new records? Maybe this is a daily archive or update process. How does FileMaker know that the parent table is merely missing, perhaps temporarily, perhaps intentionally, perhaps by error?

You put numbers (or text) into a field. On purpose. You own the process. The data stays there until you choose to do something different.

[dmontano]

Comment... thanks.

Its funny how much I think I need to understand the underpinnings of how things work in FileMaker. Like I said before, this might be so obvious to others - but to me - the forest and the trees sure get in the way of me picking this stuff up intuitively.

To further my question, should I be concerned about this remnant "parent key" sticking around? If I had to "find" all records who have had there parents deleted - how would I go about that? Using the structure I have set up and described would leave the deleted parent key in the child - therefore I can not search for an empty parent to discover those who are up for adoption - so to speak.Better to prevent the remnant key in the first place - I think.

It seems it would make sense to purge this value when the parent is deleted. If so, do I need to script this or is there another way to set up the parent fk "field" to not store - but to reference? If script - something along these lines?

When deleting parent, ask if user is sure of deletion...

Find all children of parent...

Loop through child records to replace parent fk field with ""

Delete parent

Exit script

I am trying to do as much as possible without scripts and focus on the structure doing as much lifting as possible, then resort to scripts. Lastly, just to clarify - is there anything "wrong" with this old parent key laying around? Am I wrong in being concerned that the field contains a value?

Thanks in advance

[dmontano]

Hey Bruce,

I had to look up anthropomorphic to give you a clue as to my intelligence level. I appreciate the clarity you offer. Like I said some things might be very obvious to others - not to me. Filemaker does things that I have been surprised about, for example: I just discovered creating a record four tables away I can get the 4th, 3rd, and 2nd table to create related records by setting a field in table 1. I thought I would have to explicitly set the "connecting" key in each table.

Seems very literal - but Filemaker just sucks in the key for the two intermediate tables. Frankly - that puzzles me. Furthermore, deleting in ANY of the related records in any of the four tables deletes the related record in all four tables. I know - deletion cascades - but it is not something I expected.

So... for all I knew, the remnant parent key would vaporize when the parent is deleted. I do appreciate your help.

Thanks

[comment]

should I be concerned about this remnant "parent key" sticking around?

I'd say no, but it depends on what you're doing.

If I had to "find" all records who have had there parents deleted - how would I go about that?

You could search for * in the related Parent::ParentID field and omit the request.

[bruceR]

Comment... thanks.

Its funny how much I think I need to understand the underpinnings of how things work in FileMaker. Like I said before, this might be so obvious to others - but to me - the forest and the trees sure get in the way of me picking this stuff up intuitively.

To further my question, should I be concerned about this remnant "parent key" sticking around? If I had to "find" all records who have had there parents deleted - how would I go about that? Using the structure I have set up and described would leave the deleted parent key in the child - therefore I can not search for an empty parent to discover those who are up for adoption - so to speak.Better to prevent the remnant key in the first place - I think.

If there isn't a problem with orphan records, there certainly is no problem leaving the parent key. You would never do a find the way you propose anyway; it would not prove anything. Well, certainly if the parent key is empty, it can't have a parent record! But also if the parent key value is erroneous, it doesn't have a parent record. You positively establish the presence of a parent record by searching the way Comment describes. If you allow orphans, why spend time on this? Offhand I can't imagine why this kind of tidiness is worth the effort.

[bruceR]

Filemaker does things that I have been surprised about, for example: I just discovered creating a record four tables away I can get the 4th, 3rd, and 2nd table to create related records by setting a field in table 1.

No, you set the field in table FOUR; not in table 1.

[panchristo]

Question Can't he have a calculation auto-entering/deleting the key in the child table if a ParentsCount calc=0?

[comment]

No, because the auto-entered calculation will not re-evaluate.

[dmontano]

I did not - and usually do not - explain things very well. Bruce is - I think - correct. I meant to say I am setting in table 4 and tables 3, 2, and 1 become populated. However, table 4 layout has one field from table 1 that gets set. Once that occurs, record creation occurs in all four tables.

I am not clear as to what sequence each table creates the related record. My guess is this sequence: Table 4, Table 1, Table 2, Table 3. Tables are joined in a one-to-one-to-one-to-one as such:

Table 1 --- Table 2 --- Table 3 --- Table 4

TABLE 1

table_1_PK

table_1_field_name

TABLE 2

table_1_FK

some field

TABLE 3

table_1_FK

some other field

TABLE 4

table_1_FK

table_4_field_name

On TABLE 4 layout there are two fields:

table_4_field_name (editable text field)

table_1_field_name (related field - no entry allowed.)

User enters data in "table_4_field_name". OnObjectExit script trigger on "table_4_field_name" that copies itself and pastes into "table_1_field_name". Once that occurs, records in all four tables are created and related. Deleting in any one of the four tables cascades the deletes (as set up through the relationship options) as I intend.

Hopefully that explains what I meant to say better. It was behavior like this that has befuddled me.

So coming back full circle to the original topic: I did not know the reason why the parent key is sticking with the child when the parent is deleted. My only guess was the value was stored. Seems obvious to all - but I'm not knowledgeable enough about the real mechanics going on.

Then I started wondering if it was "bad" to have this value hanging around at all. If it's there - it must "mean" something. And that's my point: what does it mean? If you were to look at all the records from just the key values in say table view - it would leave someone such as myself to believe that it has been assigned a parent and still holds the assignment. But that is not what that value means. It means maybe there is a parent assignment or there was a parent assignment previously but not any longer.

Paint my silly - but I like to rid myself of any ambiguity. I have a hard enough time trying to learn what is going on.

I really do appreciate all the help.

[bruceR]

It would really be helpful for you to post your file or a simplified example. It's hard to figure out what you're talking about. Especially since you have mixed in a script trigger.

It means even less than you think it means. It means that you placed a value in a field. You're responsible for it. You put it there. You put your sock on your foot. You put the dirty dishes in the sink.

If you want the dirty dishes cleaned and out of the sink, you need to do something about it.

At one point you made a declaration that one field would be used to relate to another field. Fifteen seconds later you might have decided, no, I'm going to use CompanyName for this relation.

What is the FileMaker application supposed to imagine about your intentions?

Or maybe you did it the other way around; a common starting out scenario. You related the invoice records to contact records based on name. And you decided oops, I need use number key fields here; and you changed the relationship.

What is supposed to happen to everybody's name?

It is important and necessary to recognize that you have much more detailed and direct control over everything about your data.

[dmontano]

Well I just encountered an instance where deleting a parent record and having the remnant parent key in it previous child records fk field is a problem. I am using a calculation that determines hierarchical position. The calculation uses the presence of an parent FK in the child record to determine if that record is a root - or not.

The calculation works well - so far as I can see - except this dependency I created on the parent fk. I guess one way to get around it would be to assign the dependency on the existence of a "name" instead of the key.

However, I think it would be best if I rid the key from fields that reference it. And that's where I have no idea in how many other tables that key may eventually end up being used in facilitating relationships. I know I could look through the solution to find all FKs that are used, but that's overhead and only something someone with developer access could find anyways.

Is it possible to script a delete of a record and find all instances of stored references to it in order to loop a replace with "" in the parent fk fields used wherever?

Thanks in advance.

[Julian K]

Going off of what Comment said ("You could search for * in the related Parent::ParentID field and omit the request. "), you could base whether it's root or not by doing something like this for the calc field:

if(isvalid(Parent::ParentID), "child", "root")

This tests the relationship for a parent, so since such a relationship doesn't exist with an orphan ( : ), it would come up as "root".

[comment]

Is it possible to script a delete of a record and find all instances of stored references to it in order to loop a replace with "" in the parent fk fields used wherever?

What do you mean by "wherever"? There's only one place that the parent fk can be stored - in the ParentID field of its immediate children. You can find those with a simple GTRR. In fact, you don't even need to find them - you can run the replace from the parent record.

[dmontano]

Sharp eye Comment - wherever is pretty vague. The context of most of my questions where centered around the one table Account Type. That table will be related to another table to define the "Type" of "Account" an account is.

Since I found one instance where the remaining parent key is problematic in the example of the hierarchy calculation, I wondered what is the effect when this table is also to be used with a different table altogether. Once again, deleting a record - deletes the relationship as expected, but the foreign key remains in the "other" table.

I am quite fuzzy about this - and I suspect I have over thought this. In any event, I have a least uncovered why remnant parent keys get in the way of some things - such as the calculation that returns the position of any record within a table within multiple hierarchies.

Since my calculation "broke" because of these remnant keys, I built a script that seems to cure my problem. I suspect the script is full of holes - but at least it seems to work for now. Attached screenshot: any comments regarding the script are highly welcomed.

Thanks

[bruceR]

Same problem - think different. Learn to proggram robustly. Don't waste your time doing things like this.

1. Stop copy/paste; learn to use set variable and set field.

2. Instead of building a wacky Rube Goldberg mechanism to staple to your bandaid, that will always be out of date, fix your calc logic.

Edited June 22, 2010 by Guest

[Vaughan]

It seem to me that a lot of the problem is that you allow a parent to be deleted. Quite simply: don't allow it. Force the user to assign the children to another parent or delete them before allowing the parent record to be deleted. That solves the problems of "ghost" parentids and record with no parents.

[dmontano]

Thanks Bruce and Vaughn,

I know the script is pretty hosed - but at least I have documented somewhat for myself what I am trying to do.

As for not allowing the deletion of parents - I agree in a true hierarchy - you disallow that behavior. In an Aggregation - you do allow it. For instance:

AGGREGATION

European Economic Community once held these countries as members:

France

Germany

Italy

What happens if the European Economic Community ceases to be an organization? The countries do not cease to exist. The membership relationship does cease though.

"TRUE" HIERARCHY

USC is composed of:

University of Southern California

Marshal School of Business

If USC closes, so does the Marshall School of Business. Unlike an Aggregation, in a True Hierarchy if a Parent ceases to exist - the child should cease to exist as well.

In the cases above - we would control these different behaviors through FileMakers "allow deletion of related records" checkbox in the relationship option to accommodate the fact that you do NOT delete the children in an Aggregation - but you do delete children in a "True" hierarchy.

At least this is my understanding - and it took quite some time to discover there was a difference. I also uncovered other "hierarchies" that I am still trying to understand the subtle differences.

Once again - thanks for all the comments. I have so much to learn, and frankly, it gets a little overwhelming at times.

Edited June 24, 2010 by Guest
parent child terms reversed

[comment]

I think Vaughan's point was: do not allow the deletion of records while they still have children.

The method you posted earlier:

When deleting parent, ask if user is sure of deletion...

Find all children of parent...

Loop through child records to replace parent fk field with ""

Delete parent

would seem like a good start.

[Vaughan]

What happens if the European Economic Community ceases to be an organization? The countries do not cease to exist. The membership relationship does cease though.

You have a many to many relationship here: a country can be related to many unions and a union can have many countries. Use a join table:

[unions] -- [Members] -- [Countries]

When a Union is deleted, delete the Member records automatically.

[dmontano]

How would you represent the hierarchy? I had used the EEC to just get the idea across that it is an aggregation structure - where you CAN delete a Parent and not the children.

In the context of a recursive relationship - one base table - is where I am focusing the question and my efforts. I am not modeling the EEC, EU or the countries that were or are members - but the point was just to illustrate there is a difference with a True Hierarchy and an Aggregation.

Comment had noticed my values earlier and noticed that there seemed to be no "like" values to deem them worthy of being placed in one table. I understand his point, but in the context of how I am using them I think it is appropriate.

In the context of Account Types the values are required to distinguish the Type of Account it is:

(Financial) A Bank Checking Account

(Financial) A Bank Savings Account

(Financial) A Brokerage Account

(Communication) An Email Account

(Communication) A Web Account

(Communication) A Fax Account

I am sure this could be modeled with a category table and subcategory and sub-sub category, or however many fixed levels you want - but the depth is always in question. That's why I choose the recursive structure for this case.

As always, I appreciate your input.

Edited June 23, 2010 by Guest

[bruceR]

Thanks Bruce and Vaughn,

I know the script is pretty hosed - but at least I have documented somewhat for myself what I am trying to do.

As for not allowing the deletion of parents - I agree in a true hierarchy - you disallow that behavior. In an Aggregation - you do allow it. For instance:

AGGREGATION

European Economic Community once held these countries as members:

France

Germany

Italy

What happens if the European Economic Community ceases to be an organization? The countries do not cease to exist. The membership relationship does cease though.

"TRUE" HIERARCHY

USC is composed of:

University of Southern California

Marshal School of Business

If USC closes, so does the Marshall School of Business. However, like an Aggregation, if a Child ceases to exist - the parent does not cease to exist.

In the cases above - we would control these different behaviors through FileMakers "allow deletion of related records" checkbox in the relationship option to accommodate the fact that you do NOT delete the children in an Aggregation - but you do delete children in a "True" hierarchy.

At least this is my understanding - and it took quite some time to discover there was a difference. I also uncovered other "hierarchies" that I am still trying to understand the subtle differences.

Once again - thanks for all the comments. I have so much to learn, and frankly, it gets a little overwhelming at times.

Well, thanks for finally revealing what you're actually talking about. This makes is clearer that the problem is a structural problem; you need a join table and then the join records are the ones you delete. The script you're trying to write is still very much a mis-directed effort.

[Vaughan]

Well, thanks for finally revealing what you're actually talking about.

Sadly Bruce, no. That was just an example to "get the idea across" of what the OP *doesn't* want.

[dmontano]

Not sure how to take the comments Bruce... Vaughn? You're both coming across like I have irritated you. I try to be polite, respectful, and express appreciation. I also try to explain to the best of my abilities. If I have made a comment (or comments) that either of you have interpreted as rude - maybe you can point that out. That was never my intent and re-reading my posts - I fail to see where I have offended. If you have construed it as such - my apologies.

On the other hand, if you are trying to shine the light on how ignorant I am - well, good job. I post in the spirit of getting some help. If I question or debate someone's response - it is about the topic - not someone's intellectual level. I don't think I would want to do that.

[Vaughan]

Your comments are not rude, merely time wasting.

First, determine the structure of the problem. Then create a data architecture to solve the problem.

I think you've started off with a clever recursive architecture and are now finding that it does not work for the real-world problem.

As Bruce and I have said, your data structure is wrong. It should have join tables.

[David Jondreau]

While there are some cool techniques out there about keeping track of keys so that each record stores its parent path in a single field, I've never found recursive relationships to be worth the trouble.

You can probably accomplish what you want using a different structure, or using "tags".

The most helpful thing you can do is post the actual problem you're having...not analogies. It looks like you're trying to keep track of "accounts".

What is an "account"? Is it some business rule you're stuck with, or is it your own creation.

What does an "fax account" have in common with a "checking account"?