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

[El_Pablo]

If the DB follows a "fk pk" (or equivalent) standard convention the function could be modified to check if it's a parent or a children. In my case, with a little tweaking this function should work nicely.

I'll try it later today. Thanks D J!

[LaRetta]

Well, dang, the attachments seem to be broken again. Here's the script called Delete Parent:

#

Set Variable [ $relations; Value:RelationInfo ( Get ( FileName ) ; "Parent" ) ]

Set Variable [ $howmany; Value: Mod ( ValueCount ( $relations ) ; 4 ) + 1 ]

#

Loop

Exit Loop If [ $howmany = 0 ]

# Then grab each portion from each line as ...

Set Variable [ $grabline; Value: Left ( GetValue ( $relations ; 4 ) ; Position ( GetValue ( $relations ; 4 ) ; " = " ; 1 ; 1 ) ) ]

Set Variable [ $count; Value:$count + not IsEmpty ( GetField ( $grabline ) ) ]

Set Variable [ $howmany; Value:$howmany - 1 ]

Set Variable [ $relations; Value:RightValues ( $relations ; ValueCount ( $relations ) - 5 ) ]

#

End Loop

#

#

If [ $count ]

Show Custom Dialog [ Message: "There are " & $count & " child relationships for this parent which have records. You cannot delete

this record."; Buttons: “OK” ]

Halt Script

End If

#

Show Custom Dialog [ Message: "Put your Delete parent record script step here"; Buttons: “OK” ]

#

Edited January 21, 2010 by Guest
Removed unnecessary comment

[El_Pablo]

If we add a superior loop to your script, it would be possible to traverse all TOs of the parent table.

Thanks for the idea.

[David Jondreau]

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

Well, there isn't a difference between a child or a parent from Filemaker's perspective, right?

I should have said "this function will check all relationships from the base TO of the current layout and see if there are any related records in those TOs".

The self is an issue, but self-relationships should be marked "Delete".

Edited January 21, 2010 by Guest

[comment]

If the DB follows a "fk pk" (or equivalent) standard convention the function could be modified to check if it's a parent or a children.

IOW, when you add a new table you need to be aware that this mechanism is in place and take care not to break it. How much different is this from having to add the new table to a calculation?

[El_Pablo]

IMHO, the main benefit here is to use a generic function (or script) vs hard coded calculation.

The hard part here is to stick to the standard from one developper to the other... and the boring documentation

Edited January 21, 2010 by Guest

[David Jondreau]

If the DB follows a "fk pk" (or equivalent) standard convention the function could be modified to check if it's a parent or a children. In my case, with a little tweaking this function should work nicely.

If you've got one TOG that contains the relevant relationships and you start from a layout based on the "parent", field naming is irrelevant.

Edited January 21, 2010 by Guest

[El_Pablo]

In my case, there can be more than one TOs of the parent. This is why I will need to call the TableNames function and filter out the parent from that list.

Your right about he naming convention, there is no need to check this.

[David Jondreau]

In my case, there can be more than one TOs of the parent. This is why I will need to call the TableNames function and filter out the parent from that list.

Not sure why you think this is necessary.

It doesn't matter if there is more than one TO for the parent, just that all the "child" tables you're checking the existence of are related to one TO.

Edit: I just realized this is step towards a referential integrity check.

And that maybe we don't need a layout. Can this be dropped into Access Privileges?

Edited January 21, 2010 by Guest

[El_Pablo]

Actually, it is not the case in the current project. Not all children are related to the main TO since I didn't see the need to represent it.

Should I attach all children to the main parent TO even if there's no use for these relationships?

[David Jondreau]

I don't know if it's necessary.

It would be helpful to see a copy of your graph and table list to make sure we're talking about the same thing.

[El_Pablo]

Hmmm... I cannot send the graph for confidentiality reason, but I'll create a small example which would explain the principle of my graph.

[El_Pablo]

A quick example is attached.

How would you get all the children of the "PERSONS" table if there is no relationships between the main TO of PERSONS and it's children?

[David Jondreau]

I'm having trouble downloading any files from FMForums right now. I'll check it out when we're back up.

[El_Pablo]

Here is a temporary link to the buySell.zip file.

http://www.mediafire.com/file/yc0frmzdoce/buySell.zip

[David Jondreau]

In this sample file I would definitely add TOs BuyersSellers and Employees to the Persons TO. That seems like a good admin Group to have.

[bruceR]

See relation info in attached.

buySell.fp7.zip

[El_Pablo]

Sorry for the late post

Some people might ask why this script could be useful. Well, let say you want to create a script to duplicate a record with all its children. A script looping through all TOs to check dependencies could be pretty useful to create a generic duplication script.

Thanks Bruce for your file, I'll check this on later today.

[El_Pablo]

Here is a modified version of BruceR's file.

I added a children relation portal.

There is still a problem with my script. I cannot part the parent and the child TO in a relation without looking for the "fk" string.

Maybe someone could check this out.

buySell.zip

[bruceR]

Here is a modified version of BruceR's file.

I added a children relation portal.

There is still a problem with my script. I cannot part the parent and the child TO in a relation without looking for the "fk" string.

Maybe someone could check this out.

Check out what? What are you trying to do?

[El_Pablo]

Sorry, it wasn't clear.

Since I'm looking for the "fk" to determine the side of the child table in the relation, is there a better way to determine this?

[bruceR]

Sorry, it wasn't clear.

Since I'm looking for the "fk" to determine the side of the child table in the relation, is there a better way to determine this?

Why are you looking there? The two sides of the relation are left and right of " = " (or other relation operator) and the foreign side is always reported as the left side by RelationInfo().

[El_Pablo]

You are right that the foreign table is always at left.

Without naming convention, how do we know if the foreign table is a parent or a child?

[bruceR]

You are right that the foreign table is always at left.

Without naming convention, how do we know if the foreign table is a parent or a child?

I still have no idea what you are trying to accomplish.

Is there any meaningful difference between parent and child for this purpose? Are you looking for the existence of related records or are you looking to find out what tables are in use by others?

Edited January 28, 2010 by Guest

[El_Pablo]

Okay, I'll try to explain with a concrete case.

I need to duplicate an appraisal report.

A report is used for house appraisal.

A report is used to calculate the value of a given house (called subject). The house is a scenario, i.e. the house is one-story, has a brick facade. So the appraiser enters and select data here and there to get the value of the house.

In some case, the appraiser needs to create a second scenario for the subject, which is the same house, BUT instead of having the facade in brick it's made of stone. Hence, the value of the house changes.

In a single report, the appraiser might need to assess the value of multiple buildings. Eg : House with a detached garage. So now we have a "buildings" table.

For now I see these tables and relationships.

Reports -= Buildings -= Sidings

A house is a single family building, now there are buildings with apartments. And in each one of these, there are some rooms (Living room, kitchen, bedrooms, ...).

We now add to building the "apartments" table and a "rooms" to the latter.

So :

Reports -= Buildings -= Apartments -= Rooms

Now the land on which the building is built, lands and buildings are independent. Since a building can burn, but the land is still there. Each land has a value of its own. A Report can have multiple lands.

Reports -= Lands

Here are the relationships and tables :

Now the original question was "How to know if a parent table is used by a child table?". Why such a question? Because I wanted to create a generic script that could duplicate a record "branch" and all this without using the GTRR script step.

Imagine a script that you could just call "duplicateRecordBranch (recordType; recordID)", this script could duplicate a record and all of it's children. This is why I wanted to know if there was a way to check if a record had children in the whole graph.

I hope this clarify my needs. :

Edited January 29, 2010 by Guest

[bruceR]

Sorry, you're on Windows, so the problem is complex.

On a Mac, it can be solved with a single line of applescript.

Assuming a layout with auto-create relations; and on which no calc fields are displayed; and on which the parent record key is not displayed.

Perform applescript:

Create new record with data (get data current record)

[Søren Dyhr]

I would definitely seek to solve this via a recursive structure, and try to exploit both GTRR (in the found set version) as well as Constrain/Extend found set ... each should be fired successively in each branch. But by all means keep every "Lease/Location" in the same table.

But as I kind of suggested was "Lease/Location" ... was it because a normalization would land you somewhere else logically than the classifications you have thought of.

It very near to "Parties" when dealing with contacts, where we in our minds distinguish between companies and persons although a fair share of attributes are ... ehm - shared.

--sd

[El_Pablo]

Thanks guys!

In our case, we don't need to know exactly the lease and location, since we are evaluating a building cost. But I'll keep that advice.

[Søren Dyhr]

Try to investigate Shawns file here:

http://fmforums.com/forum/showpost.php?post/266796/

--sd

[David Jondreau]

I need to duplicate an appraisal report.

A report is used for house appraisal.

A report is used to calculate the value of a given house (called subject). The house is a scenario, i.e. the house is one-story, has a brick facade. So the appraiser enters and select data here and there to get the value of the house.

In some case, the appraiser needs to create a second scenario for the subject, which is the same house, BUT instead of having the facade in brick it's made of stone. Hence, the value of the house changes.

In a single report, the appraiser might need to assess the value of multiple buildings. Eg : House with a detached garage. So now we have a "buildings" table.

For now I see these tables and relationships.

Reports -= Buildings -= Sidings

A house is a single family building, now there are buildings with apartments. And in each one of these, there are some rooms (Living room, kitchen, bedrooms, ...).

We now add to building the "apartments" table and a "rooms" to the latter.

So :

Reports -= Buildings -= Apartments -= Rooms

Now the land on which the building is built, lands and buildings are independent. Since a building can burn, but the land is still there. Each land has a value of its own. A Report can have multiple lands.

Reports -= Lands

Here are the relationships and tables :

Now the original question was "How to know if a parent table is used by a child table?". Why such a question? Because I wanted to create a generic script that could duplicate a record "branch" and all this without using the GTRR script step.

Imagine a script that you could just call "duplicateRecordBranch (recordType; recordID)", this script could duplicate a record and all of it's children. This is why I wanted to know if there was a way to check if a record had children in the whole graph.

I think it's possible to do what you want, using RelationInfo() but I've only gone so far as to apply it to children, not to grandchildren. It may be extensible, but it seems like more trouble than it's worth. And may be unnecessary.

I don't think your relationship structure is solid. I don't see why Sidings needs a Building FK. Unless it's really a join between Sidings and Buildings. Same with Lands. That should be a Report-Land join table, no? And Buldings shouldn't have a ReportID in it.

You may need a join between Report and Buildings (Scenarios?).

Why would you need to duplicate the Apartments and Rooms records? Those aren't going to change, are they?

Does a Report encompass more than one Scenario? In other words, does an appraiser list the value of a building with a brick facade and with a stone facade on the same report?

[El_Pablo]

Mac users have a big advantage here.

Concerning the recursive structure, I think GTRR is too limited. Because you have to manually set the related table.

I think I need a structure which shows which table is the parent of whom, the field used in the relationship and the layout each table is associated with. So using this structure, I might get to something recursive using "Set field by name" and constrain/extend found set.

[El_Pablo]

People might want to look at what is real estate appraisal.

http://en.wikipedia.org/wiki/Real_estate_appraisal

This portion of the thread is the "cost approach".

I think it's possible to do what you want, using RelationInfo() but I've only gone so far as to apply it to children, not to grandchildren. It may be extensible, but it seems like more trouble than it's worth. And may be unnecessary.

I know it's a lot of work, but if I can do something reusable in other projects this could be very useful.

I don't think your relationship structure is solid. I don't see why Sidings needs a Building FK. Unless it's really a join between Sidings and Buildings. Same with Lands. That should be a Report-Land join table, no? And Buldings shouldn't have a ReportID in it.

You may need a join between Report and Buildings (Scenarios?).

Let say we are doing a report "A" for the actual cost of a house. All the calculation are made so there is a given cost at the end in the report. Now we must create a second report for the same house, but for renovation.

Now there is a new report "B", the owner wants to add an extension to the house and change the siding. He wants to know the final value of his home. If I modify anything to the original house, this will also modify the calculation in the report A. Which is a "no-no".

Why would you need to duplicate the Apartments and Rooms records? Those aren't going to change, are they?

Yes the building can change, since it's a construction cost scenario. Scenario 1 : Building with 3 large apartments. Scenario 2 : Building with 6 small apartments.

Does a Report encompass more than one Scenario? In other words, does an appraiser list the value of a building with a brick facade and with a stone facade on the same report?

Yes and no, a report = 1 construction scenario for residential housing. BUT LATER I must implement commercial buildings which might have multiple buildings.

[El_Pablo]

BTW, the building part is full of "type" tables to reconstruct the building. Eg : Roofing, windows, doors, flooring, etc.

E.g.:

Building -= flooring =- MaterialTypes

[Søren Dyhr]

I don't think your relationship structure is solid. I don't see why Sidings needs a Building FK. Unless it's really a join between Sidings and Buildings. Same with Lands. That should be a Report-Land join table, no? And Buldings shouldn't have a ReportID in it.

This is my take here as well, but I'm still struggling to see why all this copy'ing really is healthy.

Perhaps is the situation used in this model is more correct:

http://www.fmforums.com/forum/showpost.php?post/149069/

Perhaps is the copying synonymous with pre-population??

--sd

[El_Pablo]

For the prepopulation, there are bunches of lookup tables partially filled.

Contextual attempt for explanation :

House A is the actual house and has a valuation report.

The user wants to create a new report based on house A, because the owner might want to renovate his house. We call this "potential value".

The need to copy records is like cloning a house. If I clone house A, I will have a new house B based on house A. These two become independent.

At cloning time, House A has a brick facade and vinyl sidings so do House B.

The appraiser, then, changes house B facade and one wall to stone, but leaves 2 walls with vinyl sidings. He also adds a bathroom to the basement, a home theater room and an interior sauna. He calculates the new value for house B.

I hope this example clarifies the need of duplication.

PS : Don't forget that I didn't show all the lookup tables. Eg : sidingTypes.

Edited February 1, 2010 by Guest