Skip to main content

Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

Scout/Tutorial/4.0/Minicrm/Lookup Calls and Lookup Services

< Scout‎ | Tutorial‎ | 4.0‎ | Minicrm

The Scout documentation has been moved to https://eclipsescout.github.io/.

Note.png
Scout Tutorial
This page belongs to the Minicrm Step-by-Step Tutorial. It explains how to create a lookup call and a lookup service that can then be used to fill smartfields or listboxes. You need to have completed the Code Types chapter in order to continue.


What is this chapter about?

In the previous chapter we have seen how to fill smartfields with an enumerated list of codes that were based on a code type. However, sometimes you need a smartfield (or listbox) with dynamically calculated entries that are based on the data in your database (e.g. a smartfield that allows selecting a person or company). This chapter shows how to accomplish this by implementing a so called Lookup Call and a corresponding Lookup Service. The lookup call is going to allow us to dynamically query companies from our database and provides the basis for a new Employer smartfield on the person search form.

Create Person Search Form

Before we get to the interesting part we have to do some quick preliminary work. If you have not already done so in an earlier chapter, we first have to create a person search form. Go to the PersonTablePage, right-click and choose Create Search Form. The created search form contains a field for each column defined on the PersonTablePage.

Scout.3.9.minicrm.lookup.person search form.new menu.png

Use Person as the name.

You might also want to set a "Sub Package". To keep all your search form together you might want to choose something like ui.desktop.outlines.pages.searchform. Another possibility is to group your form and searchform by entity (PersonForm and PersonSearchFrom in the sub-package entities.person).

Click Finish.

Scout.3.9.minicrm.lookup.person search form.wizard.png

Create Company Lookup Call

In the steps following later we want to add a company smartfield to the PersonSearchForm we just created. This field will present a filterable list of companies that allows users to easily find persons who are working for a specific company. In order to be able to do that we first have to create a new LookupCall:

Scout.3.9.minicrm.lookup.lookup call.new menu.png

Set the type name to CompanyLookupCall and key type to Long. Also make sure to select the create new lookup service option as well as the AbstractSqlLookupService, then click Finish.

Scout.4.0.minicrm.lookup.lookup call.wizard.PNG

Note.png
'AbstractLookupService'
In this tutorial the AbstractSqlLookupService implementation is used as lookup service super class. As shown later in this chapter, this abstract implementation is very convenient if your lookup service is based on a single SQL statement to retrieve the data. However, keep in mind that the Scout framework does not force you to use this class, instead it allows you to use any AbstractLookupService implementation. If you for example need a lookup service that operates on the basis of webservice calls you can easily provide your own specialized lookup service implementation.


The new lookup call has now been created. Note that it is already configured to use an (also automatically created) lookup service. Note that we did not have to care about the creation and registration of the lookup service and its corresponding service interface, it has all been managed by the Scout SDK.

Scout.3.9.minicrm.lookup.lookup call.explorer.png

public class CompanyLookupCall extends LookupCall<Long> {
 
  private static final long serialVersionUID = 1L;
 
  @Override
  protected Class<? extends ILookupService<Long>> getConfiguredService() {
    return ICompanyLookupService.class;
  }
}

Now it is time to implement the company lookup service. Double-click on the CompanyLookupService and use the following implementation:

public class CompanyLookupService extends AbstractSqlLookupService<Long> implements ICompanyLookupService {
 
  @Override
  public String getConfiguredSqlSelect() {
    return "" +
        "SELECT  C.COMPANY_NR, " +
        "        C.NAME " +
        "FROM    COMPANY C " +
        "WHERE   1=1 " +
        "<key>   AND     C.COMPANY_NR = :key </key> " +
        "<text>  AND     UPPER(C.NAME) LIKE UPPER(:text||'%') </text> " +
        "<all> </all> ";
  }
}

Looking at this implementation we see that for each company returned by our lookup service we provide both a (unique) key and a text, which is a general characteristic of lookup services. Further we see that parts of the SQL statement are enclosed in tags. This is because a lookup can be performed in several ways:

  • Key-Lookup: Single-result lookup based on a unique key (e.g. when loading a form with a smartfield containing a value).
  • Text-Lookup: Multi-result lookup based on a textual search-term (e.g. when entering text into a smartfield).
  • All-Lookup: Unrestricted lookup that returns all available key-text pairs (e.g. when clicking the magnifier button on a smartfield).

