Displaying SQL Server Data in Geocortex Workflow

[Music] hi everyone my name is Patrick Singler 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 geo cortex workflow let's take a look okay so in this tech tip video I'm going to show you how you can display data from SQL databases when you select features on the map using geo cortex workflow so here I am in web app builder and I'll just show you the workflow right off the bat and we'll dive in and see how it's all put together so here I've added a client-side workflow as a widget to my web app builder instance it's an extremely simple workflow all it's really asking me to do is enter the facility ID of one of my hydrants I think it's it's it's one of these hydrants in the area here if I click Submit then it's going to pan to that hydrant and now we're actually displaying related inspections now this inspection information is actually being pulled dynamically from SQL Server but we also do have an ArcGIS relationship so if we do want to quickly confirm that we are pulling those three inspections here they are but this workflow this data here is actually being pulled from SQL Server and populated in this form so how do we accomplish this here you can see I've installed the on-premise version of geo cortex workflow designer so if I navigate to the info tab here you can see this is a client workflow so that means I can add it to web app builder and I can I can essentially display forms and present information to my end-users so what this workflow does is it I've got an initial form here just asking the user to enter their facility ID you can see this is textbox 1 and it's form 1 I add some graphics to the map to select that hydrant and then this is where I actually run a server-side workflow to then query that SQL database so is the URL of my server workflow which is this workflow here on the right and then I'm passing in some arguments so I'm passing in the name this could be any anything you want you can name this it probably make more sense what I called this facility ID or something like that but this example I just said name and I'm passing in the textbox one value I can maybe zoom in a little bit here just just to show you what that looks like and so essentially then the workflow kicks off this server workflow and what this does is it gets the workflow inputs so it gets whatever I send it to it and then I'm using the run SQL query activity this requires a provider name so here I've entered in Microsoft SQL Server I've added a connection string you can see I've kind of grayed out some of these items just because some of this is sensitive information but here you can see we require a data source in this example an initial catalog I'm navigating to my production one integrated security is false this is my user ID and also a password and you can are the active before this kind of shows you how to configure those connection strings last but not least I need a command text here so this is where I'm actually performing my query in SQL Server so I'm doing a select statement so select facility ID hydrant condition inspector last update these are attributes within my inspections table in SQL Server from this is the the table that I'm querying where the facility ID is like the name that I pass it so this @ name this essentially is configured in the parameter section here so this is again this can be anything you want it I just happened to make it but them both called names but this could be where I don't know where facility ID again equals my get work loading puts dot name now this dot name is the name that we sent to it from the client-side activity so that's going to run and perform that query and if we kind of so then the next step is setting the workflow output so here I'm just giving it a name called SQL query output this could be anything you want and I'm passing in a value so I'm passing back the data table from this SQL query activity to my client workflow so it's running I'm actually running a log activity so we can look what that we can see what that looks like and then I'm essentially saying I'm creating a variable called inspections and it's just essentially taking the results of my SQL query out so it's this is a data table that we've passed back to our client side workflow and this has to match you know the name that you set in your you know send workflow set workflow output activity then what I'm doing is I've just got another form here it's an item picker that I've added if I edit the item sub workflow here you can see I'm using get form element items from collection that's important I'm passing in the variable that I created so this is my inspections dot results alternatively you can just do you know your run workflow output dot SQL query out or whatever but here's this is the variable that I created and here I'm just creating a label using markdown so I've bolded my condition I'm passing in the hydrent condition attribute from the SQL server table and then I'm adding some new lines to kind of break it up and that's that's pretty much it I'm setting that and then that's how we're able to display those inspections in that form now if I just quickly run this once more and open up the console and let's just clear that and run it you'll see once the server-side workflow runs it returns this SQL query out so this is being passed from the server-side workflow back to the client workflow and here you can see these are the three attributes that I've or four attributes I guess that I've selected to be returned so yeah that's essentially how you can display data from SQL databases when you select features on the map using geo cortex workflow bye for now [Music]

Channel: Geocortex

Views: 498

Rating: 5 out of 5

Keywords: Geocortex, Geocortex Workflow

Id: 7QXI9teWxuA

Length: 7min 24sec (444 seconds)

Published: Fri Aug 16 2019