creating records via new relationship

[Bonnerbl]

I have an existing table (a). I added a second table (b)that has a one-to-one relationship with (a). Some of the fields in ( are calcd from fields in (a). There were already some records in (a) when I created this new record and relationship.

How can I get FM to automatically create the new records in (b)for those (a) records that already exist?

If I did an export of the records in (a) and then imported them back doing an update would that do it?

[Matthew F]

Related records will be created automatically if:

1) You have set up the relationship graph to allow new records to be created in table B, and

2) On Layout A you enter any data from a related field from table B.

To do this on all of the records you could run the following script the following:

1) Place the match fields ('ID') for both Tables A and B on Layout A.

2) Run the following script:

Show all records

Go to Record[First]

Loop

Set Field [Table A::ID; Table B::ID]

Go to Record[Next; Exit after last]

End Loop

[LaRetta]

If you are on Table A then you want to reverse the fields in this step:

[color:blue]Set Field [Table A::ID; Table B::ID]

If you ran this, you would blank out all your primary IDs in Table A. I realize it was just a typo but it could be a major problem if the person hadn't backed up first. :wink2:

Edited August 5, 2010 by Guest
Added sentence

[LaRetta]

I would question why you need to create empty blank records before they are needed.

Using your method, you will always have to deal with scripting creation of related Table B record to keep it up to date. Record creation in Table B should be natural by-product when data needs to be entered into Table B field. If the relationship is 1:1 then you can simply place all related fields from B onto A (or any fields except the ID) and let the user type into them.

So even though there may not be a related record, there will AUTOMATICALLY be created when the User needs it.

[comment]

I would question why you need a one-to-one relationship at all.

[LaRetta]

I considered asking about the purpose to suggest whether it was necessary but for once I didn't want to over-talk a subject. I also see nothing wrong with 1:1 relationships in some circumstances, such is the case with government form with 300 questions, there is no need to clutter the field definitions of your main table with fields which are only used for a specific purpose and there is no need to group or relate the information otherwise.

But I agree that normally 1:1's juice isn't usually worth the squeeze. :wink2:

[bruceR]

One-to-ones were even recommended by an FMI person at Devcon for performance reasons. Again - agreeing w LaRetta - frequently they are not worth the bother.

But in the example you provided - or in an a case with fewer fields but with large volumes of indexed text in some fields, keep the bulky fields in the second table. Find performance and other performance will be WAY faster because the only index you are passing is for the simpler base table. When you gotta search the fat fields, only then will you take the performance hit.

[hildafehd]

I'm having trouble getting a relationship to create a new record, but I'm new to Filemaker, so I can't tell if I'm doing something wrong or if it's not working as it should.

I am trying to create a database that will have several tables with one or two fields that feed into a master table. Right now I have defined two tables, the master and one feeder. I've created a relationship between fields in each table that are identically named, ID and Score and checked off in the "Edit Relationship" window that new records should be created in the master table via the relationship. However, in the feeder table when i create a new record and input data into ID and Score, the master table still remains empty of records.

Am I missing a step?

Oh, and let me know if more detail is needed to diagnose the problem.

Thanks!

[LaRetta]

Hi hildafehd, welcome to FM Forums!!

"I am trying to create a database that will have several tables with one or two fields that feed into a master table."

It would be most helpful to use specific names for your tables so we can visualize their purpose. Also, if this is a one-to-many relationship, which side is the many?

"However, in the feeder table when i create a new record and input data into ID and Score, the master table still remains empty of records."

In general, for 'Allow Creation of related" to work, you must set a field in your Master table (any field but the two used in the relationship because they will fill in automatically). You can do this by placing a Master field directly on your feeder table or by using script.

But I'm providing a generic answer since I can't comprehend the purpose or structure from your generic description. If you can provide clearer detail, we can help you further (if my current input is insufficient to move you forward).

[Bonnerbl]

Thank you for the correction to mfero's post. You both told me exactly what I needed.

If you read my post again you will see that the reason I needed to create the B records now was that the B table was added to a database that already had records in the A table. Going forward there wouldn't be any problem because B would be created automatically. The essense of my problem was that A records already existed before table B was created.

Edited August 10, 2010 by Guest

[hildafehd]

Well, it took me a while, but I got it to work. I hadn't realized before that you could a field from a different table onto a layout. This now makes me think that I don't actually need relationships here at all. I was just making a one-to-one relationship between variables that I wanted to show up on both tables. But, maybe I need it for the creating new records function?

Here is a more in depth description of what I'm trying to do with this database:

I want this database to contain data collected from standardized tests on subjects. I'm planning on having a table for each test and then a summary/master table that will have the score and other identifying information in it from each individual test's table.

So far, I have one test table, called LetterWord Identification. It has a slew of radio button fields for choosing 1 or 0 if an item is correct or not. Then there is a field called LetterWordIDscore, which sums the total of all the item fields. Then there is a field called Subject ID which is the identifier for that subject's data.

The "master" table is called StandardizedSummary and contains identical fields called LetterWordIDscore and Subject ID.

The relationships I made were one-to-one between these identically named fields in each table.

I ended up creating a field called Date in the Master table and adding that to the LetterWord Identification table layout to get the relationships to automatically create records in the master table when LetterWordIDscore, Subject ID and date were all filled out in the LetterWord Identification table.

Is that all clear?