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>  

Sunday, April 30, 2017

Experimenting with AWS Lambda

An inexpensive Kindle book ($4) that I found helpful for structuring my learning:

AWS Lambda: A Guide to Serverless Microservices

Some quick notes, but mostly Google search and AWS documentation otherwise.

After downloading the AWS CLI configure it:

> aws configure
AWS Access Key ID [None]: accesskey
AWS Secret Access Key [None]: secretkey
Default region name [None]: us-east-1
Default output format [None]:

Change the output format when using CLI by using the --output parameter:

> aws lambda get-account-settings -output text|json|table

AWS CLI Reference Page: http://docs.aws.amazon.com/cli/latest/index.html

To date I have not found a page (or three) quick reference in PDF format.

Continuing experimentation:

https://gofore.com/en/going-serverless-with-amazon-s3-and-lambda/


Using the Excel Sunburst Chart for Visualizations

Though not a new concept, Office 365 (Excel 2016) has a Sunburst Chart type (a.k.a. Multi-level Pie or Donut Chart) that is very handy for presenting hierarchies. Here are some ideas to consider:

A series of project portfolios:



A phone "wheel" like a phone tree (recently used this idea myself):




And I will credit the idea (thank you Chris Baugh) to an application at work representing questions on an assessment and how they relate to Common Core:




There is more sophisticated color formatting available in a variety of JavaScript libraries such as FusionCharts and HighCharts, but having the chart type in Excel makes it very approachable for those wanting an ad hoc visualization that doesn't require any programming.

Saturday, March 04, 2017

DOT (GV) Graph Definition Language

Playing around with graphing tools and ran across DOT (.GV) which meets the needs for simplicity that I have with a current side project. Just using this page to collect information in case it's useful to someone else.

GVEdit (Graphviz)
http://www.graphviz.org/

Helpful StackOverflow posting
http://stackoverflow.com/questions/6344318/pure-javascript-graphviz-equivalent

Graphviz in the Browser
http://www.webgraphviz.com/
Based on this JavaScript library
https://github.com/mdaines/viz.js

Google Charts Image Charts API also supports DOT for now... (deprecation notice)
https://developers.google.com/chart/image/docs/gallery/graphviz

As long as Google supports it, this is a no-brainer way to show DOT scripts in the browser.

Using Google Charts Image Charts API:

 https://chart.googleapis.com/chart?cht=gv&chl=digraph{node1[label="Root%20Node"][shape=box];node1->node2[dir=both][label="%202%20inches"];node2->{node3%20node4%20node5}[color=green]}&chrs=300x300  


This link is currently available to test DOT scripts using the Google Charts Image Charts API:
http://sandbox.kidstrythisathome.com/erdos/

DotViewer (Android App)
https://play.google.com/store/apps/details?id=com.woiapp.dotviewer

Some DOT shortcuts
Main Documentation Page
http://www.graphviz.org/Documentation.php
DOT Guide PDF Version
http://www.graphviz.org/pdf/dotguide.pdf
Polygon Shapes
http://www.graphviz.org/content/node-shapes#polygon
Attributes [attr=value]
http://www.graphviz.org/content/attrs
Special Characters
http://www.graphviz.org/doc/char.html
Colors Names
http://www.graphviz.org/content/color-names
Further related research into plotting dot language in the browser:

vis.js
http://visjs.org/

Import Data in the DOT Language
http://visjs.org/docs/network/#importDot

Supported shapes in vis.js (DOT has many more that are not currently supported): database, circle, ellipse, box, image, text, dot, star, triangle, triangleDown, square, icon

Useful comparison of Graph Libraries
https://anvaka.github.io/graph-drawing-libraries/#/all




Thursday, January 19, 2017

Parsing UserForm and Code Modules in VBA

Some research into what's possible with VBA and code generation of JavaScript for a proof-of-concept project.

In order to parse a UserForm and VBA code modules, first enable Microsoft Visual Basic for Application Extensibility in the Tools / References feature:



For this example create a simple UserForm:



The Parse button should be wired up to call the Parse() subroutine in the Module1 code module. The entire source will be listed in the worksheet when this Parse routine is run.

Here's the code to place in Module1:

1:  Option Explicit  
2:    
3:  Sub Macro1()  
4:    Form1.Show  
5:  End Sub  
6:    
7:  Sub Parse()  
8:    Dim cmpComp As VBIDE.VBComponent  
9:    Dim cCont As Control  
10:    Dim lRow As Long  
11:    Dim lLine As Long  
12:      
13:    Sheets("Sheet1").Activate  
14:    ActiveSheet.UsedRange.ClearContents  
15:    
16:    lRow = 0  
17:    lRow = SetRow(lRow, "Show UserForm and VBA Code Modules")  
18:    lRow = SetRow(lRow, "Workbook " & ThisWorkbook.Name & ", Protected = " & ThisWorkbook.VBProject.Protection)  
19:    For Each cmpComp In ThisWorkbook.VBProject.VBComponents  
20:      lRow = SetRow(lRow, "Component Type = " & cmpComp.Type & ", Name = " & cmpComp.Name)  
21:      Select Case (cmpComp.Type)  
22:      Case vbext_ct_MSForm:      ' UserForm  
23:        
24:        ' Form1 is hardcoded here. If you include this code to traverse the  
25:        ' controls in the UserForm Code itself you can use "Me" instead of "Form1"  
26:        ' if you know of a way to parse controls in a UserForm using a string variable  
27:        ' or better yet, from another worksheet, please let me know  
28:        
29:        For Each cCont In Form1.Controls  ' Form1 here, could use "Me" in UserForm code  
30:          lRow = SetRow(lRow, " Control type = " & TypeName(cCont) & ", Name = " & cCont.Name)  
31:        Next cCont  
32:          
33:      Case vbext_ct_StdModule:    ' VBA code  
34:        lRow = SetRow(lRow, "Lines of code = " & cmpComp.CodeModule.CountOfLines)  
35:        For lLine = 1 To cmpComp.CodeModule.CountOfLines  
36:          lRow = SetRow(lRow, Format(lLine, "00#") & ": " & cmpComp.CodeModule.Lines(lLine, 1))  
37:        Next lLine  
38:      Case vbext_ct_Document:     ' worksheet  
39:        ' not UserForm or Code so other type of worksheet  
40:      End Select  
41:    Next cmpComp  
42:  End Sub  
43:    
44:  Function SetRow(lSheetRow As Long, sLine As String) As Long  
45:    SetRow = lSheetRow + 1  
46:    Cells(SetRow, 1).Value = sLine  
47:  End Function  
48:    

