Printing Related data that's 2 relationships deep

[spragueg]

I have 3 databases.

A (Campaigns)

B (Jobs)

C (Stages)

Relationship 1 Many (A) to Many (:

Shows jobs in each campaign. A job can be related to more than one campaign

Relationship 2 One ( to Many ©

Shows stages for each job.

The problem is I need to print this data so that each job appears with it's campaign(s). To do this I need to print in the (A)Campaign file. I need to find a way to get the ©Stages for each job to print in a portal in file (A) based on Relationship 1.

Anyone have any ideas?

[spragueg]

I cannot send the files. It's against my comany's policy. However, I would like to hear your thoughts.

I have decided that there is a way to do this although it is a huge pain in the a$$. I willl have to make a relationship for each possible step and then a calc that draws the data for each step in the (B)Jobs file. Then I can see the steps from file (A)Campaigns. So that means 30 or 40 relationships and 30 or 40 calcs in the (B)Jobs file.

What do you think? Will it be too slow?

[Kurt Knippel]

Here is a simple fix.

If any particular Job record is related to a single particular Campaign record and if any particular Stage record is related to a single particular Campaign record, then really each Stage record is related to a Job record.

Create a "CampaignID" field in the Stage file and have it lookup the "CampaignID" field from it's parent Job record.

P.S. This will work regardless of whether the field is a single ID or multi-key field.

Then one relationship from the Campaign file to the Stage file via the CampaignID field.

Easy as pie!

[falkaholic]

If i understand right. You want to relate a field 2 relation steps away.

What I do is have a calculation field in the middle database this is just:

databaeseC::fieldC

then relate the that file in the master db.

[RussBaker]

Given that your Jobs in file B will have a key field link to the Campaigns in file A, and also key links to the Stages in file C...

Why don't you do your report printing from the Jobs(: file, sorted by sub-summaries on Capmaigns(A) and showing a portal of the stages from Stages©.

This goes against my principles of always printing from the file that holds all the data - but if you are doing a one-page-per Camapign or Job print, there should be plenty of room on the page for a decent sized Stages portal. Of course, you won't be able to slide this portal...

Russ Baker

Canberra, Australia

The Land of TWOGM

[danjacoby]

quote:

---

Originally posted by Andy Gaunt:

Dan will probably murder me for this, but what the hey!

.

---

I will NOT murder you for this; I'm not that type of person.

I will simply hire someone to murder you; it's much cleaner that way.

[BobWeaver]

I have found that if you have File_A related to File_B which is related to File_C, then you can create an unstored calculated field Field_X in File_B with the formula:

File_C::Field_Y

Then, in File_A you can display File_B::Field_X which will carry the data accross the two relationships. These are not lookup fields, so you don't need to do any relookups.

According to all the information that I have about Filemaker, this should NOT work, but it does. Don't ask me why. There may possibly be situations where it doesn't work, but I haven't encountered any yet. So, you could give this a try.

[andygaunt]

Dan will probably murder me for this, but what the hey! .

Can you send your files off list to me and will look at for you.

[spragueg]

quote:

---

Originally posted by CaptKurt:

Here is a simple fix.

If any particular Job record is related to a single particular Campaign record and if any particular Stage record is related to a single particular Campaign record, then really each Stage record is related to a Job record.

Create a "CampaignID" field in the Stage file and have it lookup the "CampaignID" field from it's parent Job record.

P.S. This will work regardless of whether the field is a single ID or multi-key field.

Then one relationship from the Campaign file to the Stage file via the CampaignID field.

Easy as pie!

---

If you look at my first post you will see that some jobs need to appear in more than one campaign.

[spragueg]

quote:

---

Originally posted by Russell Baker:

Given that your Jobs in file B will have a key field link to the Campaigns in file A, and also key links to the Stages in file C...

Why don't you do your report printing from the Jobs(: file, sorted by sub-summaries on Capmaigns(A) and showing a portal of the stages from Stages©.

This goes against my principles of always printing from the file that holds all the data - but if you are doing a one-page-per Camapign or Job print, there should be plenty of room on the page for a decent sized Stages portal. Of course, you won't be able to slide this portal...

Russ Baker

Canberra, Australia

The Land of TWOGM

---

This is what I thought of doing first but I can't because some jobs need to appear in more than one campaign. Which is not possible from the jobs file. It is possible however from the campaigns file using portals to show each campaigns jobs.

So, I have already solved that problem. Now I just need to get all the steps for each job to show up in the portal row.

[spragueg]

quote:

---

Originally posted by Russell Baker:

Given that your Jobs in file B will have a key field link to the Campaigns in file A, and also key links to the Stages in file C...

Why don't you do your report printing from the Jobs(: file, sorted by sub-summaries on Capmaigns(A) and showing a portal of the stages from Stages©.

This goes against my principles of always printing from the file that holds all the data - but if you are doing a one-page-per Camapign or Job print, there should be plenty of room on the page for a decent sized Stages portal. Of course, you won't be able to slide this portal...

Russ Baker

Canberra, Australia

The Land of TWOGM

---

This is what I thought of doing first but I can't because some jobs need to appear in more than one campaign. Which is not possible from the jobs file. It is possible however from the campaigns file using portals to show each campaigns jobs.

So, I have already solved that problem. Now I just need to get all the steps for each job to show up in the portal row.