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.

See relation info in attached.

buySell.fp7.zip

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.

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

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?

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?

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().

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?

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, 201015 yr by Guest

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, 201015 yr by Guest

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)

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

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.

Try to investigate Shawns file here:

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

--sd

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?

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.

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.

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

E.g.:

Building -= flooring =- MaterialTypes

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

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, 201015 yr by Guest