Creating a Business Object or IQA using a LEFT JOIN with a filter

Occasionally you may wish to create an IQA that contains a filtered LEFT JOIN.

As an example, a user may want to be able to create a query of all the contacts, left join on Activity, but only retrieve the Activities that are type "Dues" (or another type).


Activity A
ON = NetContactData.ID AND A.Activity_Type = 'DUES'


Presently the way to do this is to create custom business object in the Business Object Designer using Activity as a Source table. 


Then in the Filter expression on the database tab you can apply a filter this object.

For example:

Activity_Type = 'DUES'




Now when you create your IQA. You can add NetContactData and create LEFT JOIN to your new custom business object. The business object will already have filtered the Activity Type and your LEFT JOIN will return the correct results.




Please sign in to leave a comment.
Powered by Zendesk