Sunday, September 24, 2017

A Vision for a Simple Form Definition Language

In my travels as a software product manager I've experienced repeated requests to create basic form entry into a SQL database for reporting. Typically these requests don't have a significant budget behind them and may involve a smaller number of users. Pointing non-technical staff to Google Forms or similar isn't that helpful and ideally we'd like to have the data easily incorporated into enterprise reporting (Office 365 environment in particular).

Products like QuickBase have been used in the past, but aren't integrated well in our O365 environment and have significant licensing costs. There are other products (PHP/MySQL solutions seem to be common) for easy specification of forms using WYSIWYG editors, but I'm looking for something that lets me define the input form and related SQL tables/columns directly.

I've recently been impressed with a SQL Report Builder interface in an application we use at work (Genius SIS). It provides a quick way to create reports in raw form that can be exported to Excel and used in daily processes. With a SQL Query Builder (and Excel or possibly Power BI or similar for more polished reporting) the reporting side can be handled. It's the quick definition of forms that remains a challenge.

The Office 365 (O365) Forms product seems to be aimed at surveys currently. O365 PowerApps may be a solution, but it has a significant learning curve and requires significant programming expertise. Developing with it in a production environment is also restricted to higher enterprise licenses. I'd also like to stick with standard web technology so we don't have to install anything on mobile devices.

Ideally I'd like to connect it all up in an Office 365 environment with a Azure SQL database, possibly using Azure Functions to extend capability. This is an evolving vision so I'm tracking my thoughts here in case someone runs across this and is interested.

Doing some research, there is very little in the way of non-XML user interface markup options, for example PlantText (https://www.planttext.com)


There are surprisingly (to me at least) just a couple of established options for XML-based User Interface markup languages. A good summary can be found by searching for "A Review of User Interface Description Languages for Mobile Applications" (https://www.thinkmind.org/index.php?view=article&articleid=ubicomm_2016_6_10_10063) which in addition to my own research leads me to two primary options: XAML and XUL. (Oracle also has FXML, but I'm not in that world.)

XAML is used by Microsoft. Plenty on the web. After looking at it, it's just too big to consider for the little project I have in mind. Of course it would be great to leverage the thought work and standard behind it. It's also in the Microsoft camp and would make sense for O365. Maybe for Version 2 (...) a XAML-based format might be considered.

XUL is used by Mozilla, though evidently it is now in the process of being phased out in favor of Web Components. Since I'm only interested in leveraging the thought work and syntax, and a very small subset at that, it seems like the best way to go.

A Google Docs Spreadsheet XUL Controls summary is a work in process at: https://goo.gl/jL5BfG

Here is a sample of a proposed SFML "Simple Form Markup Language"...


 <!--  
 Simple Form Markup Language (SFML)
 Based on a very small subset of XUL with extensions prefixed with sf_  
 https://developer.mozilla.org/en-US/docs/Mozilla/Tech/XUL  
 https://developer.mozilla.org/en-US/docs/Mozilla/Tech/XUL/XUL_Reference  
 The intent is to embed this within an environment (e.g. O365) as a way to specify very simple forms  
 bound to SQL tables. The scope of SFML is very limited, but it should be quick for proof-of-concept  
 or supporting lightweight processes that are being tried and can't justify significant tech investment.  
 -->  
 <sf_simpleform>  
 <!-- database connection string - not sure how authentication / permissions will work yet  
    since this will be an embedded technology, chances are it will be integrated with other  
       methods for authentication  
 -->  
 <sf_binddb>connection string to db</sf_binddb>  
 <!-- database used for reference tables - where control uses an index into a table -->  
 <sf_refdb>connection string to db for reference tables</sf_refdb>  
 <vbox>  
  <label value="Last Name" control="LastName" />  
  <textbox id="LastName" placeholder="last name" size="10" min="2" max="20" sf_bindtable="students" sf_bindcolumn="lastname" />  
  <label value="Status" control="Status" />  
  <menulist size="10" sf_bindtable="students" sf_bindcolumn="status">  
   <!-- the selected attribute is only relevant if the record is new, otherwise the value from the bound field is used -->  
   <menuitem label="UNKNOWN" value="-1" selected="true"/>  
   <menuitem label="ACTIVE" value="0" />  
   <menuitem label="BLOCKED" value="1" />  
   <menuitem label="GRADUATING" value="2" />  
   <menuitem label="WITHDRAWN" value="3" />  
  </menulist>  
  <!-- this field value is indexed into a table "GraduationPath" and column "GraduationPathIndex" -->  
  <label value="First Year as 9th Grader" control="Cohort" />  
  <menulist sf_reftable="GraduationPath" sf_refcolumn="GraduationPathIndex" size="10" sf_bindtable="Students" sf_bindcolumn="GraduationPathIndex" />  
  <!-- !!! radio buttons support indexing into a table in the future? -->  
  <label value="Eligible for re-enroll:" control="reenroll" />  
  <radiogroup id="reenroll" sf_bindtable="Students" sf_bindcolumn="EligibleForReEnroll">  
   <radio id="-1" label="Unknown" selected="true"/>  
   <radio id="0" label="No" />  
   <radio id="1" label="Yes" />  
  </radiogroup>  
  <checkbox label="Returning student:" checked="false" sf_bindtable="students" sf_bindcolumn="ReturningStudent" />  
  <label value="Projected graduation date:" control="graddate" />  
  <datepicker type="grid" value="today" sf_bindtable="students" sf_bindcolumn="ProjectedGraduationDate" />  
  <!-- contrived example for a time, but it could be a useful entry type -->  
  <label value="Time of birth:" control="birthtime" />  
  <timepicker id="birthtime" hideseconds="true" is24hourclock="false" value="now" sf_bindtable="students" sf_bindcolumn="TimeOfBirth" />  
 </vbox>  
 </sf_simpleform>  

No comments: