Problems with loop, layout and iterating over parent-child relationship

[mhubley]

I am new to Filemaker (version 9), and have been ripping what little hair I have left out trying to do the following. I have two related tables, a parent and child. The parent table is fully populated, the child table is not, it is empty. I want to iterate over the parent tables records, and for each of them create a new record in the child table, relate it to the parent by setting the foreign key field, and then copy some other fields from the parent record to the new child record. Everything seems to work fine except for the iterating part - once I change layout to the child table (see pseudo code below) I cannot seem to ever change layout back to the parent table to move to the next parent record. What am I doing wrong here? I have also tried this with a counter variable instead of first/next and have the same problem. I know it is something stupid but for the life of me I can't figure it out, and have been banging my head on this for hours, it should be trivial. Thanks for any suggestions you can provide, surely this is one of the most basic things you want to do in a relational database so I suspect I am just missing something specific to how you do it in a Filemaker script. I have also tried it with/without the CommitRecords (seems to make no difference).

Go to Layout (parent)

Go to Record (first)

Loop

  Go to Layout (child)

  Create New Record

  SetField (Child:field1, Parent:field1)

  etc.

  CommitRecords

  Go to Layout (parent)

  Go to Record (Next; exit after last)

End Loop

[Quartekoen]

If you're simply trying to populate the child table with data that's identical the the parent, you could always try exporting it, then importing it into the child. It can be a lot cleaner and easier than scripting a looping copy of all the data.

[mhubley]

Thanks - its not identical unfortunately, its only certain fields I want to copy (and then delete from the parent table as well once I get the copy part working). I basically have a non-relational database I am trying to turn into a relational one, which seems to be a painful process in Filemaker.

[comment]

surely this is one of the most basic things you want to do in a relational database

Actually, it's the last thing I'd want to do in a relational database. Why would I create records unless I have some data to put into them? And why would I want to duplicate data from parent to child - isn't that what the relationship is there for, to let a child see the parent's data through it?

This last point is also the reason why your script cannot work: you are not establishing a relationship before trying to get data from the parent record. There IS NO parent record, until you set a foreign key in the child record to the parent's primary key, e.g.

Go to Layout [ Parent ]

Go to Record [ First ]

Loop

SetVariable [ $parentID ; Parent::ParentID ]

Go to Layout [ Child ]

New Record

Set Field [ Child::ParentID ; $parentID ]

# NOW YOU HAVE A RELATIONSHIP AND CAN DO:

Set Field [ Child::Value ; Parent::Value ]

Commit Records

Go to Layout [ Parent ]

Go to Record [ Next ; Exit after last ]

End Loop

[comment]

its only certain fields I want to copy (and then delete from the parent table as well once I get the copy part working). I basically have a non-relational database I am trying to turn into a relational one

It does sound like you'd want to import the parent records into the child table. You can do this directly (no need to export to another file), and you can pick which fields to import.

[mhubley]

Thanks, but I have done all that. I have established the foreign key and the relationship, it is the pseudo-codes' "field1".

And when you have a non-relational database in one table that you want to convert to a relational database in multiple tables, this is a pretty basic thing to have to do, and common when people construct databases and don't know what they are doing.

[comment]

Thanks, but I have done all that. I have established the foreign key and the relationship, it is the pseudo-codes' "field1".

No, you haven't. When you do:

Set Field [ Child::Field1 ; Parent::Field1 ]

there is no parent record yet. So the expression Parent::Field1 does not return a value. It cannot, because it doesn't know WHICH record in the Parent table is the one you want. At this point you are in the Child table, and all the records in the Parent table look the same.

And when you have a non-relational database in one table that you want to convert to a relational database in multiple tables, this is a pretty basic thing to have to do, and common when people construct databases and don't know what they are doing.

As I said in my second post (the first one was written before your clarification), importing the records seems more suitable for this task.

[mhubley]

Thanks, posts that crossed in the night, just saw your second, and the import records does the trick.

[Quartekoen]

There IS NO parent record' date=' until you set a foreign key in the child record to the parent's primary key[/Quote']

This is one of the things that bugs me the most about Filemaker. If I set a database to be the child of another, should it not see that other automatically as it's parent? Perhaps there are some logical issues that I'm not realizing, but it just seems like it would be natural, and having to create some relationships twice seems redundant.

[LaRetta]

If I set a database to be the child of another, should it not see that other automatically as it's parent? ... it just seems like it would be natural...

Nothing is natural in the world of computers - they must be programmed and that is what we do. The reason relationships don't happen automatically in FileMaker is that each of us may want different solutions for different purposes. Who says what is related? You? Me? What you consider related won't be what *I* want to relate on, thus we each must set our own logic within the bounds of the design.

If you read various posts here on Forums, you will see a hundred different relationships and a thousand different configurations on join criteria. We are given the clay from which to mold our own visions and we can change relationships at will; thank God we aren't limited.

[comment]

If I set a database to be the child of another, should it not see that other automatically as it's parent?

No. We are mixing some terms here, and that can be confusing. When you define a relationship between TABLES (or rather table occurrences), you're setting up the CONDITIONS under which a RECORD from one TO will be related to a RECORD from the other TO (these conditions, or rules, are also known as predicates).

So you can call the two tables related to each other - but still there may not be even one pair of records that fulfill the conditions set up in the relationship's definition.

having to create some relationships twice seems redundant.

You're not creating any relationships twice. After you have set up the rules, you need to make individual pairs of records conform to those rules. So setting a specific child's foreign key to a specific parent's primary key establishes the relationship between these two RECORDS - because now it conforms to the rule defined for the relationship between the two TABLES.

If you like, you can think of a relationship between tables as a kingdom. Related pairs of records are citizens - but you have to pass the citizenship test in order to become a citizen.