combining same table occurences in portal

[Bjohn]

I’m sure this has been asked and answered before, but I cannot find an answer.

 

I have 4 tables: 1)Contacts, 2)Contacts2, 3)Programs, and 4)Projects.

 

Contacts & Programs are connected by Fields: C_program_name & PM_program_name. In ProgramsLayout, I have a script (button) that opens ContactsLayout, Go to Field [C_program_name], Set Field [$$PM_program_name]. Thus connecting the 2.

 

Contacts2 & Projects are connected by Fields: C_project_number & PJ_project_number. In ProjectsLayout, I have a script (button) that opens ContactsLayout, Go to Field [C_project_number], Set Field [$$PJ_project_number]. Thus connecting the 2.

 

I want a Portal in ProjectsLayout and ProgramsLayout that lists the contacts from both. In other words, when I open ProjectsLayout, I would like to see contact names that are associated with Projects Table, as well as Programs Table.

[AHunter3]

For a portal aimed at a table occurrence of Contacts to display all contacts related to either Projects or Programs, you need to have that table occurrrence connected to your local layout's table occurrrence by both C_program_name and by C_project_number.

 

You have not explained as of yet how Programs and Projects are related to each other. I need to know that in order to explain how you would "reach for" the value of the related C_project_number when you're on a Programs layout, or "reach for" the value of the related C_program_name when you're on a Projects layout, but if that's clear to you you can proceed without explaining it to me.

 

Let's say you're on a Programs layout first. You create a calculation field of result type "text" in the Programs table, defined as:

 

C_program_name & "¶" &

RelatedProjects::C_project_number

 

 

... where "RelatedProjects" refers to however Projects table is indeed related to Programs. Let's call this field "Prog and Proj"

 

Then you create a calcfield in the Contacts table, also result type "text", defined as

 

PM_program_name & "¶" &

PJ_Project_number

 

This field, here in Contacts, will be indexed. Let's call his field "Prog and Proj Key"

 

 

Now you make a new table occurrrence of Contacts, let's call it ProgProjContacts1, and you link it to Programs like so:

 

 

Programs::Prog and Proj = ProgProjContacts1::Prog and Proj Key

 

 

now a portal to ProgProjContacts on the Programs layout will show contacts of either the program or the project.

 

Repeat the "Prog and Proj" calc field in the Projects table and make yet another table occurrence of Contacts ("ProgProjContacts2") and repeat the relationship and you can put a similar portal on the Projects layout.

 

 

 

NOTE: these instructons assume that no Project's project number would be the same as any Program's program name. Were that not the case, you'd add a prefix such as "programs_" to all three calcfields in front of program name and a different prefis such as "projects_" in front of project numbers.

[Bjohn]

AHunter3,

Thank you much for your reply.

I have Contacts & Programs related by _contactNumber (a unique number) and Contacts & Projects related by _contactNumber. Therefore, Programs and Projects are related by _contactNumber.