Linked data: from a workflow to a report
?Greetings!
I have an address point map layer with a data link containing communitites.Both data sources are linked together via a zipcode field.
My workflow selects an address point, displays the FULLADDR & ZIPCODE fields from the address point data, along with the community NAME and ZIP5 fields from the linked data, in the Results List. Good so far.
And I can bring the FULLADDR & ZIPCODE fields from the address points into my report with the usual FeatureSetToDataTable --> Assign --> AddToDataSet routine.
But how do I get the linked data NAME & ZIP5 fields from my selected address point into their own data table so I can bring them into a subreport? I assume I need to run another Assign and AddToDataSet, but exactly how?????
I need very obvious hints!
FeatureSet queryResult =“FULLADDR, ZIPCODE” fields from the address points
and “NAME, ZIP5” fields from data link ZIPCODES_DataLink
Thanks so much!
0
-
What I would do is this: - Get the address points (Querytask, Featureset to DataTable, Add to Dataset)
- Get the related data (Querytask, Featureset to DataTable, Add to same Dataset)
- Add a relation to the dataset. This is a bit tricky; you need the Invoke Method activity: Set TargetObject to youDataset.Relations and MethodName to Add. Add two parameters, both of type DataColumn. The first one is the ZIPCODE column of you address point dataTable, e.g. addressesTable.Columns("ZIPCODE"), the second one is the ZIP5 column of the linked data (lnkedTable.Columns("ZIP5")
- Note that both columns should have the exact same type. If one is a string field and the other is numeric, it won't work
- Search the Report Designer help file for the topic 'Include a Subreport in a Report'
- The report will use the relation in the dataset to determine which of the linked records it needs to display for every address point.
0 -
Berend,
Thank you for responding!
I'm afraid I'm not quite sure how to query for the related data --
Do I query the actual address points service a second time, or do I need to query the linked data table for the ZIP that matches the ZIPCODE in my selected address point??? Sorry, this is confusing to me!0 -
I’m querying my address points to match the user input address (below).
If my DataLink is called “ZIPCODES_DataLink,” located C:\Program Files (x86)\Latitude Geographics\Geocortex Essentials\Default\REST Elements\Sites\Elections\Viewers\HTML5\VirtualDirectory\Resources\ZIPCODES.accdb , how exactly do I query ZIPCODES_DataLink for the ZIPCODE that matches my address point?
Any help is much appreciated!
_img_ alt="User-added image" src="https://latitudegeo--c.na53.content.force.com/servlet/rtaImage?eid=907f2000000kAGB&feoid=Body&refid=0EMf2000000fyYo" _/_img_0 -
Yes, you need to do a separate query on the related table. If this table is available through a mapservice, you can use a QueryTask activity with the Query Service URL set to that table.
If the two tables are related as an ArcGIS relationshipclass, you can use the RelationshipQueryTask activity instead, followed by a RelationshipResult to FeatureSet.0 -
Sorry, I missed the part where you say the linked data is in an Access database. In that case, you would have to use the SQL Query activity. This will return a datatable object that you can add to the dataset. I am not aware of any other way to query data that is defined in an Essentials data connection.
BTW, are you sure you want the database inside the Virtual Directory? This location is meant for downloadable content, such as images used in the viewer. Having a database there is unusual.0 -
Berend,
You raise a timely topic: the best location for a data table.
I am working with a very large table containing assessor's tax parcel data. We initially (currently) went with an external ACCESS database and then created a data connection in our Essentials site. But I don't know that this is the best, most efficient option.
Do you have an opinion, or can you steer me towards any existing discussions, as to what might be the "best" (efficient and easy to update) way to consume a very large data table within Essentials? 1) Inside/within a map service, either joined or related to the parcel layer (we prefer not to have this data as attributes of the feature class, but rather just joined or related); or 2) as an external table, then added as a data connection in an Essentials site?
Your input is appreciated!0 -
As usual: It depends.
If the size of the data is relatively small, and it is readonly, you might as well store it in an Access database. We have even used CSV files with linked data once, because that was the format we received the data in and it was just the quickest way...
(My point was that your data is now in a web accessible location, so a clever user *might* download the entire file. There's ususally no reason not to store data in, say, C:\Data. You just need to make sure the Geocortex process has read/write permissions on the directory the Access database is in)
Some reasons not to use Access, but a 'real' database such as SQL Server, Postgresql or Oracle:- The size of the data is large. The old mdb format was notoriously slow if larger than 500MB. I'm not sure if that's still the case with accdb though.
- The data will be accessed by multiple users
- The data will be modified
- Other applications need to access the same data
- You have a cluster of Geocortex machines that all need to share the same data (it appears to be surprisingly difficult to use an Access db on a network share)
- You need some kind of user authentication on the data. Although that can be achieved with file permission on the Access db too, it's easier to control in a database.
Linked data vs ArcGIS Relationship considerations:- Publishing that data as an ArcGIS service gives users an additional way of retrieving all the data, i.e. through the ArcGIS REST endpoint. If the data is sensitive, you may not want this.
- If the data is in a database that's not accessible by ArcGIS, you have no choice but to use an Essentials data connection
- The SQL workflow activities are more flexible than the Query Task: you can use all SQL statements supported by the database. If you need complex queries, SQL is preferable over a Query Task.
0 -
Thanks for posting your workflow Lisa! I've never used the SQL Query activity and I needed to do the same thing as you and with your help I was able to get it to work! 0 -
So glad it helped, Chelsea! 0
Du måste logga in om du vill lämna en kommentar.
Kommentarer
9 kommentarer