Searching by Primary Key

Owned by Jonathan Muse
Last updated: Oct 08, 2021
1 min read

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

To store the primary key in a database custom field, mark the primary key field in the query using "as pk".

Example 1

Let's use a nested select in a dynamic query:

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 a child custom field associated, 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});

Example 2

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;

For instance, we have multiple cities and one country. If we define one field to store the countries and the child to store the cities using without primary key, we would have one value for the country and one value for the city.

This query can be changed to:

Use Primary Key

Then the parent will 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.

See More