Joining Salesforce Business Objects

In this section:

This chapter describes the special considerations when joining two salesforce business objects.

The following image shows a sample of the columns retrieved from CASE, a list of customer problem reports.

Sample Columns

Note that the sample includes a product code, PRODUCT__C, but not the actual product name. The product name can be obtained from the PRODUCT2 object.

The synonyms created for the methods of Salesforce business objects are similar to synonyms for database stored procedures and web services. They are represented as a hierarchy with INPUT and OUTPUT parameters. The following image shows the synonym created for the salesforce object PRODUCT2 for the method RETRIEVE_PRODUCT2.

Synonyms

The INPUT parameters are those that are passed to the method. The OUTPUT.INSTANCE parameters are those that are returned as an answer set. For example, the following image shows the answer set returned when the first four columns are retrieved using the method RETRIEVE_PRODUCT2, and shows a list of product names and product codes.

In order to create a table in DataMigrator or a report in WebFOCUS that includes the case information and the product name, you must join the field from CASE called PRODUCT__C to the product code field in PRODUCT2.

Join Editor

There are two product codes in the synonym for the RETRIEVE_PRODUCT2 method. There is INPUT.PRODUCTCODE and INSTANCE.PRODUCTCODE. You could select either field and get the same result, but the way the data is retrieved is different. The following sections describe the different methods that can be taken to obtain the result.


Top of page

x
JOIN to an INPUT Field

When you join to an INPUT field, for example, INPUT.PRODUCT_CODE, the value becomes an input parameter to the method. The second method is called for each row retrieved by the first method. This method is acceptable if only one or a small number of cases are retrieved. However, in this example, where RETRIEVE_CASE returns 26 rows that means that RETRIEVE_PRODUCTS2 would be called 26 times. The product code becomes the input parameter to RETRIEVE_PRODUCT, but note that you must pass an input parameter, using a WHERE condition, to RETRIEVE_CASE.


Top of page

x
JOIN to an OUTPUT Field

When you join to OUTPUT field, for example, INSTANCE.PRODUCT_CODE, the entire answer set for both tables are retrieved first, and the JOIN is done on the full answer sets. This method is acceptable, as long as the answer sets retrieved are not too large. In this example, where RETRIEVE_CASE returns 26 rows and RETRIEVE_RODUCT2 only returns 17 rows, it is the faster approach. Note that in this case you must still pass at least one input parameter to each method.


Top of page

x
JOIN Output

The following image shows the result of the join with the case information and the product name.


WebFOCUS