Example 2: Linking queries

In our second example, we'll create a form variable designated as a link type. This involves connecting two distinct queries, namely "orders" and "update order comment", to form a more powerful combined query. The result will be a table equipped with an edit button, enabling users to modify the comment field for each row via a modal window.

The code for “orders”:

Select
   orderNumber,
   orderDate,
   requiredDate,
   shippedDate,
   status as "tddata::status",
   comments,
   customerNumber,
   '$commentEdit' as "operations::Operations"
From
   orders
Order By
   orderDate Desc

Upon completing the code, our screen will appear as follows. Next, we'll walk through the process in a detailed, step-by-step manner.

Initially, you'll observe in the code the use of a form variable, highlighted in red, named $commentEdit. This particular variable serves as the connector to the update query, enabling the editing of comments for each row in this table.

Let's now delve into the configurations for the $commentEdit variable:

The Name field of the button is labeled as "Edit comment", which clearly communicates its function to the user. Its type is set as Link, and it is linked to a query named "Update order comment", which we will discuss later. The display type for the button is configured as Modal. The choices for this setting include either Modal or New Tab, determining the action upon button click. We selected Modal to keep the user on the same screen instead of redirecting them to a new tab.

The URL Variables section is critical as it defines how the two queries are connected. Here, we specify the keys and values that enable the queries to correspond with each other, with the values representing the column names in the table. In terms of styling, the CSS classes are set to btn btn-sm btn-warning, adhering to a standard Bootstrap button format.

Now that we have completed setting up the form variable, let's examine the "Update order comment" query, which is linked to this form variable.

The form variables here are straightforward. The variable $orderNumber is a text input set to readonly, and its default value is sourced from the $orderNumber in the original query's "orders" form variable URL. This setup informs the update query about which specific row is intended for editing. For the $comment variable, its default value is assigned using a select function. With Select comments From orders Where orderNumber = $orderNumber as its default, the field is automatically populated with the existing comment from the selected row.

The SQL code for this operation is a straightforward update statement:

Update
   orders
SET
   comments = '$comment'
Where
   orderNumber = $orderNumber

In this statement, we're updating the 'orders' table by setting the value of the comments column to the value of $comment. We target the specific row to update in the WHERE clause, matching the orderNumber in the table to the $orderNumber received from the original "orders" query.

With our code and settings in place, let's see what happens when we click the "Edit comment" button. For instance, we'll modify the comment for a specific row.

Begin by clicking the 'Edit comment' button.

A modal window appears, displaying all relevant data, including the Comment field already populated with the existing comment. Imagine the customer's requirement has shifted from needing ad materials to requiring a dashboard for overseeing all their shipments.

Here's the revised comment. Once we've finished updating it, we'll click the 'Update' button.

Upon successful execution of the query, a confirmation modal will appear.

We can now observe that the comment for the specified row has indeed been updated.

This demonstrates the ease with which we can modify the database directly from Kvery.

To conclude, we apply Custom CSS, set from the bottom menu, as follows:

td[data-val="Shipped"] {
	background-color: #d2f4e8!important;
    	color: #0f6848;
}
.execute-content iframe {
	max-height: 35vh !important;
}

This CSS styling is what enables us to add color coding to the table, enhancing its visual presentation and readability.