8 Replies Latest reply on May 10, 2011 10:38 AM by philmodjunk

    Sorting reports with multiple factors, i.e. spec. date range, adjusted by variable.

    JennySmith

      Title

      Sorting reports with multiple factors, i.e. spec. date range, adjusted by variable.

      Post

      This will be a long explanation.

      I am a graphic designer and I'm creating a database to manage my time and billing.

      My database setup is in the picture. Basically I have many customers who each have their own products/forms (no two customers have the same product). Each of my projects can only include one product (each charged for the time I spent on them). I log in and out of them on the worklog repeatedly (which makes several records for each) and mark them as proof or completed. Could have up to five proofs beginning in January and not completed until May, for example.

      I need to have a billing report every month (which is not the same cycle usually) that will show all forms that are completed and all of the times i have gone in and worked on them (i.e. their proof worklogs). On the report, I cannot include any forms that have not achieved a complete status, as I can't bill for the proofs until the job is complete. I do bill for the proofs once it is complete. So I need a report where I can put in a date range, separate by completed worklogs + any proof logs associated with the completed form#, and excluding any proofs that do not have a complete worklog. I also need a report that shows all proof logs that do not have a complete log, exluding the proofs that do. So that I can have an "outstanding" proof report, to remind customers if their job has been in the system too long.

      This might not make any sense, but if anyone gets it and has any thoughts please let me know. (I am also crap at scripting).

      Screen_shot_2011-05-09_at_4.55.15_PM.png

        • 1. Re: Sorting reports with multiple factors, i.e. spec. date range, adjusted by variable.
          JennySmith

          Oh, and "Proof" and "Complete" are on a variable list for the field job status (as a radio button).

          • 2. Re: Sorting reports with multiple factors, i.e. spec. date range, adjusted by variable.
            philmodjunk

            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?

            • 3. Re: Sorting reports with multiple factors, i.e. spec. date range, adjusted by variable.
              JennySmith

              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...

              • 4. Re: Sorting reports with multiple factors, i.e. spec. date range, adjusted by variable.
                philmodjunk

                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.

                • 5. Re: Sorting reports with multiple factors, i.e. spec. date range, adjusted by variable.
                  JennySmith

                  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.

                  Report 1:

                  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

                  Report 2:

                  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.

                  • 6. Re: Sorting reports with multiple factors, i.e. spec. date range, adjusted by variable.
                    philmodjunk

                    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".

                    • 7. Re: Sorting reports with multiple factors, i.e. spec. date range, adjusted by variable.
                      JennySmith

                      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?

                      • 8. Re: Sorting reports with multiple factors, i.e. spec. date range, adjusted by variable.
                        philmodjunk

                        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:

                        Creating Filemaker Pro summary reports--Tutorial

                        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.