6 Replies Latest reply on Oct 13, 2015 7:36 AM by DavidJondreau

    Showing fields from multiple tables on one portal


      Hello everyone,


      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.


      RelationshipGraph Screenshot.JPG


      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?


      Table List Screenshot.JPG

      Table List

      Job Sheet Screenshot.JPG

      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!

        • 1. Re: Showing fields from multiple tables on one portal

          If you have 1 task for 1 job for one client - this is a line in a estimate portal - and for this specific task your employees enter 3 different entries in a timesheet table, each having the same taskID, jobID and clientID, just build composite keys.


          in estimate you have a calculated key, ClientID & "-" JobID & "-" & TaskID.


          in Timesheet you have the same key, but you find it in many records, 2 from John and one from Joe, each having a total time, a date and a worker's ID.


          Define a relationship between estimates and timesheets, based upon this composite key.


          in estimate you define a calc field = Sum(estimates_timesheets::TimeTaken) and you display it near the Est hours field you already have, don't see the problem.

          • 2. Re: Showing fields from multiple tables on one portal

            There will be multiple tasks for each job. Those will be the line items that make up the estimated time spent on the job.


            At this time there is no Estimates table with which to define a relationship.


            I'm unfamiliar with the concept of a "composite key" so I don't quite understand that.


            I'm wanting to compare the estimated time of each task line item, not just the total time. I want to be able to estimate the time that we will spend on the design, copywriting, proofing, etc. for a job, and then when time sheet entries are made for those specific tasks, those actual times spent will show up in that estimate tab portal. That way I can always be watching the time for each task item.

            • 3. Re: Showing fields from multiple tables on one portal

              How about relating Job Sheet Task Items with Timesheet Task Items? Use a new TO of Job Sheet Task Items, based the relationship on TaskID and JobID. Then in Timesheet Tasks, you'll be able to see the related Job Sheet tasks.


              It's unclear if this would be a one to one or one to many. If it's the latter You could create a new field that adds up the hours from Job Sheets, either in a calculated field or in a scripted process. If it's the former, you can simply display the estimated hours on the timesheet portal.

              • 4. Re: Showing fields from multiple tables on one portal



                I'm going to run some more tests, but I think you've gotten me where I need to be. I actually created another TO of Timesheet Task Items and then related it to Job Sheet Task Items the way you suggested. I think that was my missing piece.


                Once I confirm that this is correct I'll let you know. In the meantime, thanks so much for the help!

                • 5. Re: Showing fields from multiple tables on one portal



                  All signs point to this being the correct step. The key was linking the second TO of the Timesheet Task Items table to the original Job Sheet Task Items table with the relationship connecting the fk_JobID and fk_TaskID of each table. Then I added the time entry field from the new TO to my portal and it works just like I need it to. With this relationship I can also compare the two numbers with conditional formatting so I know immediately when actual time spent is more than was budgeted.


                  Thanks again!