Discussions

Expand all | Collapse all

summary for multiple tables

  • 1.  summary for multiple tables

    Posted 05-12-2017 05:45
    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. 


  • 2.  RE: summary for multiple tables

    Posted 05-12-2017 05:56
    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.


  • 3.  RE: summary for multiple tables

    Posted 05-12-2017 06:02
    I'm CA time so 1 hour back from you. When are you available?


  • 4.  RE: summary for multiple tables

    Posted 05-12-2017 06:07
    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.


  • 5.  RE: summary for multiple tables

    Posted 05-12-2017 06:09
    I'll email you once I get in tomorrow, around 10 ish (11 your time), Traffic :-)