• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 6. Creating Relationships > Summarizing Related Data

Summarizing Related Data

Summary functions work on related fields in a layout just as they do on the non-related fields in the database. You can summarize the contents of a portal's data using a calculation field to create an account statement for individual clients, for example.

To summarize related data:

Open your master file and go to the layout that holds your portal. Choose File > Define Fields.

When the Define Fields dialog box appears, type a name for the field, click the Calculation radio button, then click Create.

In this example, the field name is Sales Summary (Figure 6.42).

Figure 6.42. Related fields can be summarized with a calculation field.

In the Specify Calculation dialog box, double-click an aggregate function from the function list on the right.

Depending on what type of information you want, you can use Sum, Average, Count, Max, Min, StDev or StDevP. In our example, we've chosen Sum because we want to add all of the records.

Click the drop-down list above the field list on the left and select the relationship for the field you want to summarize (Figure 6.43).

Figure 6.43. Before you choose a field, choose the relationship from the drop-down list to switch the field listing to the source file.

The field list will change to display the fields in the source file. Because they're related fields, their names begin with a double colon.

In the field list, double-click the field you want to summarize (Figure 6.44).

Figure 6.44. Choose the field you want to summarize from the field list on the left.

The two related field names (the one in the master file and its related field in the source) will appear in the formula box (Figure 6.45).

Figure 6.45. Enter the related field in the function between the parentheses.

Leave the calculation result set to Number. Click OK and then Done.

Switch to Layout Mode (Ctrl+L/ Command-L) and add the field to the layout outside the portal.

In our example, we've added a Sales Summary field (Figure 6.46).

Figure 6.46. Put the summary calculation field on the layout body, not in the portal.

Go to Format > Number.

When the Number Format dialog box appears, you can specify exactly the format in which you want the summary information to appear (Figure 6.47).

Figure 6.47. You can format the summary field to display in any standard number format.

In this case, we chose the Format as decimal radio button and set it to display the number to two decimal places. We also used a comma to set off the thousands column.

Switch to Browse mode to see the total for that record in the summary field (Figure 6.48).

Figure 6.48. The summary calculation field displays the total for the current record.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint