Sum used in this fashion is controlled by the relationship involved. Your portal's relationship matches a given project to all the related Line Items records. Thus, Sum ( Line Items::Hours Billed ) gives you the total of all hours billed for the current project record like you wanted. To get a total for the current project, but just for a selected month, you'll need an additional relationship that matches only to those records. You can use separate fields for month and year, but you don't really need to.
Define a calculation field, cMonth in Line Items as: DateField - day ( DateField ) + 1 and select "Date" as the calculation's return type. This calculation returns the date for the first day of the month for all dates from that month.
Define a date field, SelectedMonth, in Projects and give it the same expression for as an auto-entered calculation and clear the "do not replace existing values..." option.
Now add a new relationship:
Projects::ProjectID = LineItemsByMonth::ProjectID AND
Projects::SelectedMonth = LineItemsByMonth::cMonth
LinItemsByMonth is a new table occurrence of Line Items, created by clicking the box in Manage | Database | Relationships and then clicking the button with two green plus signs.
Sum ( LineItemsByMonth::Hours Billed )
Will now compute the desired monthly amount and you can format SelectedMonth with a pop up calendar if you wish. Any date you select or enter will automatically update to the first of the month when you exit the field.
Note: you can use the same method as above for separate fields for month and year if you want, you just need two calculation fields to match in Line Items and both pairs of fields will be included in your relationship.
Note, you can also use a layout based on Line Items with summary fields and sub summary parts to give you a report of selected line items, grouped by month with both sub totals for each month and also a grand total. This method does not require any additional relationships and table occurrences.
My database is set up as "Project" linked to the "Time Billing" linked to the "Line Items". "Project" and "Time Billed" are linked by the "Project ID", and "Time Billing" and "Line Items" are linked by the "Billing ID". How would I adjust this to make the relationships work?
Everything appears to work, but when I put in a date to show the time worked for that month, nothing appears in the field for the Sum.
I take it you have this:
Projects::ProjectID = TimeBilling::ProjectID
TimeBilling::BillingID = LineItems::BillingID
It depends on where the date is that you want to filter on. If it's a date in TimeBilling (the date of the bill), you can use:
Projects::ProjectID = TimeBillingByMonth::ProjectID AND
Projects::SelectedMonth = TimeBillingByMonth::cMonth
TimeBillingByMonth::BillingID = LineItemsByMonth::BillingID
And Sum is still defined in projects.
If the date is a date in LineItems (date work is logged), then you'd need:
TimeBilling::BillingID = LineItemsByMonth::BillingID AND
TimeBilling::gSelectedMonth = LineItemsByMonth::cMonth
gSelectedMonth is a field with global storage specified and is defined in the TimeBilling table, but you can place it on your projects layout.
All of this added complexity argues for the other option: A summary report based on LineItems.
I think I almost have it working (with your amazing help, of course!)!
The gSelectMonth doesn't automatically change to the first day of the month, so the final Sum only shows up if I manually select the first day of the month. It works for the calculations in cMonth, but not the date for gSelectMonth. Is there a way to get it to automatically change to the first day of the month, no matter what day in the month is chosen?
Nevermind, I figured it out (just had the wrong field specified).
Thank you so much!!!!!!!
You are awesome, thank you so much!!!!!!
Ok, now is there a way to "sum" all of the billing time for this found set? I have the total amount of hours that will be billed for the selected month for each project for this client, but now I want to add the totals of this set together for a grand total of hours for this client's projects for the month. How do I "sum" the field of a found set?
To sum the field for all records in your found set use a summary field defined to compute the "total of" the field you want to sum.
You may want to investigate creating a summary report with summary fields and sub summary parts created on a layout based on LineItems. You can add fields from Time Billing, Projects and a related Clients table as needed to provide all the info needed for the report, but you can use summary fields and sub summary parts with sorting to get totals and sub totals broken down in quite a number of different ways.
Hi, Can u send a small sample for this problem? thank you.