Depending on the way the lookup is performed, only one SQL part in tags is used. If for example a Text-Lookup is performed, only the SQL code in the corresponding <text> tag is used, whereas the SQL code in the other tags is ignored.

Note.png
'AbstractSqlLookupService'
In the above section we have shown you how to return key-text pairs in your lookup service implementation. There is nothing wrong about that, but it is also not the whole truth. The AbstractSqlLookupService actually allows you to return additional meta-data that controls how the returned data is presented to the users. You can for example return icon IDs, color codes, tooltip texts or font descriptions. The complete list and order of all columns supported by the AbstractSqlLookupService is as follows:
  • Object key
  • String text
  • String iconId
  • String tooltip
  • String background color
  • String foreground color
  • String font
  • Boolean enabled
  • Object parentKey used in hierarchical structures to point to the parents primary key
  • Boolean active (0,1)


Our lookup call is now ready to be used. This is what we are going to do in the next section.

Adding an Employer Field

Next we are adding an additional Employer smartfield to the search dialog. This will allow users to find persons who are working for a specific company. The field is added to the search form as follows: right-click on the PersonSearchForm and pick New Form Field... from the menu.

Scout.3.9.minicrm.lookup.person search form.new field.menu.png

Then pick Smart Field from the list and click Next.

Scout.3.9.minicrm.lookup.person search form.new field.wizard1.png

Set the name to Employer and set the lookup call to our newly created CompanyLookupCall, then click Finish.

Scout.3.9.minicrm.lookup.person search form.new field.wizard2.png

After restarting our server and client we can see a fully functional Employer smartfield in the person search form. When entering text into this smartfield, the system automatically filters the shown list of available companies.

Scout.3.9.minicrm.lookup.person search form.client swt1.png

Now there is only one piece missing here. Even though the Employer smartfield behaves as desired, the rows in the PersonTablePage are not yet filtered according to the search constraints when clicking on the search button. That is what we do in the next section.

Updating Table Pages and Outline Service

In order to constrain the SQL statement for the person outline, we have to adapt the service operation getPersonTableData and add a parameter formData of the type PersonSearchFormData to the method. Do that for both the service interface IStandardOutlineService and the service implementation StandardOutlineService.

Interface:

public interface IStandardOutlineService extends IService {
 
  public Object[][] getCompanyTableData(CompanySearchFormData formData) throws ProcessingException;
  public Object[][] getPersonTableData(PersonSearchFormData formData, Long companyNr) throws ProcessingException;
}

Implementation:

@Override
public Object[][] getPersonTableData(PersonSearchFormData formData, Long parentPageCompanyNr) throws ProcessingException {
 
  StringBuilder statement = new StringBuilder();
  statement.append("SELECT PERSON_NR, LAST_NAME, FIRST_NAME FROM PERSON WHERE 1=1 ");
 
  if (parentPageCompanyNr != null) {
    statement.append("AND COMPANY_NR = :parentPageCompanyNr ");
  }
 
  if (!StringUtility.isNullOrEmpty(formData.getFirstName().getValue())) {
    statement.append("AND UPPER(FIRST_NAME) LIKE UPPER(:firstName || '%') ");
  }
  if (!StringUtility.isNullOrEmpty(formData.getLastName().getValue())) {
    statement.append("AND UPPER(LAST_NAME) LIKE UPPER(:lastName || '%') ");
  }
  if (formData.getEmployer().getValue() != null) {
    statement.append("AND COMPANY_NR = :employer ");
  }
 
  return SQL.select(statement.toString(), new NVPair("parentPageCompanyNr", parentPageCompanyNr), formData);
}

Note that we have relied once again on the naming convention: We are referencing the value of the Employer form field by using an identically named :employer bind variable.

Now we need to forward the PersonSearchFormData to the server. Return to the method execLoadTableData in the PersonTablePage and add a new parameter to the service call according to its interface definition. The required PersonSearchFormData is available from the filter parameter:

@Override
protected Object[][] execLoadTableData(SearchFilter filter) throws ProcessingException {
  PersonSearchFormData formData = (PersonSearchFormData) filter.getFormData();
  if (formData == null) {
    formData = new PersonSearchFormData();
  }
  return SERVICES.getService(IStandardOutlineService.class).getPersonTableData(formData, getCompanyNr());
}

After restarting both server and client the Employer smartfield is fully functional and ready to be used.

Scout.3.9.minicrm.lookup.person search form.client swt2.png

