Relationship problem?

[ron G]

I had this one working and then I did something and shot the poor thing in the leg.

In summary: [color:red]Vendors Layout works OK. [color:blue]PROJECT Layout doesn't. So, I copy Vendors layout and change the layout setup to show records from PROJECTS. PROJECTS "STILL" doesn't work. (of course I add fields from PROJECTS)

The fundamental problem is that none of the FOREIGN KEYS in IMPROVEMENTS will update when records are added to the PROJECT or PROPERTY fields. (Yes, I have [color:brown]But the VENDORS::FK_VendorID in Improvements WILL update. Why? I don't know.

ALL FK fields have "Allow creation of records in this table via this relationship" turned on.

I am stumped. Your thoughts are appreciated. Thanks

Ron

[bcooney]

I see that you're off to a shaky start. How about looking at this sample file.

What you must decide is what is the main table? Improvements or Projects? Then the other tables "feed" that data entry screen. Yes, they have their own data entry, but they have supporting roles.

In the sample file, I show how to set up a relationship graph using the Anchor/Buoy approach. If you don't start this way, you'll end up with a mess. Also, I created value lists and assigned them to popup menus. If the popups get too long, you switch to another method of choosing these values (popup window in list view).

Take this apart and ask questions.

Topic_190806.zip

[Fenton]

The fundamental problem is that none of the FOREIGN KEYS in IMPROVEMENTS will update when records are added to the PROJECT or PROPERTY fields

I'm afraid the above statement makes no sense. Foreign key fields don't "update". Neither do you add "records to... fields". In any case, both Project and Property are "parents" of Improvements, so adding record to a parent table would have no affect whatsoever to existing children of previous parent records. Maybe the preceeding is just problems with symantics, I don't know.

There is some confusion about where foreign keys go. If Improvements is a child of Project, then you do not need or want an fk_ImprovementID in Project; nor an fk_VendorID, nor an fk_ProjectID (why is it a foreign key in its own table?). If Project was a direct child of Property, then you would have a fk_PropertyID, so that is correct.

But you do not have Project as a direct child of Property. You've put Improvements between them. That will still work, sort of, but if Project is a direct child of Property it really should be between Property and Improvements.

I have no idea what Project List is supposed to be. It looks like a self-relationship of Project, based on its primary ID to another ID in Projects, producing some kind of subset of projects?, possible but unlikely.

[Fenton]

P.S. I think I can guess why you put Improvements directly attached to Property. So that you could add records to Improvements from there. But then you have to choose which Project. Which is inefficient. If Projects was a direct child of Property, and Improvements a direct child of Project, then you would go from a Property to a Project, and add an Improvement.

The relationship from Project to Improvements would be based on BOTH ProjectID and PropertyID, with [x] Allow creation turned on for Improvements. Then you'd automatically get both IDs into Improvement.

But, you may say, how can I see all the Property's Improvements from Property? You would add another "instance", another table occurrence of Improvement directly to Property, based on only the PropertyID, much as you've done, but not used for data entry (necessarily, though it could be). It may have another instance of Project hanging off of it, so you could see the Project name.

Redundant, possibly. But it is more correct relational design. You will often have what a beginner might think of as redundant table occurrences, especially if you use the anchor-buoy structure, which bcoony mentioned and which I highly recommend. It will make your FileMaker life much easier in the long run.

A naming convention is also mandatory. There is a PDF from FileMaker at this (long) address:

http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_alp.php?p_sid=ipzDJkOi&p_lva=6126&p_li=&p_accessibility=0&p_page=1&p_cv=&p_pv=&p_prods=0&p_cats=&p_hidden_prods=&prod_lvl1=0&p_search_text=6126&p_new_search=1&p_search_type=answers.a_id

And I could not find it any other place, which is too bad. I often find that the FileMaker site is "pretty" at the cost of actually being able to find technical information.

[ron G]

I'm afraid the above statement makes no sense. Foreign key fields don't "update". In any case, both Project and Property are "parents" of Improvements, so adding record to a parent table would have no affect whatsoever to existing children of previous parent records. Maybe the preceeding is just problems with symantics, I don't know.

There is some confusion about where foreign keys go. If Improvements is a child of Project, then you do not need or want an fk_ImprovementID in Project; nor an fk_VendorID, nor an fk_ProjectID (why is it a foreign key in its own table?). If Project was a direct child of Property, then you would have a fk_PropertyID, so that is correct.

But you do not have Project as a direct child of Property. You've put Improvements between them. That will still work, sort of, but if Project is a direct child of Property it really should be between Property and Improvements.

I have no idea what Project List is supposed to be. It looks like a self-relationship of Project, based on its primary ID to another ID in Projects, producing some kind of subset of projects?, possible but unlikely.

+++++++++++++++++++

[color:blue]Thanks for the analysis. In using the FK fields, I was trying to provide a 'path' via the PK to FK fields into related tables. I expected that basing a layout on PROJECT and then adding a new record would result in IMPROVEMENTS::FK_PROJECTID being updated with the serial from PROJECT::PK_PROJECTID. Therefrom, I would have a path to PROPERTY. But, What I did failed so I must try and see why what you promoted succeeded.

Any additional ideas would be helpful.

Thanks again.

[Fenton]

It would perhaps be best if you uploaded a small example file of what you're doing. It is pretty straightforward to create a child record from a parent record via a portal. If typing into the blank portal row is what you're talking about, then it should create a record in the child table, with the parent ID as a foreign key.

[x] Allow creation of related records must be on for the relationship, on the child side of the relationship (not on the parent side).

Creating a new parent record has no affect on any other table. It makes no sense to expect anything to happen automatically in any child table just because there is a new record in the parent table.

[bcooney]

Maybe, you'd want to look at the demo I attached?

[Lee Smith]

Hi Barbara,

Please use the Reply button in the Body of the Post that you are Replying to. It make it a little more easier to follow whom you are replying to, and it doesn't get lost like this one today.

TIA for your Cooperation,

Lee

[bcooney]

Thanks, Lee. Yes, I'm sure Fenton doesn't need a demo file of FM basics! Ron G. that nudge was for you.