Oh, and "Proof" and "Complete" are on a variable list for the field job status (as a radio button).
It would seem you produce two different items for which you can bill your customers. The hours worked on their project and the proofs you produce along the way. Is that correct? Or are "proofs" logged as separate entries in the work log and you are billing for work on a "proof" as a separate item from work on the project?
Well, the way I have it set up is that each entry is separate. I could not find a way to have one work log to "check in and out" several times. That might have been an easier solution? I'm not sure. Each project gets added up for the total bill according to the amount of time on both proofs and one complete. I charge for every time I "touch" the project. Whatever time I spend on it gets rounded up into 15 minute increments. So I could work 2 min on one proof(15), then 17 on another (30),etc. until I get to the complete which is also billable time. Then all the billable time gets added up to total billable time and turned into the price charged. I'm not sure how to identify several proofs and one complete without logging them as separate entities. (For each work log I have a date, stop, and start button, and all of the calculations are done). So far the database works fine with the calculations and reporting gets separated to all those identified by the "lawson #". I just can't figure out how to do the separate reports.
I could be making this harder on myself...
I'm trying to verify what you need here by making sure that I understand your procedures. When you bill for a "proof" is this separate from the time you worked on it or is it just that you want to show the hours worked on a proof as a separate item on the invoice?
As a consultant, I have a "quick and dirty" little database I use for generating invoices for projects I complete. I use a portal to a table I call TimeEntries that relates by ProjectId to my projects table. Each time I log in and out of a project, I use a portal to TimeEntries on my Projects label to create a new record in this portal.
My invoice is printed from a layout based on the TimeEntries table. I pull up the TimeEntries for a given project and then use summary fields to compute total hours and a total to bill the client. Using that approach, you can add additional recors to TimeEntries to bill for proofs and you can add as many as you need. You can either use logged time to bill for work on each proof or you can add additional number fields to log charges for the proof if it is not time related. I have a text field in this table where I log what kind of work I was doing on the project. I could enter "proof" in this field and sort my records on my invoice to pull them into a separate section of the report with their own sub summary parts if I needed to do that.
Sorry I'm being so confusing, it's probably because I'm so confused! :) Maybe it'll be easier if I show what kind of report setup I need... And I could have totally setup my db wrong to accommodate it.
Date Range: 4/27/11 to 5/26/11 - would need to include all modifications for each, not date range dependent, and would only show those that end with a complete status.
Company Name Cost Center Lawson No Form Description
Bleck, Inc. 12345 6234 Form 12000
Modifications: 1/23/11 Time In: 8:45 AM Time Out: 9:32 Total Time: 0:47 Billable Time: 0:60 Status: Proof
5/4/11 Time In:__ Time Out:__ Total Time:__ Billable Time__ Status: Complete
Total Billable Time: 0:75 Bill: $75.00
12462 5801 Form child seat belts
Modifications: 1/23/11 Time In: ___ Time Out: ___ Total Time: ___ Billable Time: 0:60 Status: Proof
3/3/11 Time In: ___ Time Out: ___ Total Time: ___ Billable Time: 0:60 Status: Proof
5/4/11 Time In: ___ Time Out: ___ Total Time: ___ Billable Time: 0:60 Status: Complete
Total Billable Time: Bill: $75.00
Total Bill: $50000.00
Would be the exact same setup but would show a past 3 month date range with those forms that do not end in a complete status - proofs outstanding. I have to show the exact minutes I spent each time I touched it. Round those minutes up by 15 min increments, and show each time I proofed it up until completion.
Those forms that have not been completed in the date range I'm billing, cannot be included in the total bill - can't charge for them until they have a line that says complete.
Do you know how to create a summary report with sub summary parts?
If you know how to do that, the main issue here appears to be performing a find on the WorkLog that pulls up the correct records. Once you've done that, you can sort the records to group them by project and thus your sub summary parts can compute totals for each.
When you perform a find, you can include criteria in related records, so your find can limit the work log records to only the work done on a project where a status field identifies that project as complete. You'll need to include some additional criteria so that you don't continue to list worklog records for the same project once it has been paid. That, can be yet another value in your status field or a value entered in a different status field.
Report 2 can use the same layout, but with different find criteria to find worklog records of projects that are not complete, and that fall in a specified date range.
PS. Your screen shot shows "projects" and "products". I don't know if each "form" is a "product" or a "Project".
Ok. I will try this suggestion and see if it will work for my info. I learned on access, minimally, and have only been using FMp for about 5 days... So, thanks so much for helping.
My products are the actual forms/posters/what have you. But most of these are for hospitals, so they update every few years, but keep the same lawson, hospital id. The projects are the times I modified those products. (i.e. each product has several projects, but a project can have only one product). There are millions of products and I don't want to have to re-enter all their information 3 years from now when they want me to modify again... Does that make sense?
If you are billing for products rather than for projects that are complete, you may need to modify your relationships so that it is easier to tell which time entries are for a product that is complete.
Here's a tutorial on summary reports that you may find helpful:
It will demonstrate how to use summary fields with sub summary parts and also shares a few "tricks of the trade" that are sometimes very useful when working with summary reports.