I wanted to begin my question by asking about the fundamentals behind it. Hopefully I can then come to the answers for my specific situation later and really learn something.
What I'm wanting to do is ultimately compare multiple points of data from multiple tables on one portal. I want to understand what some of the fundamentals are behind doing this. What do I need to remember in regards to adding Table Occurrences, making specific relationships among the TO's, linking specific match fields in the edit relationship dialog box?
I realize this is vague, but if anyone has thoughts on those fundamentals, I would really appreciate it.
If my question really is too general and broad, I'll now get a little more specific with my application:
With my database I want to:
- Create Job records for my clients (Jobs & Clients tables)
- Create an estimate of time for that Job (portal line items populated with Tasks that come from the Tasks table, with the portal sending records to Job Sheet Task Items table)
- When work is done on this job, I want to create a Time Sheet each day showing billable work. (Time Sheet is created in the context of the Time Sheet table, with portal records populated and sent to the Time Sheet Task Items table)
- On the original Estimate portal, I want to be able to compare the actual time entered on the Time Sheet with the estimated time. This is the part of the whole thing that is giving me trouble. That's why it's in bold.
- I want to create an Invoice that bills the Client for the actual time spent on a Job. (Invoice is created in the Invoices table, with a portal sending the line items to an Invoice Line Items table
As I mentioned, I'm having trouble comparing estimated and actual time on one portal.
I have attached shots of my relationship graph, table list, and the Job layout that shows the portal I ultimately want to have that compares the actual and estimated time.
Relationship Graph Notes
What you see on my graph in green are what I consider my core tables, in yellow are my join tables, and in blue are the 3 TO's of my Tasks table. The Tasks are individual records that represent the estimate items, time sheet items, and billable invoice items.
All has been fine up until this point of comparing estimated time and actual time, and the problem comes from me not being able to figure out how to link a Job # with the Task associated with it, and ultimately how much time I estimated and actually spent. It's that link between the Job # and the Task # that isn't there.
I feel like I'm close but just need some help figuring this bit out. Do I need to rethink the Tasks table? Do I need to do away with the 3 Tasks TO's and go a different route?
Job Sheet Layout
On the Job layout shown in the picture, I'm wanting to ultimately have the information that's on the upper right portal shown in the portal on the Estimates tab. Right now I'm having to separate the two portals, but I'd rather have them together. Whatever problem I'm having is what's keeping me from doing that.
Help would be very much appreciated. Ask any and all questions you want. Thanks so much!