How to create use foreign keys in custom query selector

Introduction

This wiki entry explains how to configure custom HQL query selectors to support using foreign key references in their selector fields.

This will enable filtering those fields as any other foreign keys in the standard grids. A combo with all the options that apply to the current data being displayed will be displayed.

A working example can be tested by installing this patch: https://drive.google.com/file/d/18IX3wZSzsaM5miajUQn4EF76DjISW2w2/view?usp=sharing. It updates the ProductSimple selector and assigns it to the Product field of the Lines tab of the Sales Order window. Two foreign key properties are added, the category of the product and the organization of the price list version, to demonstrate that we can have foreign keys of properties of different entities.

Custom HQL query configuration

In order to have the foreign key support, it is important the the relevant part of the SELECT clause of the custom HQL points to an entity and not to a primitive property. For instance, if we want to display a foreign key column to show information about the categories in a product selector, the relevant SELECT clause should be written like this:

SELECT e.productCategory as productCategory, ... FROM Product e

If instead of having a foreign key field we just wanted to show the name of the product category in a string column, we would have written the HQL like this:

SELECT e.productCategory.name as productCategory, ... FROM Product e

Selector Field configuration

As with all other selectors fields, it is important to fill in properly the Name (i.e. productCategory), Display Column Alias (i.e. productCategory) and Clause Left Part (i.e. e.productCategory).

In order to enable foreign key capabilities to the field an extra step should be done: the Reference field must point to a Table reference that links to the referenced entity (i.e. to this one: https://livebuilds.openbravo.com/erp_pi_pgsql/?tabId=103&recordId=163)