Creating multiple one-to-many relationships

[nareyramos]

Hi everyone,

 

I have a database with the following tables:

 

Table 1: Projects (ProjectID, EstimateID, Description, ProjResp)

Table 2: Estimates (Project ID, EstimateID, ActivityID, CreationDate, LastModified, TotalCost)

Table 3: Activities (EstimateID, ActivityID, ActDescription, ActResponsible, ActCost, Spent, Balance)

 

The way I have my layouts designed, I have a layout #1 (main menu) where you input/view the info for every each project (from table 1 - Projects), but it also has a portal that shows the estimates (from table 2 - Estimates) that have been done for that very same project. Then I have another layout (from table 3 - Activities) with the activities and all activity related data. I know that each project can have many estimates, and each estimate can have many activities. I just don't know how to make it work. I have also created a button in the portal rows (from Estimates) in Layout #1 to be able to go ONLY to the activities that are part of a specific estimate, but I just can't make it work. Can somebody shed some light?

 

NR

[eos]

 I just don't know how to make it work.

 

If this is a structural question: based on that statement …

 

each project can have many estimates, and each estimate can have many activities.

 

 … you need to relate your table as

 

Projects --< Estimates --< Activities

 

using the respective primary and foreign key; and …

 

Table 2: Estimates (Project ID, EstimateID, ActivityID, CreationDate, LastModified, TotalCost)

 

… you don't need an activityID here; since Estimates is the parent, and Activities is the child table, Activities needs a foreign Estimate key (which you already have).

 

If this is a workflow question: you could e.g. create a new field in your Projects table, in which you capture (via script) the primary ID of an Estimate record that you click within the portal on layout #1.

 

Then create a new relationship to Activities like

 

Projects::selectedEstimateID = Activities_forSelectedEstimate::estimateForeignKey

 

and put a portal into Activities_forSelectedEstimate on your layout #1. Now you can see all the Activities for the selected Estimate (you could also select multiple Estimate IDs and see all related Activities for all of them, if you use a text field for the selection field and add/remove clicked IDs). Use Conditional Formatting to indicate which Estimate(s) is/are selected.

 

This would let you work with almost all aspects and related records of a Project on a single layout. If you'd rather spread your work across several layouts …

 

 I have also created a button in the portal rows (from Estimates) in Layout #1 to be able to go ONLY to the activities that are part of a specific estimate, but I just can't make it work. Can somebody shed some light?

 

… then have a look at the Go to Related Record script step.

[nareyramos]

Thanks for your reply EOS. I get the first part of your explanation. As for the second part, I think that you went a step further by explaining how to show the activities associated to an estimate on my layout #1. My first problem is that I can't make my Estimate portal on layout #1 work correctly. This portal includes the fields EstimateID, CreationDate, LastModified and TotalCost. Here is what I want to be able to do: I want to be able to create an estimate from this Estimate portal in my Projects table; then click on it (row button) and go to the Activities layout and start creating activities for that particular estimate. The estimate total amount (Summary Field TotalCost) should then appear in the portal in my layout #1. Also, once an estimate is created, I want to be able to click on that estimate in my layout #1 portal, go to Activities and see only those activities (records) related to that Estimate. Probably I should upload something so that you can see for yourself. Let me know if this works better. I'd appreciate it if you could give me a hand on this. Thanks again.

 

NR

[eos]

[…]

Probably I should upload something so that you can see for yourself. Let me know if this works better.

 

Yes, please do that.