Coming full circle with JavaScript and SQLexplorer

Having the opportunity to work on a project like SQLExplorer has been a highpoint in my FileMaker career. It was a great way to participate in the collective learning of the ExecuteSQL function by the community when it first hit the scene. But, it’s also been a great way to get a handle on the increasingly popular interaction of FileMaker Pro and JavaScript.

The Idea: JavaScript Portals for FileMaker

SQLexplorer has always displayed your query results in a “JavaScript Portal” in a web viewer , and this was born from a pretty simple requirement. Since you can choose different columns for your SQL query, having pre-formatted fields in a traditional portal often didn’t “look right.” We thought that with a “simple” JavaScript grid we’d be able to dynamically set column widths as well as apply some basic formatting, i.e. text floats left and numbers float right. We settled on using jQuery and the jQGrid plug-in for this. They’re  easy to set up, and being able to use Theme Roller provided us an easy way to keep the “portal”‘s theme matching the layout’s theme. With this, we were able to get a great looking grid that not only met are original requirements, but also had some great additional features that came with the jQGrid plug-in; notably, column sorting, changing the column order via drag and drop and a filtering tool. The end result ended up very polished, and to our great delight, several developers have re-engineered this technique for their own solutions.

The Limitation

As exciting as the JavaScript portal was, it made the limitations glaringly obvious. At this point in history, the FMP URL protocol was new (a byproduct of FMGo) and we didn’t have the ability to initiate action from our web viewer on local files. For SQLExplorer, this was OK: it was designed to display data, and it did that beautifully. However, for the developer trying to take this technique to the next level in a production FileMaker solution, this limitation made the portal pretty limited. This all changed when FileMaker 13v2 came out, which gave us consistent behavior for the FMP URL protocol across all deployments. Now we could write our JavaScript so it can actually fire FileMaker scripts, and we’ve seen an increasing number of web viewer based solutions hitting the scene.

New SQLexplorer

With this in mind, we decided to revisit SQLExplorer and release a version that demonstrates firing a script from the jQGrid plug-in, so hopefully folks will be able to take this technique to the next level.

onSelectRow Event

This actually turned out to be a pretty easy modification, because in the original JavaScript I had set up on onSelectRow event in the jQGrid plug-in. I had done this simply to give the ability to deselect/unhighlight a row. The natural jQGrid behavior is for a row to highlight when you click it, and stay highlighted until you click another row. I ALWAYS write my FileMaker routines to deselect/unhighlight the row if you click it again, and it was bugging me that the jQGrid wasn’t doing this, so I added:

onSelectRow: function (id, status, e) {
if(status===false){
$("#list4").jqGrid('resetSelection');}
}

to the Configure jQGrid script, which is the API for the jQGrid settings. This is a simple event that simply checks if the current row is selected, and if so, we reset it.

With this in place I just needed to add some code for my script.  I left my deselection in place, but if you are selecting, we add:

onSelectRow: function (id, status, e) {
if(status===false){
$("#list4").jqGrid('resetSelection');}
}
else{
var firstColumn = $(this).getCell(id,0);
var p = encodeURIComponent(firstColumn);
var url = "fmp://$/"&Get(FileName)&script=RowOnClick&param=" + p
window.location = url;
}
}

The id represents the row id and is passed from the event itself, you can then use the jQGrid method .getCell(rowId,columnIndex) to get the contents of a particular column/cell. Remember, JavaScript uses 0 based indexing, so a column index of 0 is the first column. Once we have that value, we encode it and create a url that will call a script with our cell value as the parameter. Once we have that url, we just call it using window.location. The script itself is just a simple dialog that shows the parameter, but once you’ve got the circle completing in a script, the possibilities are endless.

It’s important to note that onSelectRow is just one of many trigger events set up in the jQGrid plug-in, so this basic model can extend to all sorts of different triggers. Check out the jQGrid docs on this to see all it can do.

But I Need the ID!

It’s great to be able to pass the information in a row column back to FileMaker, but is FileMaker going to be able to do anything with it? My JavaScript portal doesn’t show the primary key, just the data I want to show, so returning the first column doesn’t really do me any good!

What I need is the primary key, but I don’t want to show that in the portal. What we came up with is an option to “Hide The First Column” checkbox in the query, so you can add a column with your id to the “front” so you can reference it, but not see it. We’re already dynamically defining the columns in the script Test Query, so we just added a little routine to see if we’re the first column, and if we are and the checkbox is checked, the first column will have it’s hidden property set to True.

Then you can set the id as the first column and return it to your script without messing up your display.

First column with the id is hidden, but can be referenced by jQGrid.

And that’s it!

We hope you’ll take some time to download the newest version of SQLExplorer and that it will be a useful tool for not just continuing to strengthen your SQL chops, but your JavaScript ones too!

The post JavaScript Portals for FileMaker: Clicking on Rows appeared first on SeedCode.

Source