summary for multiple tables

  • 0
  • 2
  • Question
  • Updated 2 years ago
  • Answered
Sorry in advance for being long. Working with big client that rhymes with "foogle" ;-)
I Have over 75 tables but need help with only 4. :-)  If someone could help I am available for a video chat, just let me know. 

I have the following Tables currently:

Project
Jobs
Time entry
Rate card

Projects<Jobs<time entry<Rate card

Rate card:
Emp Name: List of users
Rate: Free numeric

Time entry: 
Job Name: Drop down list from related Jobs table Job Name (ref proxy)
Project Name: Auto pulled from related Job (entered above) Job Name (Text lookup)
Date: date text
Emp Name: Drop down list from related Rate Card table (ref proxy)
Rate:  Auto pulled from related Name (entered above) Rate Card Rate (numeric lookup)
Hours: free numeric
Total Time entry $$: formula Rate * Hours

Jobs:
Project Name: Drop down list from related Project table Project Name (ref proxy)
Job Name: Free text
Total Job Actual $$: numeric summary from related Job Name


Projects:
Project name: Free text
Table of linked Jobs
Total Project Actual $$: numeric summary from related Project Name

_________________________________________________________________________

My ask: Currently I can get a Project summary, but when I click on the hyperlink it takes me to the jobs record that comprise that total. I would like it to take me to the time entry records that comprise that total. I need this because I need to build a Monthly Manager Actual $$ approval table. Each manager has 20-200 Projects tagged to them so I need to build a separate to monitor the BVA and approval money spent each month.

I have tried to make a relationship from Project to Time Entry, however the Project name is being auto-pulled based on the Job. I don't want to make the Project a drop-down selection as that will open the door for data to be inconsistent. I tried to set dynamic rule to change the reference proxy but no luck. I think I might have to make a table "Project_to_Time Entry" and have Project<Project_to_Time Entry & Time Entry<Project_to_Time Entry. 
Photo of David Cowles

David Cowles

  • 150 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 2
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I think you should be able to accomplish this by (1) creating the relationship you spoke of and (2) modifying the URL to prefill the related project AND job at the same time.

You wont want the lookup, that that wont give you the reporting you need.

Where are you located? Time Zone?  I'm in MDT, USA.
Photo of David Cowles

David Cowles

  • 150 Points 100 badge 2x thumb
I'm CA time so 1 hour back from you. When are you available?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
So we are both up late, lets plan tomorrow.  Email me with your best time tomorrow, and I'll send a conference invite.  I'll be in the office at 8.

matthew@cirrusops.com

p.s. My brother used to work at the Mtn View 'foogle' location.
Photo of David Cowles

David Cowles

  • 150 Points 100 badge 2x thumb
I'll email you once I get in tomorrow, around 10 ish (11 your time), Traffic :-)