Search by Primary Key

Since version 1.0.5, the values for the custom fields of type Database Child Information can be displayed depending on the primary key of the value from the parent field(of type Database Information).

The convention for storing the primary key in a database custom field is to mark the primary key field in the query using "as pk".

Let's use a nested select in a dynamic query for an example:

Dynamic query with a nested select

SELECT id, fk_id, col1 FROM testTable1 WHERE fk_id = (select id from testTable2 where col = {customfield_10000});

If the parent field does not have associated a child custom field, there is no need to use to search by primary key.

For those fields, the query should look like:

Primary Key

SELECT id as pk, fk_id, col1 FROM testTable1 WHERE fk_id = (select id from testTable2 where col = {customfield_10000});

In the following example we assume the results of the following query will return multiple values for the parent field:

select country, city from table;

Suppose we have multiple cities and one country. If we have defined one field that would store the countries and the child stores the cities, using without pk, we would have one value for the country and one value for the city.

This query can be changed to:

Use Primary Key

select country, city, id as pk from table;

Then the parent should have a list of countries(same name), but with different ids and the child field can have one of the cities specified for that country.