Parametrizing Lookup Calls

So far our Employer smartfield has been independent of any other fields in the search form. In this section we are going to change this and add an additional Employer Type smartfield to the person search form. This smartfield will allow us to search for persons working for a specific type of company (e.g. only persons working for Supplier companies). Then of course the two employer smartfields should also be linked. If we have for example set the Employer Type field to Supplier, then the Employer smartfield should only list suppliers but no customers.

Let us start by adding the new Employer Type smartfield to the person search form:

Scout.3.9.minicrm.lookup.employer field.new menu.png

When creating the field use the following options:

Scout.3.9.minicrm.lookup.employer field.wizard.png

One way to pass on the information from the Employer Type to the Employer smartfield is by setting the master field accordingly: Click on the Employer field in the Scout Explorer view. Then change the Master Field property to Employer Type.

Scout.3.9.minicrm.lookup.employer field.properties.png

We can now adapt the CompanyLookupService quite easily. Scout automatically provides a :master bind variable that contains the value of the master field. We can make use of this variable as follows:

public class CompanyLookupService extends AbstractSqlLookupService implements ICompanyLookupService {
 
  @Override
  public String getConfiguredSqlSelect() {
    return "" +
        "SELECT  C.COMPANY_NR, " +
        "        C.NAME " +
        "FROM    COMPANY C " +
        "WHERE   1=1 " +
        "  AND (C.TYPE_UID = :master OR :master IS NULL) " +
        "<key>   AND     C.COMPANY_NR = :key </key> " +
        "<text>  AND     UPPER(C.NAME) LIKE UPPER(:text||'%') </text> " +
        "<all> </all> ";
  }
}
Note.png
Lookup Call Parameterization
In this section the lookup call was implicitely parameterized by configuring a master field on the smartfield. However, lookup calls can also be explicitely parameterized by configuring a Lookup Call Variable. Such variables can be created very easily in the Scout Explorer view by right-clicking on the Variables folder below a lookup call. Once such a variable has been defined, a correspondingly named bind variable (e.g. :companyNr for a lookup call variable named CompanyNr) will automatically be available to your AbstractSqlLookupService implementation. Note that instead of creating multiple lookup calls and lookup services that share an almost identical code-base it is usually preferable to create a single lookup call that is properly parametrized. This leads to unduplicated and more maintainable code. (E.g. instead of creating a MalePersonsLookupCall and a CompanyPersonsLookupCall you could combine them to a generic PersonLookupCall with variables GenderUid and CompanyNr.)


Our outline service method responsible for selecting persons needs an update as well:

 @Override
public Object[][] getPersonTableData(PersonSearchFormData formData, Long parentPageCompanyNr) throws ProcessingException {
    StringBuilder statement = new StringBuilder();
    statement.append("" +
        "SELECT PERSON_NR, LAST_NAME, FIRST_NAME " +
        "FROM PERSON P, COMPANY C " +
        "WHERE P.COMPANY_NR = C.COMPANY_NR "
        );
 
    if (companyNr != null) {
      statement.append("AND P.COMPANY_NR = :parentPageCompanyNr ");
    }
    if (!StringUtility.isNullOrEmpty(formData.getFirstName().getValue())) {
      statement.append("AND UPPER(FIRST_NAME) LIKE UPPER(:firstName || '%') ");
    }
    if (!StringUtility.isNullOrEmpty(formData.getLastName().getValue())) {
      statement.append("AND UPPER(LAST_NAME) LIKE UPPER(:lastName || '%') ");
    }
    if (formData.getEmployerType().getValue() != null) {
      statement.append("AND C.TYPE_UID = :employerType ");
    }
    if (formData.getEmployer().getValue() != null) {
      statement.append("AND P.COMPANY_NR = :employer ");
    }
 
    return SQL.select(statement.toString(), new NVPair("parentPageCompanyNr", companyNr), formData);
}

After restarting server and client our new person search form should work like a charm!

Scout.3.9.minicrm.lookup.employer field.client swt.png

Search Form Parameterization

The PersonSearchForm is now working very well for our top-level PersonTablePage. But how about other places? When navigating to the PersonTablePage below a specific company the Employer (and Employer Type) field in the PersonSearchForm does not make much sense, because all listed persons are belonging to the same company anyway. One way to resolve this would be to hide the Employer field in the PersonSearchForm in such a case. Another, probably more transparent solution, is to disable the Employer field and to fill it with the corresponding company. Let us implement the second solution.

