Geocortex Workflow extends what you can do with your Geocortex and Web AppBuilder for ArcGIS applications. A key component of this is providing a way to efficiently connect spatial data in a GIS application with non-spatial data stored externally.
In this week’s tech tip video, we show you how can pull data from a SQL server to display in Geocortex Workflow without replicating the data. This streamlined process allows you to get a complete picture of your assets, enabling better informed decisions.
Video Transcript
“Hi everyone, my name is Patrick Fingler. I work on our Technical Marketing team, and in this tech tip video I’m going to show you how you can display SQL server data inside of Geocortex Workflow. Let’s take a look!”
In this tech tip video, I am going to show you how you can display data from SQL databases when you select features on the map, using Geocortex Workflow. Here I am in Web AppBuilder and I’ll just show you the workflow right off the bat, and then we’ll dive in and see how it’s all put together.
Here I added a client-side workflow as a widget to my Web AppBuilder instance. It’s an extremely simple workflow: all it’s asking you to do is to enter the facility ID of one of my hydrants. If I click submit, then it’s going to pan to that hydrant and now we are actually displaying related inspections. This inspection information is actually being pulled dynamically from the SQL server, but we also do have an ArcGIS relationship, so if you do want to quickly confirm that we are pulling those three inspections, here they are. This data here is being pulled from a SQL server and being populated in this form. So, how do we accomplish this?
You can see I’ve installed the on-premise version of Geocortex Workflow designer. If I navigate to the info tab, you can see this is a client workflow, so that means that I can add it to Web AppBuilder and I can essential display forms and present information to my end users.
I have an initial form here asking the user to enter their facility ID. You can see that this says ‘textBox1’ and it’s ‘form 1’. I added some graphics to the map to select that hydrant, and then this is where I run a server-side workflow to then query that SQL database. This is the URL of my server workflow, which is this workflow here on the right. Then I’m passing in some arguments. I’m passing in the name (this could be anything you want), and I’m passing in the ‘textBox1’ value. I can zoom in a little bit here to show you what that looks like.
Essentially, the workflow then kicks off the server workflow and what this does is it gets the workflow inputs and then, I’m using the ‘Run SQL Query’ activity. This requires a provider name, so here I entered in ‘Microsoft SQL Server’. I added a connection string and you can see that I greyed out some of these items because some of this is sensitive information. But here you can see that this requires a data source. In this example: an initial catalog to production, integrated security is false, this is my User ID and also a password. The activity for this shows you how to configure those connection strings.
Last but not least, I need a command text here. This is where I’m performing my query in SQL server. I’m doing a ‘SELECT’ statement to select the facility ID, hydrant condition, inspector, and last update. These are attributes within my inspections table in SQL server. ‘FROM’ – this is the table I am querying, ‘WHERE’ the facility ID is ‘LIKE’ the name that I pass it. This name is essentially configured in the parameter section here. Again, this can be anything you want, I just happened to call them both ‘names’. But this could be where ‘Facility ID = $getWorkflowInputs1.inputs.name’.
Now this ‘.name’ is the name that we sent from the client-side activity. That’s going to run and perform that query. The next step is setting up the workflow output. Here I’m just giving it a name called ‘SQL query output’ – this could be anything you want. I’m passing back the data table from the SQL query activity to my client workflow. I’m actually running a log activity so we can see what that looks like. Then I’m essentially creating a variable called ‘Inspections’ and it is taking the results of my SQL query out.
This is a data table that we passed back to our client-side workflow. This has to match the name that you set in your ‘Set Workflow Output’ activity. Then what I’m doing is I have another form here that is in the item picker I’ve added. If I edit the item’s sub-workflow here, you can see I’m using ‘Get From Element Items From Collection’ (that’s important). I’m passing in the variable that I created, so this is my ‘inspection.result’. Alternatively, you could just do your run workflow ‘output.SQLqueryout’.
Here’s the variable that I created, and I’m just creating a label using markdown. I’ve bolded my ‘condition,’ I’m passing in the hydrant condition attribute from the SQL server table. Then I’m adding in some new lines to break it up. That’s pretty much it. That’s how we are able to display those inspections in that form.
Now if I quickly run this once more, open up the console, clear that and run it, we’ll see that once the server-side workflow runs, it returns this SQL query out. This is being passed from the service-side workflow back to the client-side workflow. Here you can see these are the four attributes that I selected to be returned.
That’s how you can display data from SQL databases when you select features on the map, using Geocortex Workflow. Bye for now!”
Want to learn more about Geocortex Workflow or see it in action? Click the button below for more information or to request a demo.
Contributors