How to know if a parent table is used by a child table.

[El_Pablo]

Hi,

Is there an obscure trick to know if a parent table is used by children table?

I know that I could add all the children tables to the parent main TO and add a calculated field in the parent table. But again, I will need to modify the calculation each time I add a new child.

Eg A person table which is used in many TOs. A person as an employee, as a boss, as a buyer, ...

Edited January 18, 2010 by Guest

[bcooney]

Are you doing a subtype/supertype setup? Wouldn't you have the parent foreign key in the child record?

[El_Pablo]

There is a parent FK in each child table.

Eg

In a system, a person can be an employee. And another person can be a customer.

There are at least 2 TOs of person here. Three if we use the Anchor-buoy method.

PERS_PERSONS -= pers_EMPLOYEES

PERS_PERSONS -= pers_CUSTOMERS

EMP_EMPLOYEES =- emp_PERSONS

CUS_CUSTOMERS =- cus_PERSONS

[bcooney]

I don't understand. If you have a foreign parent key, then the child "uses" the parent. What is the question, again?

[El_Pablo]

Let say you have a big system with many TOs of Persons.

Is it possible from the Persons point-of-view to know every children it has without doing a calculated field in Persons.

Example

A person record Joe Smith is an employee and he is also customer. If I want to delete I need to be sure that this record is not use anywhere else.

Do I absolutely need to create a calculation in persons to check if this record is used by a child table.

Calculation :

PERS_PERSONS::isUsed =

not isEmpty (pers_EMPLOYEES::fkPersonID) or not isEmpty(pers_CUSTOMERS::fkPersonID)

This calculation could prevent a record from being deleted if a script check this field.

But this calculation is a bad design since there is a dependancy in the "persons" table to its children.

Sorry, it's hard to explain...

[bcooney]

I think I understand. You don't have child tables. You have self-joins. All TOs are based on the Person table.

imho, the calc in Persons is the only method given your structure and the calc does give you one place to check for a person that's "in use." You could avoid the calc and put all the logic in the Delete Person script, but why is that bad design? You are checking dependancies...

[El_Pablo]

Well, from a purely design pattern point of view, this is a bad design because there's a need to modify the calculation each time there's a new child table that use the person table.

I guess that it is not possible to apply all design patterns in FM.

[bcooney]

You mean a new relationship from PERSON would require an update in your "IsUsed" calc. You aren't creating new tables.

[El_Pablo]

Well if in the system structure, the developper needs to add a person info related table such as "phones" which has an fkPersonID in it.

There will then be the following relationship

PERS_PERSONS -= pers_PHONES

And there will be a need to modify the isUsed fonction.

Sorry, perhaps I don't use the good vocabulary.

[bcooney]

OK. In this example you have created a new table "PHONES". Still, no magic that I know of to see if a record has any related children other than the calc you've described.

[El_Pablo]

You confirmed what I thought... no magic here.

Thanks!

[LaRetta]

Well, I don't agree with the structure but that's just me. One thing you can do ... take one copy of each child _pk_ID field (from each table) and place it on your parent table. Change the number format to boolean 'yes'. Label above each field the child table occurrence name (the type of child). Disallow entry into the child fields in Browse mode.

This will eliminate the need of the calculation and you will always instantly see if that parent has children and which types of children (which table) they are in. Just a thought! :smile2:

[El_Pablo]

Well, I don't agree with the structure but that's just me. One thing you can do ... take one copy of each child _pk_ID field (from each table) and place it on your parent table. Change the number format to boolean 'yes'. Label above each field the child table occurrence name (the type of child). Disallow entry into the child fields in Browse mode.

This will eliminate the need of the calculation and you will always instantly see if that parent has children and which types of children (which table) they are in. Just a thought!

I guess you meant "place it in your parent "LAYOUT""?

Otherwise, if we put a child field in the parent table, we break the second normal form...

In any system, we must respect at least the third normal form.

Maybe you could explain your view.

[LaRetta]

I said, "place it ON..." and not IN. But yes. I meant parent layout.

[bruceR]

Don't forget the relationinfo function, which would allow you to dynamically find all relationships and loop through calcs to evaluate them. But the whole problem here sounds dreadful.

[bcooney]

Bruce, LaRetta, I am curious as to what is it about this scenario that causes your concern. It seems a very common need to validate that there are no dependent relationships when deleting a record. Of course, this is in the scenario like he's described where a person is one of many types, not the typical Inv>InvLI where you'd want a cascading delete.

[El_Pablo]

The relationInfo function, I forgot about this one. I will try some test with it.

Thanks!

[comment]

Filemaker cannot tell which relationships are "dependent" and which are not. You can either test for no related records through ANY relationship (including a parent table), or you need to specify which relationships are significant for this purpose. And, of course, it is difficult to specify a relationship to a table which does not yet exist. Contrary to popular belief, Filemaker is not psychic. :)

[El_Pablo]

Is there a function or a way to find all the occurences of a table?

[El_Pablo]

Since I'm using a standard naming convention (anchor-buoy), I think I might figure out a trick to get to my goal.

Here's my test script

A few more lines and I will convert this script to a generic one. Maybe, there's a possibility to convert the script to a custom function.

Sorry as you know there's no copy-paste as text in FM scriptmaker...

[El_Pablo]

With this script, I get all the relationship from my person table.

[David Jondreau]

Interesting. I've never used RelationInfo(), but it seems relatively simple to build a custom function that would do this test for you. If you have a naming convention that you'll always have the same field name be non-blank in the child records.

[El_Pablo]

That's the important point in FM : STANDARD NAMING CONVENTION.

I attached an empty file with the script and dependant CFs.

People might want to give it a try or maybe someone would like to convert it to a custom function.

[LaRetta]

It seems a very common need to validate that there are no dependent relationships when deleting a record.

Is there? If the parent record (Joe Smith) and if the structure is properly related, the children should be deleted because they are irrelevant, orphaned, headless and meaningless without a parent. That's the big giveaway that the structure is wrong here. But I don't believe in deletions; it seems there will always come a time when someone will ask, "Who was that guy that ..." etc.

Of course, this is in the scenario like he's described where a person is one of many types, not the typical Inv>InvLI where you'd want a cascading delete.

It is not structured as one type of data per table but rather many tables with Joe Smiths in it. The only reason there is a need to check child tables, and not allow a delete if any children exist, is because Joe Smith in one table is treated as a different Joe Smith in another. It is this multi-child (same-type data) structure which makes us cringe because it will complicate the solution for remaining world history.

[El_Pablo]

In many case, there's a need to share a same parent. Eg In POS system, you want to know if an employee buys a lot of the store items. You need to share a person record which is related to an employee and customer record. We call this third normal form (3NF).

[LaRetta]

"We call this third normal form (3NF). "

Please don't spat relational theory to me of all people (you continually do that). But you still miss the point of proper normalization.

If Joe Smith is a person then he can be a Type Employee and also a Type Customer and you don't need an Employee table and a Customer table - only a Persons table (and possibly a join table). But please proceed as you will ... you seem to be an authority on it all so why are you seeking assistance here?

Edited January 20, 2010 by Guest

[El_Pablo]

Mea culpa, I didn't want to make people feel like dumbs. It's kind of a reflex, because I'm currently fixing a system that a person developped long time ago (for 12 years...) without knowing anything about simple NF and it made me mad that a non-developper could create such a mess. I won't do the theory stuff anymore.

Back to the point.

An employee is not necessarily equal to a customer. Eg Does a customer has a current pay rate or a need to keep a social insurance number? Why should I put all these specific field inside the same table and not separate them?

Hmmm... but that question has been debated in another thread.

[LaRetta]

Apology accepted and I apologize for being sensitive. It's just that sometimes people who come from other disciplines think we 'filemaker' people just crawled out from under a wood pile and have no understanding of 'true programming' or 'true relational theory.'

I won't answer your question since you indicated you're researching it. But let me explain a situation and see if it helps put it in perspective. I took over a design where there were 22 files. Each file was a sales rep and contained their customers (these were all Act databases). There were also files for manufacturers, jobbers, retailers and employees. And many of these persons were MULTIPLE types, a Manufacturer who bought products as well, or worked as a jobber in certain situations and so their information was entered in all relevant files.

