You should use a single table and then manipulate the found set with find and sort actions or use a summary report to sort and group your records. I'd need to know more detail on your table(s) before I could give a more detailed response on how you can do this.
Here's the DB structure. Company is a mother table. It's direct descendants are a Projects table and the General Events table (among others: contacts, documents, etc). Dependent to the Projects table is the Project Events table. I don't see how Project Events and General Events could be one table based on this structure and the associated relationships.
I did try one table but couldn't get Event portals to show the data correctly based on the context. In Company view I want 2 separate portals for General and Project Events. In a Project View the Events portal should show just events related to that project.
As always, the devil is in the details. I can't tell from that description whether you can use a single table for General and Project events or not.
What fields do you have defined in each of these two tables?
I don't see how Project Events and General Events could be one table based on this structure and the associated relationships..
If that's all the difference between the two types of events, there's no reason why they couldn't be in the same table - and if you want to be able to search events and show results of any type, they SHOULD be in the same table.
All you need is two foreign keys in the Events table - one for CompanyID, and one for ProjectID - and two occurrences of the same table for the two relationships.
I tried this method before. I couldn't get it to work as the 2nd foreign key in Events can't be Company ID because it's in all Event records. After thinking it through again I realized that I needed a second Company ID, one unique to General Events.
So I created a second Company ID field ('Company ID General Activity') in Events with an If/Then calculation: If(Event Category="General"; Company ID;"") where if Category=General, the Company ID shows in the second Company ID field, otherwise if the category is Project it's blank.
I used the calculated Company ID as the key between General Events and Company ID in the Events General table and all worked out correctly.
Thanks for the inspiration to try that method again!