Database issue with relationship...

[ctmarket]

[color:green]EDIT: The link has been fixed.

I have been argueing with my friend with regards to this relationship.

I have attached the database. Please take a look at the relationships. (Right Click, "Save target as").

Each Sector can contain any amount of activities.

Each Activity can contain any amount of Actions.

Each Action can contain any amount of steps. (Action Step Number - The order in which the Action is performed)

Sectors are defined by the user - so I am arguing that when a new Action record is added to the database, the user should select which Sector this action falls under - hence the need for a link from the Actions table to the Sector Table.

The final aim is to achieve a Print Layout like this.

So how would it be possible to show the sector name in the Print Layout without entering the relationship in the Actions Table?

Edited November 3, 2006 by Guest

[Ender]

If all Actions share the same Sector as the Activity they are a part of, then it is not necessary to tie the Sector directly to the Action. If you wish to see what the sector is for the Actions, just use the related Sector Table information.

In some cases, a grandchild record might need to inherit the parent key from the child record, so that filtering or certain types of Finds will work correctly, but this does not appear to be necessary here.

[ThatOneGuy]

Hi ctmarket:

I have been argueing with my friend ...

Indeed, it does sound like there's a relationship issue! Sorry, I couldn't resist. Just kidding!

I tried downloading your file, but the link didn't work. Could you take a look at that?

Each Sector can contain any amount of activities.

Each Activity can contain any amount of Actions.

Each Action can contain any amount of steps.

I'm down with that! This shows good data normalization, so I think the structure is sound.

So how would it be possible to show the sector name in the Print Layout without entering the relationship in the Actions Table?

If we have Table Occurrences that reflect the structure above, we can "pull" data across multiple occurrences from the Sector table onto the Actions layout. Experiment with a field on the Actions layout that's targeted to the related Sector.

[W]hen a new Action record is added to the database, the user should select which Sector this action falls under - hence the need for a link from the Actions table to the Sector Table.

Here's where process and interface play huge parts. For example, if you allow the creation of related records through the appropriate portal, an Activity record can automatically populate itself with its "parent" Sector. Likewise, an Action record can automatically populate itself with its "parent" Activity.

On the other hand, if you're designing with modal windows in mind where users are guided through record creation, you'll need some fields (e.g. pop-ups) where they can select the appropriate parent records. In that instance, you'll need some additional Table Occurrences to "drive" the Value Lists.

Let us know how if this helps. :goodluck:

[ctmarket]

Thanks for your assistance!

I have fixed the database link... Any suggestions on the Print Layout?

I thinking that I would need to use a script or something to display it - not entirely sure...

[ThatOneGuy]

Thanks for your assistance!

Pleasure's mine ... hope you and your buddy are on "speaking terms." :

Any suggestions on the Print Layout? I thinking that I would need to use a script or something to display it - not entirely sure...

Are we talking about getting those related fields to display on the Print layout, or, in a broader sense, how to get the Print Layout to appear?

[ctmarket]

ta! :

I was talking about getting those related fields to display like this:

Print Layout

[ThatOneGuy]

Hey ctmarket!

I might be oversimplifying your question (or plain ol' misunderstanding it), but you can get related fields to display on your layout if you ...

• Go into Layout Mode

• Add a new field (could use the "Field tool" or copy/paste an existing field or duplicate an existing field)

• Double-click on the field to bring up the Field/Control Setup dialog

• Directly above the list of fields on the right side of the dialog box, you'll find a popup list of Table Occurrences in your file next to the Display data from label. Pick the related table occurrence, then pick the "parent" field you want to display.

I sure hope this is what you were asking about. BTW, I'm still not able to download your file ... the link's just not working for me. (Maybe when you're offline the file's not available. I don't know.) If we need to talk further, would you be able to post your database as a .zip file, using the forum's manage files option?