If William Tate called, new reception would frantically ask, "who are they?" They didn't want to ask the person (and rightfully so). So they searched the manufacturer file then Salesperson A's file then ... you see the picture? Sure, in FM we can script the searching through all the tables/files for this person but that person is sitting on hold on the phone while this is happening. And a new receptionist might be searching for someone named Bill Kaiser only to find out that they are the CEO of their very own company!

People are unique. We should look in only ONE place for a person. If receptionist gets a call from William Tate, they should look in one table and it should show that William Tate is a manufacturer of one of our products but he actually buys our products as well) and it then should say who the sales rep is and so forth.

You can have aux fields off of the persons table (which contain only the unique information pertaining to each type) or you can have fields all in one table (unused fields are cheap); only use another table if you won't need to filter by them. If you use a join table with TypeID and PersonID, that will be the ONLY place to look to know every role a person might hold.

[El_Pablo]

Interesting example here. Since the explanation is not related to the initial question and if you don't mind I quoted your post in the related thread.

Sold house - buyers - sellers

[bcooney]

What I had in my head during this thread was the structure of the current system I'm working with--it's "Grahamed." There is a People table, and what we term as "satellites" for each type of person. But, the People record has the foreign key for each satellite relevant. So, if my satellite foreign keys are all empty, then that Person record can safely be deleted. All People finds happen in the People table, and gtrr Satellite.

Yes, comment, I used my lingo. There is no such thing as a "dependent relationship," just the existence of child records.

[bruceR]

Filemaker cannot tell which relationships are "dependent" and which are not. You can either test for no related records through ANY relationship (including a parent table), or you need to specify which relationships are significant for this purpose. And, of course, it is difficult to specify a relationship to a table which does not yet exist. Contrary to popular belief, Filemaker is not psychic.

False. FileMaker can report on its relation info and this info can be used to examine the relationships and determine existence of related records. These calculations can be done dynamically based on the configuration of the database at the time the script is run.

[comment]

I suggest you read that part again.

[David Jondreau]

Here's my first stab at a custom function:

//ExistingChildren(RInfo; table)

//This function will test to see if children existing for any relationship on the current TO that does not have "Delete" option

//Result is a list of "nodelete" relationships which have existing records on "child side"

//RInfo parameter should be RelationInfo(Get(FileName); Get(LayoutTableName))

//table parameter should be Get(LayoutTableName)

//function should be run on layout based on TO you want to test

//this CF could use a lot of help

//David Jondreau, Wing Forward Solutions, LLC

//Version 0.1



Let([



//grab first set of info

break = Max(Position(Rinfo; "¶¶";1;1);4);

thisSet = Left(Rinfo; break-1);



//Test option for no delete

thisOptions = GetValue(thisSet;3);

noDelete = not PatternCount(thisOptions; "delete") ;



//could escape deletes here

//parse relation info

//grab one relationship criteria

thisRelation = GetValue(thisSet;4);



posTable = Position(thisRelation; table & "::";1;1);

posEq = Position(thisRelation;"=";1;1);

lenRelation = Length(thisRelation);

lenField = If(posTable = 1; lenRelation - posEq -1; posEq-1);

startField = If(posTable = 1; posEq+2; 1);

field = Middle(thisRelation; startField;lenField);

//test for children

childrenExist = not IsEmpty(GetField(field));



//return relation if children exist

thisResult = If (childrenExist and noDelete; thisRelation & ¶);



//remove just tested Set from RelationInfo

valRinfo = ValueCount(RInfo);

valSet = ValueCount(thisSet);

newRInfo = RightValues(RInfo;valRinfo - valSet-1) ;



//test for more sets

recurse = ValueCount(newRInfo) >3  ;





result  = thisResult & Case(recurse; ExistingChildren( newRInfo; Get(LayoutTableName)))



];

result

)

[comment]

//This function will test to see if children existing for any relationship

How do you know they are children - not a parent or self?

[LaRetta]

Although you realize I don't believe your situation warrants the complexity of the Graham method, I believe you can still get your answers with a simple script. If you disallow ability to delete parent without script, your Delete button could dynamically check the status of all children and wouldn't need to be adjusted as child tables are added (thanks to suggestion by Bruce).

Attached is a script-only solution. It currently just tells you how many related child tables have records for the current parent you are viewing. It could be easily modified to capture each populated child table occurrence name as well.