Lighten your LiquidOffice Development Load

If you are developing forms in LiquidOffice, chances are form owners have asked you to include drop lists to ensure data integrity in your backend database or repository.  And just as likely they have asked for drop list value changes AFTER the form has been published. Or sometimes there is a business need to accommodate frequent drop list value changes. In many cases it is advantageous to allow users to make the drop list value changes themselves.   One approach is to use a LiquidOffice drop list maintenance form, and restrict access to this form to the appropriate power users. Whenever the need for a change arises the maintenance form is available.

To get started, create a database to house the drop list values.  Each field in the table will correspond to a drop list on your form.  Populate these fields with the initial values the form owner has provided.

Next, create a connect agent to your database.  In LiquidOffice Management Console, highlight the Connect Agent icon then select File/ Add.  The wizard walks you through steps where you name the connect agent and the type (SQL DataBase Read/Write in my case). Select a JDBC driver and modify the JDBC URL to reflect your hostname and database name.  Provide credentials, select ‘LookUp Only’, and you are done.

When designing your form’s drop list, go into the properties to select Control type: drop list and List source: dynamic (from database).  You can then select the connect agent, table and column and you are set.  If you want the display column to be different than the storage column, click Storage Column and select another column from the drop list. An example: Display = “Arizona”,  Storage =”AZ”.   Remember you must publish the form to confirm the drop list is correctly populated.

Now create another LiquidOffice form to perform maintenance on the drop-list table.  One approach is to have this form start with an ‘Operation’ radio group where the choices are ‘Browse, Add, Change, or Delete’.  Next you create a drop list using the steps in the previous paragraph and finally place an entry  field labeled ‘edit’ beneath this drop list.

Now some scripting will allow us to control the table we just created. First up is to create code to delete, add, or modify records in the database.  Here we’ll build corresponding DELETE, INSERT, or UPDATE SQL statements,  then create code to make a db connection.  Finally we execute the code on the server side OnSubmit entry point. Some examples follow.

If deleting an entry your statement might look like:

var sSQL = “DELETE FROM “” + TABLE_NAME + “” ” + “WHERE (“DOC_CLASS” = ‘” + DB_Doc_Class +  “‘ AND “FIELD_NM” = ‘” + DB_Field_Nm + “‘ AND “TITLE” = ‘” + DB_Title + “‘ AND “DISPLAY_COLUMN” = ‘” + DB_Original_Storage_Value + “‘)”;

If adding an entry, your SQL Insert statement code may look like:

var sSQL = “INSERT INTO “” + TABLE_NAME + “” (” + “”DOC_CLASS”,”FIELD_NM”,”DISPLAY_COLUMN”,” +  “”STORAGE_COLUMN”,”TITLE”,” +  “”RELATEDVALUE”,”SEQUENCE_NUM”) ” + “VALUES (” +  “‘” + DB_Doc_Class + “‘” +  “,'” + DB_Field_Nm + “‘” + “,'” + DB_Display_Column + “‘” +  “,'” + DB_Storage_Column + “‘” + “,'” + DB_Title + “‘” + “,'” + DB_Related_Value + “‘” +   “,0” + “)”;

Code to execute the SQL statement  as follows:

var ReturnCode = con.executeUpdate( sSQL );

Extend this core functionality to cover any drop list field on your  form(s).  Liquid Office drop list values are now TRULY dynamic.  Your users can make changes to their drop list values without a call to the original form developer… YOU!

 Note the edit field below each droplist…