As you've offered, please supply more info on your tables and relationships as well as on the calculations/summary fields you defined to compute your totals.
Ok...let's start with this and see how it goes. I'm new to posting pictures so if there's a better way, let me know!
Links result in 404 "URL not found" errors.
Ok...fixed the links...please try again!
Both links show the same relationship graph. And that image doesn't show what key fields are being matched in the child table occurrences. Also, if you have multiple table occurrences pointing to the same data source table, it can help to color code them with matching colors.
Let's see if we can narrow the focus a bit. On which box in your relationship graph have you based your layout?
Is this a list view layout, a portal or some other design.
If this shows us a portal, on which box in the graph is it based? Did you set up a portal filter for it?
The field at the bottom that appears to compute the correct total, how is it defined?
Fixed the link to the Tables graph and am working on answering your other questions. Appreciate your assistance immensely.
1) The layout is based on the "Budget Entry" box on the relationship graph.
2) This is a list view layout. The data in the white area at the bottom of the layout resides in a "footer"
3) The field at the bottom that computes the correct total is a SUMMARY field from the "VRSD FY11 Actual Labor 3" table occurence. That table is populated with "Actual" data that is provided to me by our finance people.
And the link to that TO is by Project number so the total correctly totals all the work logged on a given project.
I gather each record in budget entry represents a specific employee and how many hours they were budgeted to need on the project. Thus, the difference between the total at the bottom and the actual hours listed represents work done by employees that did not have any time for the given project in their budget but worked on the project anyway.
Simply creating one record in Budget Entry for every employee with the needed project number would fill in the gaps here.
One way to script that might be to enable "allow creation of record via this relationship" for the Budget entry side of the BudgetEntry to VRSD FY11 Actual Labor relationship.
Then a script run from VRSD FY11 Actual Labor:
Show all records
go to record [first]
Set Field [Budget Entry::employee ID# ; VRSD FY11 Actual Labor::Emp#]
Go To Record [ next ; exit after last]
Would fill in the missing records. (Existing records are left unchanged by this script.)
There are a lot of issues with your current database design. A TO for a specific fiscal year doesn't look like a good idea (Will you add all new TO's and layouts for FY12?) just to spot one issue. The fact that employees can log time in the actual labor table without having a matching budget record is another.
I recommend you sit down with an exprerienced database consultant who can analyze your business model and database to workout a system that will work for you while avoiding such issues as you move forward.
Sometimes a night's sleep brings a new perspective...
We're looking at this all backwards. A simple layout redesign will avoid all the above scripting that I suggested yesterday.
Base the report of your original screen shot on VRSD FY11 Actual Labor and add the budget data as related fields from Budget Entry. Your totals at the bottom can now be defined as summary fields in the actual labor table.
This will show all your labor entries for a given project without omitting those individuals who did not have a budget entry record by worked on the project anyway.
Thanks for you assistance on this. I will try your last suggestion and see how it goes and let you know!
Ok, not sure if I followed your direction correctly, but when I base the "report" on VRSD FY11 Actual Labor table, I do in fact get all of my actual data, but now I have the same (but opposite) problem with the hours budgeted. The report now shows all of ACTUAl DATA (good!) but you can't tell from the same report who was originally budgeted for this project.
See the screenshot:
I may have to try your original solution, which was "one entry in budget entry for every employee" using the script....
That shouldn't be needed. I get a 404 error when I click the link.
ok...fixed the link.
OK, you look like you're almost there. It looks like these columns: Job Title and Name draw their data from Personnell Budget Data. Since there's no matching record in Budget Entry for these "not budgeted" records, the names don't show. The solution is to make an extra table occurrence of "Personnell Budget Data". Select it and then click the button with two green plus signs to make an extra Occurrence Box for this table. Link it directly to VRSD FY11 Actual Labor by Emp# and replace these fields on your layouts with matching fields taken from this new table occurrence.
That should do it.