Latest Contributions by RPGIS

Hi,   I am not sure if this is possible or not but I am trying to update a related table from another feature based on changes in the specified date field. Originally I was specifying all of the fields that needed to be inserted. Rather than specifying all of the fields directly; I would like to get all of the attributes based on matching fields. Here is what I have thus far. //_______""" Functions for modifying the related table """_________ // Get matching field names function GetMatchingFields( FieldListA, FieldListB ){ var MatchingFields = [ ] for ( var i in FieldListA ){ var fieldname = FieldListA[i] if ( Includes( FieldListB , fieldname ) ){ Push( MatchingFields, fieldname ) } } return MatchingFields } // Get field names from input features function GetFieldNames( InputFeature ){ var FieldNames = [ ] var FeatureInfo = Schema(InputFeature).fields for ( var i in FeatureInfo ){ Push( FieldNames , FeatureInfo[ i ].name ) } return FieldNames } //_______""" Specify Variables """_________ var FeatureClassName = 'Insert Featureclass Name' var TableName = 'Insert Table Name' var FeatureID = $feature.OBJECTID var InspDate = $feature.InspectionDate var IDField = 'OBJECTID' var DateField = 'InspectionDate' var Order = DateField + ' DESC' // Order the features by the date field descending var History = OrderBy( FeatureSetByName( $datastore, TableName ) , Order ) var Hydrants = $featureset // Get matching field names found in both features var MatchingFields = GetMatchingFields( GetFieldNames( Hydrants ), GetFieldNames( History ) ) // If the date has a count greater than 1 then populate the list with unique dates var DateCount = Count( Filter( Hydrants, DateField + ' = @InspDate' ) ) while ( DateCount != 0 ){ var DateChange = DateAdd( InspDate , DateCount, 'seconds') var DateCheck = Count( Filter( Hydrants, DateField + ' = @DateChange' ) ) if ( DateCheck == 0 ){ var InspDate = DateChange break } else { var DateCheck = DateCheck - 1 } } return InspDate // Filter feature by criteria var FilteredFeature = First( Filter( History, DateField + ' = @InspDate' ) ) // Insert records into the related table if the date does not exist in the related table var Values = First( Filter( FeatureSetByName($datastore, FeatureClassName , MatchingFields ), DateField + ' = @InspDate' ) ) if ( InspDate > First( History )[ DateField ] ){ Console( 'Inserting record from @FeatureClassName into @TableName' ) return { "result": InspDate, "edit": [ { // Specify the related table "className" : TableName, //the type of edit, in this case we want to add so we say `adds`, its an array since we can make many inserts "adds" : [ { // Add the current feature data based on the object id of the filtered data "attributes": Values } ] } ] } } else if ( InspDate == FilteredFeature[ DateField ] ) { Console( 'Updating record from @FeatureClassName into @TableName' ) return { "result": InspDate, "edit": [ { // Specify the related table "className" : Table, //the type of edit, in this case we want to add so we say `adds`, its an array since we can make many inserts "updates" : [ { // Specify the object id of the feature IDField : FilteredFeature[ IDField ], // Add the current feature data based on the object id of the filtered data "attributes": Values } ] } ] } }   I am not sure if I correctly wrote this script to do just that or if there is a simpler solution that already exists. Any help would be greatly appreciated. ... View more

Hi, I been having issues as of late with registering a table view with our enterprise sde database. The table view queries data from another table in another SQL server database using a linked server object. Despite several attempts to register the table view; I cannot seem to get it to register. I don't know if anyone else has encountered issues like this, but any help would be greatly appreciated. ... View more

Hi, I wasn't sure about this, but I wanted to ask. Is it possible to use attribute rules on table views created in a sde database. The reason for asking is we created a view that pulls data from another sde table using a linked server that gets updated every so often and it would be great if attribute rules can be applied to these kinds of tables. This would significantly automate a lot of things and wouldn't require additional scripting. I can write a script in python to search through and update the other features, if need be, but I would rather keep things simple and contained as possible. ... View more

