Learn Goal 5 - Part 4: Get a total of hours worked for each customer

Document created by Kedar on Dec 10, 2014Last modified by communitymanager on May 12, 2015
Version 28Show Document
  • View in full screen mode



It’s great to have a list of all the work orders on a line-by-line basis, but wouldn’t it help to know the total hours per customer as well?


FileMaker Pro provides various kinds of summary fields to do this math for you. You can get totals, counts, averages, and lots more.

In this case, let’s get the total work order hours.

 

 

Goal

Add a summary field that calculates the total work order hours per customer and place it on the “Work Orders” tab.

 

 

Steps

  1. Choose the File menu > Manage > Database…  ⇧⌘D or Ctrl-D

    The Manage Database... dialog box appears.

  2. Select the Fields tab at the top of the window.
    manage database fields tab.png

    The Fields tab lists the fields in a given table and allows you to make changes to them.

  3. Change the Table drop-down in the upper left from “Customers” to “Work Orders”.

    The fields in the “Work Orders” table are displayed.

    manage database table drop-down.png

  4. In the Field Name field near the bottom of the screen, enter the name for a new field: Hours Total.

  5. Change the Type drop down on the right from “Text” to “Summary”.

  6. Click the Create button.

    An Options for Summary Field... dialog box appears. The default summary type is set to “Total Of”, which is what you want.

    Next you need to specify the field being summarized:

  7. Change the field selected in Available Fields from “Work Order No” to “Hours”.

  8. Click the OK button in the lower right.

    The Options for Summary Field… dialog box disappears, and a new “Hours Total” field appears in the field list.

  9. Click the OK button in the lower right.

    The Manage Database… dialog box disappears and you are returned to your layout.


    Now that you've created your "Hours Total" summary field, let's put it on your layout.

  10. Make sure you are viewing the “Customer Detail” layout with the “Work Orders” tab selected.

  11. Choose the View menu > Layout Mode  ⌘L or Ctrl-L

  12. Click and hold the Field tool from the status toolbar, dragging downwards to position a field at the bottom of the “Work Orders” tab (i.e. below the “Work Orders” portal).
    field tool.png

    When you release the mouse, a Specify Field dialog box appears.  At the top of the dialog box is a drop-down allowing you to choose a table occurrence.

  13. Choose the “Work Orders” table occurrence.

  14. Select the “Hours Total” field.
    choose hours total.png

  15. Click the OK button in the lower right.

    You are returned to the layout, and your new field appears where you placed it.

  16. Line up your field horizontally with the “Hours” field in the portal above it.

    The idea is that the “Hours Total” appears to be in the same column as the “Hours” in the portal.

    pasted-image-5.png

  17. Resize your field to match the “Hours” field above it as well.


    Now that the field is in place, let's apply some formatting to it:

  18. In the Inspector, choose the Appearance tab.
    inspector appearance tab 2.png

  19. In the Graphic area, change the Fill drop-down from “Solid Color” to “None”.

  20. Change the Line drop-down from “Solid” to “None” as well.

  21. Also use the Inspector to format the text as bold and align the text to the right.

  22. Use the Data tab of the Inspector to specify two decimal places (as you’ve done before).


    Now that the formatting is finished, let's see how the field looks:

  23. Choose the View menu > Browse Mode  ⌘B or Ctrl-B

  24. Select the “Work Orders” tab.

    This is how it should look for the first record:

    Screen Shot 2014-12-21 at 3.14.02 PM.png


  25. Spot-check a couple of customers to see whether the hours total displayed at the bottom of the screen reflects the hours listed.

    If you see a “?” in the “Hours Total” field, this means it is not wide enough.

  26. If necessary, go back into Layout mode and make the field wider.

     

      

Attachments

    Outcomes