First create a CompanyNr variable of type Long for the PersonSearchForm:

Scout.3.9.minicrm.lookup.search form parameter.new menu.png

Scout.3.9.minicrm.lookup.search form parameter.wizard.png

Now we have to adapt the SearchHandler of the PersonSearchForm, so that the Employer field gets disabled and prefilled if the search form is used in the context of a specific company:

  public class SearchHandler extends AbstractFormHandler {
 
    @Override
    public void execLoad() {
      if (getCompanyNr() != null) {
        getEmployerField().setValue(getCompanyNr());
        getEmployerField().setEnabled(false);
        getEmployerTypeField().setVisible(false);
      }
    }
  }

Then we also have to pass on the CompanyNr variable from the PersonTablePage to the corresponding search form. In the PersonTablePage we implement the Exec Init Search Form method as follows:

@Override
protected void execInitSearchForm() throws ProcessingException {
  ((PersonSearchForm) getSearchFormInternal()).setCompanyNr(getCompanyNr());
}

If you have navigated to the persons below a company, the search form should now look as below. Note that the Employer field is now disabled and prefilled with the correct company:

Scout.3.9.minicrm.lookup.search form parameter.client swt.png

Parameterizing the PersonSearchForm with the CompanyNr allows us to do some refactoring. Let us throw out some old code!

The companyNr argument is not needed anymore in our outline service, since this information is already included in the PersonSearchFormData. The corresponding implementation gets simpler as well:

public interface IStandardOutlineService extends IService {
 
  public Object[][] getCompanyTableData(CompanySearchFormData formData) throws ProcessingException;
 
  public Object[][] getPersonTableData(PersonSearchFormData formData) throws ProcessingException;
}

And the corresponding implementation in the StandardOutlineService:

@Override
public Object[][] getPersonTableData(PersonSearchFormData formData) throws ProcessingException {
 
  StringBuilder statement = new StringBuilder();
  statement.append("SELECT PERSON_NR, LAST_NAME, FIRST_NAME FROM PERSON WHERE 1=1 ");
 
  if (!StringUtility.isNullOrEmpty(formData.getFirstName().getValue())) {
    statement.append("AND UPPER(FIRST_NAME) LIKE UPPER(:firstName || '%') ");
  }
  if (!StringUtility.isNullOrEmpty(formData.getLastName().getValue())) {
    statement.append("AND UPPER(LAST_NAME) LIKE UPPER(:lastName || '%') ");
  }
  if (formData.getEmployer().getValue() != null) {
    statement.append("AND COMPANY_NR = :employer ");
  }
  else if (formData.getEmployerType().getValue() != null) {
    statement.append("AND COMPANY_NR IN (SELECT COMPANY_NR FROM COMPANY WHERE TYPE_UID = :employerType) ");
  }
  return SQL.select(statement.toString(), formData);
}

In a last step we also remove the companyNr argument in our execLoadTableData(..) implementation of the PersonTablePage.

@Override
protected Object[][] execLoadTableData(SearchFilter filter) throws ProcessingException {
  PersonSearchFormData formData = (PersonSearchFormData) filter.getFormData();
  if (formData == null) {
    formData = new PersonSearchFormData();
  }
  return SERVICES.getService(IStandardOutlineService.class).getPersonTableData(formData);
}

...and there we are!

What did we just do?

Let us quickly sum up what we have done in this chapter:

  • We created a search form for the PersonTablePage and added an Employer smartfield to it. This smartfield allows selecting an arbitrary company and is based on the CompanyLookupCall (and the corresponding service) that we have created earlier.
  • The CompanyLookupService provides the data for the CompanyLookupCall and is built around an SQL statement that returns a key-text pair for each company. The SQL statement contains <key>, <text> and <all> tags that allow multiple kinds of lookups. This allows a lookup call to be properly used as a data source for a smartfield.
  • We added another smartfield to the search form. The Employer Type field allows to search for persons working for a specific type of company. We configured the Employer Type field to be the master of the Employer field and adapted the SQL code of the CompanyLookupService accordingly. As a result, when the user has set an Employer Type then the field Employer only allows selecting companies of that type.
  • In a last step we parameterized our person search form. As a result the search form's Employer field is disabled and already prefilled when the user navigates to persons below a specific company. This adaption then allowed us to refactor our outline service code and make it simpler and more consistent.

Back to the top