Using Automatic ID Lookup in Data Sources

If Automatic ID Lookup is enabled, data sources defined in view files automatically add lookup fields and tables to the list of queried and displayed fields, if lookup fields are not explicitly defined in the data source.  

For each ID sort field, the data source adds the lookup sort field before the ID sort field.

Sort fields defined in the view

Generated SQL

wr.dp_id

ORDER BY dp.name, wr.dp_id

wr.dv_id, wr.dp_id

ORDER BY dv.name, wr.dv_id, dp.name, wr.dp_id

For an ID index field, the data source replaces it with the lookup index field,

Data sources created using Java API do not add lookup fields automatically, regardless of global preferences.

Custom SQL queries

By default, Automatic ID Lookup is disabled for data sources that have custom SQL queries. To enable Automatic ID Lookup for a custom SQL query, use one of the expressions:

Expression

Example of use

Generated query - lookup disabled

Generated query - lookup enabled

sql.lookupJoin

FROM rm ${sql.lookupJoin('rm.dp_id')

FROM rm

FROM rm LEFT OUTER JOIN dp

sql.lookupField

SELECT ${sql.lookupField('rm.dp_id')

SELECT rm.dp_id

SELECT rm.dp_id, dp.name

sql.lookupIdField

SELECT ${sql.lookupIdField('rm.dp_id')

SELECT rm.dp_id

SELECT dp.name

sql.lookupConcatFields

SELECT ${sql.lookupConcatFields('rm.dv_id', 'rm.dp_id')

SELECT rm.dv_id||'-'||rm.dp_id

SELECT dv.name||'-'||dp.name

sql.concatFields

SELECT ${sql.concatFields('rm.dv_id', 'rm.dp_id')

SELECT rm.dv_id||'-'||rm.dp_id

sql.translatableField

SELECT ${sql.translatableField('projecttype.project_type')

SELECT projecttype.project_type_de AS project_type

Add-in managers can modify reports and business logic with custom queries to use these expressions. 

Below is an example of a data source with an SQL query:

Without expressions

With expressions

<dataSource id="stockDataSource">
  <table name="project"/>
  <table name="bl" role="standard"/>
  <field name="project_id" table="project"/>
  <field name="project_name" table="project"/>
  <field name="bl_id" table="project"/>
  <field name="ctry_id" table="bl"/>
  <sql dialect="generic">
    SELECT project_id, project_name, project.bl_id, bl.ctry_id
    FROM project
    LEFT OUTER JOIN bl ON bl.bl_id = project.bl_id
  </sql>
</dataSource>

<dataSource id="lookupDataSource">
  <table name="project"/>
  <table name="bl" role="standard"/>
  <field name="project_id" table="project"/>
  <field name="project_name" table="project"/>
  <field name="bl_id" table="project"/>
  <field name="ctry_id" table="bl"/>
  <sql dialect="generic">
    SELECT project_id, project_name, ${sql.lookupField('project.bl_id')}, bl.ctry_id
    FROM project
    ${sql.lookupJoin('project.bl_id')}
  </sql>
</dataSource>

Virtual SQL fields 

Some views have virtual SQL fields that use unqualified field names (for example, bl_id instead of em.bl_id in schema/ab-products/space/common/console/ab-sp-console-drawing.axvw).  

<dataSource id= "employeesWaitingDS" >
<table name= "em" />
<field name= "em_id" />
<field name= "bl_id"  hidden= "true" />
<field name= "fl_id"  hidden= "true" />
<field name= "rm_id"  hidden= "true" />
<field name= "location"  dataType= "text" >
<title>Current Location</title>
<sql dialect= "generic" >bl_id${sql.concat} '-' ${sql.concat}fl_id${sql.concat} '-' ${sql.concat}rm_id</sql>
</field>
</dataSource>

This works because the data source only queries one table. However, if ID lookup is enabled, the data source adds joins to lookup tables, and the query will fail because unqualified field names will become ambiguous. 

You should modify all such views to include qualified field names:

<sql dialect= "generic" >em.bl_id${sql.concat} '-' ${sql.concat}em.fl_id${sql.concat} '-' ${sql.concat}em.rm_id</sql>

SQL restrictions

SQL restrictions that use unqualified field names need to be modified to include the table name.

<dataSource type= "grouping"  id= "areaRangesDrawing_floorsDs" >
<table name= "rm"  role= "main" />
<field table= "rm"  name= "bl_id"  groupBy= "true" />
<field table= "rm"  name= "fl_id"  groupBy= "true" />
<field table= "rm"  name= "dwgname"  groupBy= "true" />
<restriction type= "sql"  sql= "dwgname is not null" />
</dataSource>

Change the restriction to:

   <restriction type= "sql"  sql= "rm.dwgname is not null" /> Data sources called from Java

Data sources called from Java

Data sources created from Java code do not automatically add Lookup fields. Normally, they do not need to. If you wish to use Automatic ID Lookup for these data sources, call dataSource.enableIdLookup(true). 

Copyright © 1984-2019, ARCHIBUS, Inc. All rights reserved.