Relationship Help

[RobK68]

I have three tables

AR

--

field a

TK

--

Field c

Field d

PJ

--

Field e

Field f

Relationships

AR: field a -> TK: Field C

TK: Field d -> PJ: Field f

Tk: field d shows PJ: field e through a lookup

I want to show PJ: field e from the relationship between AR's relation ship to TK's. I am not getting the correct value.

Why?

Thanks,

Rob

[Søren Dyhr]

I'm pretty sure I'm not the only one here getting dyslexic by the way you present your problem here.

No one can design a database of any value without knowing context and purpose.

Next issue you need to explain why a lookup is what you need here, lookups needs only to be used when something historic needs to be tracked, while it usually only is the tunneled value needed.

Final issue is that the choise of tables not seems to be correct normalform wise, since a 3 level one_2_many_2_many should be done recursively instead of a deliberate entity duplication. My guess is that what's wrong here instead, is that one of the relations is pointing in the wrong direction.

Perhaps will this template give you a hint to what you're after:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000717

...This means you have the autoenter part of the keying, posistioned on the wrong side of the relationship!

--sd

[RobK68]

Ok, let me rephrase. I have table 1 with a relationship to table 2. Via a field which is an integer. I want to show another field from table 2 based on the relationship. The second field is not showing me any data.

How can I set this up so I am seeing data in the second field from the table?

Thanks,

Rob

[mr_vodka]

You should be able to see the realted data. Are you using the correct table occurence?

[LaRetta]

Rob, PLEASE put names to your tables so we understand their purpose. Saying Table A and Field J tells us nothing because we can't get the vision of the purpose, as Soren says. Speaking in abstract gets us nowhere. By providing REAL table and field names, we can get the idea.

What integer do you have these tables joined on? Are you sure it is the same number in both tables? What is the meaning of this join field? :wink2:

[RobK68]

Ok, sorry.

I enclosed a jpg of my relationships.

I want to show the project name field from the projects table from the Action Register table. The Action Register is tied to task id in the Tasks table. The Project id in the Tasks table is tied to the Projects table.

I had tried using a lookup on the field projects from the Action List table, but that is not resolving correctly.

Thanks,

Rob

Edited March 10, 2008 by Guest

[Søren Dyhr]

Ok the actionRegister is the join table in the matter, but I would say that tasks is somewhat misplaced. It ought if I'm getting this correctly just be subordinated the actions, while projects is the other many side.

By the way is it a normalization 1 NF error to have suportinfo twice, it should be two different records in the task table. One could even argue that a fair amount beyond the keyfields in the join table should be broken out in the tasks table, which brings in another aspect.

You have probably been too tied up with categorizations you pursue, Should perhaps be called task items instead becoming parallel records with the supportinfo records. To distinguise each record in the renamed table should a field be added to distinguish it typewise.

It looks as if you just sussed what the tables might go like??

--sd

[comment]

The structure looks fine to me. I don't see why you need a lookup - you can simply place the Name field from Projects on a layout of Action Register. In any case, a lookup should work, provided both the parent Task and the grandparent Project records are committed before creating a new action.

[Søren Dyhr]

Ok if the structure is correct, what is the difference between actions and tasks then? Is it that actions initiated purposeless then freely can be reassigned to a new task ...or??

--sd

[comment]

My impression is that tasks are predefined for a project, while actions record the actual steps taken by a person to complete a task.

Is it that actions initiated purposeless then freely can be reassigned to a new task ...

Well, yes - if you want to put it that way. One could also say that 'line items are added purposeless to an invoice, then freely can be reassigned to a new product'. Saying so doesn't change the essence: the action table is simply a join table between tasks and personnel.