Invoicing with Time Billing template Database?

[Nicolas]

Hi, I've set up a Client database linked to the TimeBilling Template that comes with FM 6 templates.

My problem is to create an invoicing layout that uses the repeated Billing ID of each "department"for every customer and give me a single result per department.

Example:

Customer "Frank" has 5 jobs billed on different dates by the Carpentry department,(Carpentry ID=12)

2 jobs by the paints department (Paints ID=5)

3 jobs by the engineering department ( eng. depart. ID=2)

Is there a way to have an Invoice showing the "customer" each "Department's Total" and a summary of all the above "Departments"

My Customer database is quite simple and has customer ID# field.

Can someone help me find the link to the above example.

Thanks in advance.

Nicolas

[BobWeaver]

Summary fields and sub-summary parts on your report layout. You can delete the body part of the layout because you don't need it if you're not printing individual records.

[Nicolas]

Thanks for your reply.

I don't really see what you mean. What I ask for is the way to link the whole thing together and ask for the way the relationship is writen with the field values, calculations etc...

By the way I'm a Newbee

[BobWeaver]

Okay, I went through the template and realized that the department field is in the main file which makes it difficult to do what you want. There should be a department field in the line items file as well. Here's what to do:

1. Go into the line items file and create a new text field called department. Go into the field options, and under the Auto-Enter tab select Looked-up Value. In the lookup dialog, go to the relationship pop-up menu in the upper right, and select "Time Billing," and in the field list, select "::Department." Then un-check "Don't copy contents if empty," and then click OK. What this does, is automatically copy the department information from the main file into the line items file whenever a new record is created.

2. Now you can modify the report to summarize by department. First you need to change the sort order. In the line items file, select sort from the Record menu. If the report script has previously been run, then you will see the sort order as:

Customer

Project

If not, then set it up that way, Then, add Department to the bottom of the list, so that you have:

Customer

Project

Department

and then click sort.

3. Now go to the scripts menu, and open Scriptmaker. Select "Customer Report" and click the Edit button. The script window will open. Click the OK button at the bottom right. Another dialog will come up asking if you want to keep or replace information stored with the script. For Sort Order, click "Replace" and then click the OK button. The script will now have the new sort order which includes the department field.

4. Go into layout mode and select the Customer Report layout. You will see sidebars on the left labeled header, Sub-s..., S.., and F. The bottom one is the footer, and the two above it are sub-summary parts. If you double-click on the S.. sidebar, a dialog will open showing which fields it is summarizing by. The first sub-summary part summarizes by Customer, and the second one summarizes by project.

5a. Double click the sub-summary sidebar for the Project part, then simply change the selected field from Project to Department, and click OK. If you need to summarize by both Project and Department, then continue with 5b. Otherwise skip to step 6.

5b) If you want to summarize by both project and department, then you will need to add another sub-summary part to the layout. To do this, grab and drag the bottom border of the footer part part down about 1/4 inch, and move the existing footer fields down to the bottom of the footer. Now, drag a new "part" from the status area, and drop it in the footer just above the fields you moved. In the dialog that comes up, select sub-summary, and select the Project field. Click OK. In the next dialog, click "Print Below." Using Copy/Paste, duplicate the three fields from the Department sub-summary part in the new part.

6. In the Department sub-summary part, double-click on the Project field and change it to Department.

Now when you do a customer report, you should see totals for each department.

[Nicolas]

Thank's for the help Bob. I really appreciate the fact that you took the time to explain the whole procedure.

Great Forum

Nicolas

[BobWeaver]

Normally, I would have just created a sample file and posted it. But, I don't think FMI would appreciate someone modifying their sample file and then re-posting it.

Anyway, glad it helped.