Web Viewer Portal
A Different Kind of Portal for Displaying Data
The Web Viewer Portal was introduced in version 1.29.
SQL Explorer can be used to build a variety of queries that generate all sorts of different types of results. This makes using a traditional FileMaker Portal, with fixed column widths and data types, less than ideal. We thought we would do some research to see if there was something we could do using a Web Viewer with some HTML 5 and Javascript techniques to display our results in a new way. We wanted to find something that would allow the user to resize the column widths as well as load initial widths when we ran the query that made sense.
We ended up using the JQGrid plug-in and found it to be a great fit for the web viewer. You can check out the Web Viewer Portal in action here. Not only did we get our column resizing, but some great additional functionality as well including:
- Dynamic Column Sorting
- Ellipsis Display (indicating truncated value)
- Dynamic Column Re-Ordering via Drag and Drop
You can also view an expanded view of your results in a full browser window by exporting the Data URL and having FileMaker open it in your default browser. This view also adds additional search functionality where you can refine your results and copy and paste the search back into your SQL Query. It's also great for debugging as you can use your browsers debug tools that aren't available in the Web Viewer.
How It Works
HTML Template On start-up, SQL Explorer creates a Data URL Template in HTML and stores it in a global field (SQLWizardHome::jsCodeCache). During this routine, the external references for the Javascript and CSS are exported to the user's temp directory and the HTML references to the temp directory are written to the HTML template like this.
<script src="file:///private/.../jquery.js" type="text/javascript"></script>
Tokens A few of the references in the HTML are set with placeholder values or Tokens. These values are determined when the query is run and substituted into the Data URL before loading it into the Web Viewer. The Tokens are:
- <<TIME>> The execution time of the query so it can be displayed in the Web Viewer.
- <<RESULTS>> The execution time of the query so it can be displayed in the Web Viewer.
- <<MYDATA>> The query results as CSV.
- <<SCROLL>> The type of scrolling to use based on the size of the found set.
- <<COLNAMES>> The column names or headers as an array.
- <<COLMODEL>> Additional column information such as alignment, data type, etc.
- More information on theses last three tokens is in the JQGrid documentation.
CSS The JQGrid plug-in uses themes built with the jQuery UI Theme Roller. We've created a custom theme that fits nicely with the FileMaker Onyx theme the Explorer uses. If you want to use a different theme you can create it using the Theme Roller and then download it. Once downloaded, you can go the the SQL Explorer Layout css and import the CSS into SQL Explorer and change the theme of the web viewer.
In a few cases we've made some small changes to the CSS to have it fit a little better on the layout and look more "FileMakery." We wanted to preserve the ease of importing a Theme Roller theme, so rather than messing with the Downloaded CSS, we've added a small section in the HTML template to override the CSS. If you want to make any additional CSS changes or review the ones we've made, then you can do so in the script Configure CSS Overrides.
Javascript We use 7 Javascript Libraries that are loaded into the temp directory at start-up.
- jQuery v1.90
- jQuery Layout v1.30
- jQGrid v 4.52
- grid.locale-en (Localized code of jQGrid)
- jQuery-ui-Custom (Built by jQGrid folks)
- CSVtoJSON (Allows us to pass CSV as a token and convert to JSON within Javascript)
- json2 (Used in CSVtoJSON)
As with the CSS, we don't want to modify these libraries themselves, so when it's necessary to modify this code, we do substitutions to our exports so if the libraries are updated, then nothing should break. The Javascript substitutions are performed in the script Export JS to temp directory.
jQGrid Plug-In The jQGrid plug-in is an open source jQuery Library with a ton of functionality that we're only scratching the surface with here. Check out their demo page to get a sense of what this thing can do.
If you're interested in experimenting with these options then review the plug-ins documentation, Make Sure You Have a Back-Up, and start experimenting! The script Configure jQGrid is where you can make these changes to the Web Viewer. You will need to re-run the Upon Opening script to see these changes take effect.