Hi,   I am trying to figure out how to edit a named version using a sql script and I am not sure how to go about it. I can pull the named version information, but I am not sure how to update the information in the table. Here is what I have thus far. EXEC [Database].sde.edit_version 2, 1; -- opens edit session on version EXEC [Database].sde.set_current_version 2; -- Sets version to access BEGIN TRY BEGIN TRANSACTION [Tran1] MERGE [Database].[Featureclass_evw] as TargetTable USING [Server].[Database].[dbo].[Table] as SourceTable ON TargetTable.[MatchingColumn] = SourceTable.[MatchingColumn] WHEN MATCHED THEN UPDATE SET TargetTable.[ColumnA] = SourceTable.[ColumnB] ; COMMIT TRANSACTION [Tran1] -- if successful will commit the transaction END TRY BEGIN CATCH -- if error occurs, will rollback the transaction ROLLBACK TRANSACTION [Tran1] END CATCH EXEC [Database].sde.edit_version 2, 2; -- ends the edit session   I have also tried this option below. EXEC [Database].sde.edit_version 2, 1; -- opens edit session on version EXEC [Database].sde.set_current_version 2; -- Sets version to access BEGIN TRY BEGIN TRANSACTION [Tran1] UPDATE [sdeowner].[Featureclass_evw] SET [Field] = ( SELECT A.[Field] FROM [Server].[Database].[dbo].[Table] AS A JOIN [SDEOwner].[Featureclass_evw] ON [SDEOwner].[Featureclass_evw].[Field] = A.[Field] WHERE EXISTS ( SELECT 1 FROM [Server].[Database].[dbo].[Table] AS A WHERE [Field] = [SDEOwner].[Featureclass_evw].[Field] ) ) COMMIT TRANSACTION [Tran1] -- if successful will commit the transaction END TRY BEGIN CATCH -- if error occurs, will rollback the transaction ROLLBACK TRANSACTION [Tran1] END CATCH EXEC [Database].sde.edit_version 2, 2; -- ends the edit session   I cannot seem to update the values based on these sql queries so I am stuck for the time being. Any help on this would be greatly appreciated. ... View more

Hi, I have been trying to figure out how to create an attribute rule to add a record in a related table based on certain criteria. I am not sure, since I am still learning quite a bit about arcade, how to go about it. I typically program in python, but I am trying to reduce the scripting that would be needed outside of a database when a simple attribute rule could accomplish a similar task.   // Function for defining whether or not a fieldname exists in another feature function ValidateFieldNames( InputFieldsA, InputFieldsB ){ var AFields = [ ] var BFields = [ ] for ( var i = 'name' in InputFieldsA ){ return Push( AFields, InputFieldsA[ i ] ) } for ( var i = 'name' in InputFieldsB ){ return Push( BFields, InputFieldsB[ i ] ) } for ( var Fieldname in BFields ){ var FieldnameIndex = IndexOf( AFields, InputFieldsB[ Fieldname ] ) if ( Includes( AFields, InputFieldsB[ Fieldname ] ) == False ){ return Erase( AFields, FieldnameIndex ) } } } // Current date var CurrentYear = Year( Date( Now() ) ) // Inspection year and date var InspectedYear = $feature.FieldYear var InspectedDate = $feature.FieldDate // Inspection record table var InspectionRecords = FeatureSetByName( $datastore, "Featureclass", ['*'] ) Console( InspectionRecords ) // Get array of recorded inspection year and get only unique years in descending order var RecordedDates = FeatureSetByName( $datastore, "Table", ['InspectionDate'] ) var RecordedYears = Max( Year( RecordedDates ) ) // Get the matching fieldnames that exist in both features var InspectionRecordSchema = Schema( InspectionRecords ).fields var MatchingFieldnames = ValidateFieldNames( InspectionRecords, $feature ) // Loop through the update items to create a concatenated text value of items // to create a single text line of dictionary values var UpdateItems = NULL for ( var field in MatchingFieldnames ){ var ConcatText = Concatenate( field, ' : ', $feature[ field ] ) iif ( UpdateItems == NUll, UpdateItems == ConcatText, Concatenate( UpdateItems, ',', ConcatText ) ) } var UpdateItems = Dictionary( UpdateItems ) // If the current inspection year in the inspected hydrant layer // is not in the inspection records table. Then add the record to // the inspection table if ( CurrentYear == InspectedYear && InspectedYear >= RecordedYears ){ return { 'result' : InspectedDate, 'edit': [ { 'classname' : "Featureclass", 'adds' : [ { 'objectID': $feature.ObjectID, 'attributes' : UpdateItems } ] } ] } }   Any help with this would be greatly appreciated? ... View more