Here is the partial output when running Macro1:


What I'd still like to be able to do:

  1. Reference the UserForm with a variable instead of hardcoding. I tried using UserForms(cmpComp.Name), but no go.
  2. Reference the UserForm from a different (loaded) workbook. I think the issue might be that the Visual Basic Editor needs to have the UserForm loaded in order to traverse the controls.


Saturday, January 14, 2017

Converting Excel VBA Prototypes to Production Reports on the Web

I'd previously explored how to move Excel VBA prototypes into a production environment and did not find a satisfying path with Microsoft technologies.

An idea for an approach that I may try as a proof of concept:


1. The Excel VBA Macro would need to be created with a specific set of coding rules and form controls. This is not intended to be a general purpose macro converter and is just being designed for some of the practices I have used over the years. It might serve as a basis for a broader converter down the road. There is still a lot of code written in Excel VBA and it's not a market that is attracting a lot of developer tool competition. It's worth looking into building a set of libraries in VBA similar to how the Google Apps Script (GAS) functions are organized for possible leverage there some day, but the intent is not to put one prototype product into another prototype so GAS is not the target of the code generation in step 3 below. A useful site related to VBA and GAS. As I think about it, if my situation allowed for using Google for authenticating users, the GAS environment is fine for low volume usage (or scale for that matter). We just happen to be a multi-vendor environment and Google is not a primary vendor for us.

2. For Excel VBA Macros used to create reports manually it's often not practical to have end users execute it directly for complexity / security / etc. reasons. The easiest solution in a non-production environment is to distribute the reports instead of having end users access the capability to produce them directly.

3. Having written code generators in the past, they are not as challenging as you might think as long as you restrict their flexibility. Node.js is a fertile environment these days and provides a number of packages for manipulating Excel worksheets. Bootstrap is just one of many options, but I'll use it for the POC because I've been experimenting with it recently related to the AppStudio product.

4. Once the code is generated, it will probably be necessary to add enhancements to pull data directly from SQL databases and/or APIs instead of from manually extracted Excel workbooks. The goal is to move as much functionality as reasonable from Excel VBA into modern JavaScript and then enhance. Features like login / authentication don't fall into the domain of Excel VBA so would need to be provided in the web production environment.

This is labeled "low volume usage" because we're taking prototype code and placing it in a production environment as inexpensively and safely as possible. If a higher volume usage or a more sophisticated approach is needed, plan to spend more effort. For example, we'll probably invest and have development staff create an SSIS/SSRS/.NET solution consistent with much of our production environment.

Fredrick Brooks of course has a lot to say about this in The Mythical Man Month (Chapter 11: Plan to Throw One Away). And for another view check out Joel Spolsky on the perils of rewriting code from scratch.

Saturday, December 17, 2016

How to Move Excel VBA to a Production Environment?!

I was thinking of a quick-and-dirty solution for moving VBA to a server, but found it was essentially shot down by Microsoft a long while ago:

Considerations for server-side Automation of Office
https://support.microsoft.com/en-us/kb/257757

I have some other ideas of using an Excel VBA metaphor (in essence a stack of sheets) within a Node.js module, perhaps implementing a set of functions similar to Google Apps Script (GAS) to reduce the learning curve. A simple a way to move Excel VBA (which is very productive in a corporate Excel environment) to a web front-end for low volume self-serve production use is needed.

Any idea has dozens if not more people around the world thinking about it - maybe someone has already solved this problem and I can just use it?

There are postings on Stack Overflow to figure this out, but consolidated here for quick review. Here's an example of calling an Excel VBA Sub and Function from PowerShell. 

PowerShell code:

1:  $objExcel = new-object -comobject excel.application  
2:  $sFile = "c:\users\dsides\sides-work\powershell\TestCall.xlsm"  
3:  $objWorkbook = $objExcel.workbooks.open($sFile)  
4:  $objWorksheet = $objWorkbook.worksheets.item(1)  
5:  $objExcel.run('TestSub1', 'Word1 ', 'Word2')  
6:  $sRetVal = $objExcel.Run('TestFunc1','Word1 ', 'Word2')  
7:  Echo $sRetVal  
8:  $objExcel.quit()  


Excel VBA code:

1:  Option Explicit  
2:    
3:  Sub TestSub1(sString1 As String, sString2 As String)  
4:    MsgBox (sString1 & sString2)  
5:  End Sub  
6:    
7:  Function TestFunc1(sString1 As String, sString2 As String) As String  
8:    TestCallFunc = sString1 & sString2  
9:  End Function  

Looked at a few ways to put PowerShell/VBA behind a web front-end, but it's a bit too hacky and feels like it would be too fragile for a production environment. Here are a couple of links (not hard to find):

http://systemfrontier.com/powershell

http://www.poshserver.net/index.html