Forms and Reports

DbSchema Forms and Reports can be used for:

The Designer

The easiest way to try the forms and reports are using the DbSchema sample models. To open the sample model close any model in DbSchema and in the welcome panel you will have an option to open the sample projects. Alternative you can start a new form or report from the menu, which will open the form wizard. Here you can choose to use data from a table.
Database Forms and Reports Wizard
An simple example generated report is below.
Sample Database Report

The same designer is used for both, forms and reports. The designer consist of panels or tables and cells and . Panels and tables are similar in the designer: they contain cells and they have a title. A cell may contain any component: label, text field, buttons, etc. or another panel or table. This gives the flexibility of creating panels or tables inside other panels or tables. Like this is easy to design reports with multiple master-detail sections.
Sample Database Form with Master-Detail

Components can be created by clicking an empty cell.
How to Create Database Form Component

Existing components can be sized to cover multiple cells by dragging from the bottom-right grip. To move a component to a different cell drag the component text.
How to move database form  component

Clicking an component the component editor will show on the right. Here we edit a label. The label text may contain variables in form of ${...} which will be replaced at runtime with the variable data.
How to edit database form component

By clicking a table or panel title, you can edit the data source. Here the data source is a SELECT query. For each result column a variable with the same name will be created. The panel will execute the data source query and for each data record will output its body record in the output. Labels can be placed in the report to show the variables data.
How to edit database form

Form Scripts

The most important form script is the data source. This can be a simple SQL query:

SELECT city_id, city, country_id, inhabitants, last_update from city
The data source will create variables like ${city_id}, ${country_id}, ${country_id}, ${inhabitants}, ${last_update} which will be available inside the current pane or table and inside all children panels and tables. We can create then a label with text:
The city is ${inhabitants<1000?'small':'large'}. It has ${inhabitants} people.
The label text is evaluated, the ${...} will be evaluated as Java expression and generate for example:
The city is small. It has 800 people.

Beside data source scripts, the panels, tables and the form itself can have:

Master-Detail Report

Panels or tables can be embedded one in another. Look the master-detail below. The detail pane data source is using country_id=${country_id}, where the variable country_id is provided by the master pane.
Create database master-detail form

Input Variables

Beside labels, forms may contain also text fields, buttons, radio buttons, charts, etc. Buttons can open other form pages and send data as variables. In the target form the variables are received as input variables.
Database form input variable
Input variables can be edited. A data type can be set and a error message for the case the variable should be always present and is missing.
Edit Input Variable
In the next image there is a form with two labels, text fields and a button. The button has set as action on the right to open the index page when clicked. This will send automatic the variables email and password.
Form button

Short Introduction in Groovy

Groovy is pure Java plus closures. It is known for Gradle (alternative to Maven). All Java simple operations are supported:

int id = 20;
def array = ["Groovy", "is", "the", "Best"]
def multilineString = """This is a
multiline String"""

// GStrings replace variables inside. This will output 'Age=21'
println "Age=${age}"

// Iterate an result set
sql.eachRow("SELECT f.film_id, f.title FROM"){ r ->
   println r.film_id

Groovy Scripts

The forms and reports are making use of scripts to implement the afferent logic. Two languages are supported: JavaScript and Groovy. For both we set a variable 'sql' as the connection to the database. Scripts will also receive all variables send by the previous form ( if any ). The output of print commands can be read in Help / Output Logs.

Sample Groovy script which modifies data in a table

if ( 'edit'.equals( operation )){
  println "Going to update ${actor_id} ${first_name}"
  sql.executeUpdate( "UPDATE SET first_name=?, last_name=? WHERE actor_id=?",
    [first_name, last_name, actor_id] )

Same example in JavaScript

  print("Operation=" + operation);
  if ( "edit"== operation ){
    print("Updating actor_id=" + actor_id + " first_name=" + first_name + " lastname=" + last_name );
    st = sql.prepareStatement("UPDATE SET first_name=?, last_name=? WHERE actor_id=?" );

Groovy scripts can be used in Data Sources as well

  SELECT f.film_id, f.title, f.description
  FROM f limit 5
  film_id= r.film_id
  title= r.title
  description= r.description

Deploy Tomcat WAR

The forms applications can be deployed as Tomcat war applications. Like this you can export the created application under any tomcat instance. For this you will need: