How to enable a value help in a CDS view for Fiori Elements?

In this short tutorial, I would like to show you how to simply apply a value help to your CDS fields by simply annotate them with another database table.

First of all, we need a simple database table, that already contains some information. We have here a table (ZAIRORTS) containing two entries with airport id and a corresponding name.

Now that we have the table, we need to overlay it with a CDS view. We simply do that by define a view entity and import the fields and rename them as needed.

@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Value help for Airports'
define view entity ZVALUEHELP_AIRPORTS as select from zairports
{
    key airport_id as AirportId,
    name as Name
}

Then finally we can annotate with the @Consumption.valueHelpDefinition the corresponding entity ZVALUEHELP_AIRPORTS, which is our newly created CDS View from before and an element, which will use the later the content from. In our case it is the AirportID like ZRH.

@AccessControl.authorizationCheck: #CHECK
@Metadata.allowExtensions: true
@EndUserText.label: 'Projection View for ZR_CONNECTIONS'
@ObjectModel.semanticKey: [ 'CarrID', 'ConnID' ]
define root view entity ZC_CONNECTIONS
  provider contract transactional_query
  as projection on ZR_CONNECTIONS
{
  key CarrID,
  
  key ConnID,
  
  //...
  
  @Consumption.valueHelpDefinition: [{ 
    entity: { name: 'ZVALUEHELP_AIRPORTS', element: 'AirportId' }
  }]
  @EndUserText.label: 'Airport from'
  AirportCityFromId,
  
  @Consumption.valueHelpDefinition: [{ 
    entity: { name: 'ZVALUEHELP_AIRPORTS', element: 'AirportId' }
  }]
  @EndUserText.label: 'Airport to'
  AirportCityToId,
  
  //...
}

As you can see, we have defined it directly twice, since we want to use it for our connections table which defines an airport from and to.

Finally, we can check it with our Fiori Elements preview app, if it does work. We have also the ability to search through the CDS view entity inside the dialog to precise our value help.