I would like to be able to connect two similar tables together for the purpose of viewing the data in a common portal.

Background: I have a main table that houses customer data. (i.e. Name, Address, Phone Number, Primary Key)

There is a second table that houses service call data. (1.e. Date of call, type of call, work performed, tech, Foreign Key) This table connects to the main table via Primary key from Main table.

There is a third table that houses Weekly maintenance data. (i.e. Date of maintenance, Type of maintenance, tech, Foreign Key) This table also ties back to the main table via the same primary key.

I would like to link the two tables together via the same primary/foreign key from the Main table. I would then like to view the data through a portal on the Main tables layout that would list the services in chronological order.

I believe this could be accomplished through a possible fourth table. I'm not sure if I need to use a global value to link the items temporally. I don't want to populate the possible fourth table permanently for storage space reasons.

Question.pdf

I think I would handle it just a bit differently. I would separate the two types of Services into "daily" and "weekly" - so add that category.

Then - there are only two tables and you can either use a portal filter or global relationship (add "gdaily" and "gweekly" to filter the records.

I would like to be able to connect two similar tables together for the purpose of viewing the data in a common portal.

Background: I have a main table that houses customer data. (i.e. Name, Address, Phone Number, Primary Key)

There is a second table that houses service call data. (1.e. Date of call, type of call, work performed, tech, Foreign Key) This table connects to the main table via Primary key from Main table.

There is a third table that houses Weekly maintenance data. (i.e. Date of maintenance, Type of maintenance, tech, Foreign Key) This table also ties back to the main table via the same primary key.

I would like to link the two tables together via the same primary/foreign key from the Main table. I would then like to view the data through a portal on the Main tables layout that would list the services in chronological order.

Well the way you have tables 1, 2 and 3 you could create a tab set on the company record with 3 tabs. First would have the company info. 2nd would have a portal showing all service calls and the 3rd would show all weekly maintenance for that company. It's easy to sort portals. I don't think you need a 4th table or the 2nd set of the 2nd and 3rd table unless I'm missing something.

Well the way you have tables 1, 2 and 3 you could create a tab set on the company record with 3 tabs. First would have the company info. 2nd would have a portal showing all service calls and the 3rd would show all weekly maintenance for that company. It's easy to sort portals. I don't think you need a 4th table or the 2nd set of the 2nd and 3rd table unless I'm missing something.

I have exactly that already in place, I have the main parent table containing the customers data that links to 2 daughter tables containing the service and weekly info. I do have 2 separate portals that allow me to view the content related to the parent record.

This works good when we need to view the individual table data. However, there are times when we need to quickly access both tables to see what dates we were at a particular residence. It is very helpful when talking to customers on the phone. I am just trying to temporarily view the data at a moments notice. I could combine the data in a third daughter table permanently, but that would waste memory space.

A portal cannot show records from more than one table. To view all services in the same portal, they would need to be in the same table.

I am not sure I get your point about wasting memory space. If the two tables are radically different, you could use a super-type Visits table for the fields that are common to both, and two sub-type tables for fields that are specific to each type. However, this takes some doing to set up and maintain properly - e.g. making sure a new record is created in both the super-type table and one of the sub-types tables. This is not something I would rush into.

A portal can in fact show data from any number of sources, if it is based on a virtual list utility table. This is one of the basic uses of the virtual list technique.

I think what you are saying is that clicking back and forth between the 2 tabs is cumbersome. So why not put both portals below the company information. Or have one table that holds both service calls and weekly maintenance and indicate in a Type field which type of activity it is ie) service, maintenance. Then you could see all activity in a list ordered by date. You could also add a filter for just Service or just Maintenance.

"I could combine the data in a third daughter table permanently, but that would waste memory space."

So what?

You have real costs of time to respond effectively to your calls. What level of waste do you imagine is involved here?

And, per my previous note, there are easy and efficient ways to handle this.