relationships not working

[Hack]

Hi here

I have problem with relationships on job tracking data base. I start will creating an client and then setting up an Project. my problems happen when I wont to change my project client to another client it lose all the data in that protect, this only happen when I change the client iD on the project sheet not the client name. I have attached the data base which mostly like best place to start with this as it bit hard to explain

thanks

D

BD_job_tracking_v5Clone.fp7.zip

[Fenton]

I doubt that the data is completely "lost." it's just misplaced. One thing I notice is that you use BOTH the ClientID AND the Company Name in relationships. This is redundant. If you've got a real ID, then you do not need to use the name also. It makes a weak point, which may be why you're "losing" data.

It's also possible that you're accidentally changing the Company name of the Client, on a foreign layout, which would break this relationship; besides being a mistake on its own. If you put the Company name into the other files as a local field, for some reason, then it should NOT be editable there.

[Hack]

Hi Fenton

you help about year ago with data base relationship.... i think

you where great help

Yes you are right the data is not lost when I change the client_id back the data comes back as well

the problem happen when I change the client_ID the relationship breaks. How do I change the client_id and client name realated to that project?

I think the problem is back with Client table and relationships to tables i.e. projects etc

Cheers D

[Fenton]

I don't know that it's a "problem" so much as a fact of relational design. Basically you've entered the ClientID in just about all the child records of the Project.* AND you're using it in most of those relationships. So, if you change the ClientID in Projects you would have to script a process to Go To Related Records in each child table and change it there also. PITA?, kind of.

So you have to ask yourself whether you really need the ClientID in those child tables. Is it necessary to sort for Subsummary reports? Finds? Are you viewing those Project child tables from Client? (I don't think you are, most of those table occurrences are the only occurrence of their table on the graph).**

If none of the above, then the ClientID is unnecessary in Project child tables. All you really need in those relationships is the primary ID of the Project table. (Which, BTW, you are not using; you're using the Job Ref instead; which may be OK, but also a slight weakness, as it is likely user-enterable in Project).

If you need/want the ClientID in all those tables you must take responsibility to update it if you change the ClientID in Projects.

Basically, if I designed this I would remove the Company Name from all relationships, and remove the ClientID from any Project child tables and relationships where it was not needed. A relationship should use as few fields as possible to make an unambiguous connection; unless they are being used to pass data (via "Allow creation of related records," which yours are not). Using more is redundant, and creates maintenance problems, as you're seeing.

*One of the Project child relationships uses the Company Name as the key instead. This is inconsistent and inferior.

** I tried to see if you could possibly be using the ClientID in other layouts. Some of your layouts are missing their table occurrence. Delete those layouts.

(P.S. Your Print Specs definitely uses the ClientID. So those would need to have their ClientID updated.)

Edited March 5, 2007 by Guest
PS

[Hack]

Hi Fenton

sounds like I have big job on my hand to to get the relationship design to work properly

When I have tried to change the relationship layout the whole data base falls over

I see what you mean about the client_ID bing through the child tables

The problems I see when I remove the Company name client_id from all relationships how do I know what project go with which client if see what I mean

so I should use job_ ref number instead of client_id to link tables. The problems is how do I link from the client table to the project table with out using the client_id

May be the it best is develop the a new data base from the start and then hopefully I wont run into these problems. The problem them would importing the old data.... either way it looks like mission

Cheers D