Custom Database Pickers

SheetKraft supports the configuration of suggestion lists to make it easy for users to enter correct input values. We call this input control a Picker. The list of suggestions can be static (List Picker), specified as part of the activity definition or it can be linked to a database table (Database Picker). The UI for configuring the Database Picker takes a table name and a column name. Unique values from the selected column are shown by default. There is also an option for the type of search within the list when the user starts typing into the input box. The default is "Contains", which means that any value in the column that contains the text typed by the user will be shown as a suggestion. This is the most convenient option for the user in most cases. However, if the table is extremely large, this might not be efficient. In such cases, the search can be configured as "StartsWith" which means that only values that start with the text typed by the user will be shown.

Occasionally there are cases where this functionality is not sufficient. The list of suggestions may need to be filtered based on the values of other inputs or based on the user who is currently logged in. For such cases, SheetKraft provides the option of customizing the query that is used when the list of suggestions needs to be retrieved. This post will describe how this can be achieved.

By default the query looks like the following

select distinct {top (@_count_)} "City" as "Value" from "abc"."Locations" where {"City" contains @_input_} and {"City" > @_lastseen_} and {"City" < @_firstseen_} {order by "City"} {limit @_count_}

Explanation of the syntax

The syntax of this query is very close to SQL with some simple extensions that allow SheetKraft to easily modify it as needed. The usage of these extensions will be described here. This query fetches values from the "City" column of the "abc"."Locations" table.

However, the list must be filtered based on the text typed by the user. This is achieved by the {"City" contains @_input_} part of the query. @input is a SheetKraft built-in variable which will be substituted by the text typed in by the user. Built-in variables start and end with an underscore to avoid conflicts with user defined variables.

Suppose the user typed in the letters 'mum'. When executing this query against a SQL Server backend, SheetKraft will transform {"City" contains @_input_} to something like "City" like '%' + @_input_ + '%' and execute the query with the variable @input set to the value 'mum'. The actual transformation is even more complex because the text typed by the user might contain the '%' character in general and this will need to be escaped. Since it would be too cumbersome for users to take all this into consideration, SheetKraft understands the pattern {any_sql_expression_here contains @any_variable} and transforms it into a correctly escaped like clause.

When suggestions are needed, it would be inefficient (or even infeasible) to get the entire list. Hence only a few values are queried and shown to the user. This is achieved by the {top (@_count_)} and {limit @_count_} parts of the query. Depending on the database backend, only one of these two parts will be retained in the actual query.

When the user scrolls down the list of suggestions, more suggestions need to be loaded. Similarly if the user scrolls up, previously shown suggestions might need to be reloaded. The {"City" > @_lastseen_} and {"City" < @_firstseen_} parts of the query help with this. @lastseen represents the last value that has been seen by the user, so that new values to be shown must be after this. Similarly @firstseen represents the first value that has been seen by the user, so that new values to be shown must be before this. SheetKraft will drop one of these parts based on whether the user is scrolling up or scrolling down. The {order by "City"} part of the query must be correspondingly transformed into order by "City" asc or order by "City" desc and SheetKraft handles that too.

To summarize, there are built-in variables that SheetKraft will substitute and there are patterns to parts of the query that SheetKraft will understand and transform to form the final query.

Let us now see how to customize the query in various ways. Changes to the query are shown in bold.

Adding a filter condition

Suppose it is necessary to only show values in the "City" column where the "IsActive" column has the value 1. This can be achieved by setting the query to select distinct {top (@_count_)} "City" as "Value" from "abc"."Locations" where {"City" contains @_input_} and {"City" > @_lastseen_} and {"City" < @_firstseen_}and "IsActive" = 1{order by "City"} {limit @_count_}

Note that the only change is adding and "IsActive" = 1 in the correct location with regular SQL syntax.

Join with another table with a condition based on current user

Suppose it is necessary to only show the cities to which the current user has access. Suppose that the user access information is stored in another table "abc"."LocationAccess" which has columns "LocationId" and "UserId". This can be achieved by setting the query to select distinct {top (@_count_)}loc."City" as "Value" from "abc"."Locations"as loc inner join "abc"."LocationAccess" as acc on loc."Id" = acc."LocationId"whereacc."UserId" = @_userid_ and{loc."City" contains @_input_} and {loc."City" > @_lastseen_} and {loc."City" < @_firstseen_} {order byloc."City"} {limit @_count_}

Note that the change required is adding a join with regular SQL syntax and using aliases to prevent ambiguity in column names.

Adding a condition based on another input

Suppose that there is another user input for State and the list of cities must be filtered to only the cities in the selected state. To achieve this, a variable needs to be created for the selected state. In the Define Activity UI, the Sql Name field for the State input can be set to @state. Then the query can be further modified as shown. select distinct {top (@_count_)} loc."City" as "Value" from "abc"."Locations" as loc inner join "abc"."LocationAccess" as acc on loc."Id" = acc."LocationId" where acc."UserId" = @_userid_and loc."State" = @stateand {loc."City" contains @_input_} and {loc."City" > @_lastseen_} and {loc."City" < @_firstseen_} {order by loc."City"} {limit @_count_}