Create Data Maps

Use the <ob_map_create> tag to read and process data on Salesforce objects that have many-to-many relationships with one another. For example, the Resource object in PSA will have a number of Assignment objects created against it; these in turn may be linked to multiple Timecard objects. You can create data maps in Output Builder which can be used to display information on how many timecards, as well as assignments, are linked to a single resource. This powerful functionality enables reports that display complex many-to-many relationships to your business users in an engaging way.

Syntax:

<ob_map_create object="Salesforce Object" relation="relationship name" map_by="api_field_name_1,api_field_name_2" map_name="My Map Name">
<filters>
<filter field="FilterOn_api_field_name" operator="equal,less,less_or_equal,greater,greater_or_equal,not_equal" value="value to compare to" />
<filter ... />
<filter ... />
...
</filters>
<ol_field name="api_field_name_1" />
<ol_field name="api_field_name_2" />
</ob_map_create>

Where:

  • Salesforce Object is the API name of the object to read.
  • relationship name is the name of the relationship (if blank, all records are read).
  • api_field_name_1,api_field_name_2 is a comma-separate list of fields to group the results by.
  • My Map Name is the name of the map, which is used to reference the map in a subsequent output_list.

You can add <filter> tags to filter the information returned by the <ob_map_create> tag. Where:

  • FilterOn_api_field_name is the API name of the field to filter the records returned by the <ob_map_create> tag.
  • equal, less, less_or_equal, greater, greater_or_equal, not_equal is the way in which to compare the api_field_name with the value to compare to.
  • value to compare to is the value to compare the api_field_name with to determine which records to include in the results.

You can filter information in the parent object by placing a <field> tag in the value attribute.

Note:

When you include more than one <filter> tag, the filters are added together using AND logic. This allows you to specify a ranges of values to filter on.

For each API field name specified in the map_by property, an <ol_field> tag must be included in body of the <ob_map_create> tag. The <ol_field> tag determines the fields that are read by the SQL query.

Mapped Aggregate Field

You can use the <ol_aggr_field> tag to generate mapped aggregate results that can then be read and displayed in a report. You can nest <ol_aggr_field> tags within an <ob_map_create> tag, as shown in this example:

<ob_map_create object="Opportunity" relation="" map_by="AccountId" map_name="acctVal">
<ol_field name="AccountId" />
<ol_aggr_field name="Amount" aggregate_function="sum" />
<ol_aggr_field name="Amount" aggregate_function="min" />
<ol_aggr_field name="Amount" aggregate_function="max" />
<ol_aggr_field name="Amount" aggregate_function="avg" />
<ol_aggr_field name="Amount" aggregate_function="cnt" />
<ol_aggr_field name="ExpectedRevenue" aggregate_function="sum" />
</ob_map_create>