Latest Contributions by RichardFairhurst

For features you also have the Transfer Attributes pair of tools that configures the transfer and that performs the transfer, but it is not available for Standalone Tables.  I use those tools a lot.  An adaptation of that tool that would encompass Standalone tables would be extremely useful, because a user could then predefine just those fields they want to duplicate from a source to the ones they want in a table and leave all other fields alone in a the record.  The configuration tool also works between sources and destinations where the field types are the same, but the field names are different.  The click-click work should be adapted to occur not only between features on a map, but between Tableviews on the little grey squares that are to the left of each record or any combination of the two.  So record to record transfers within a single Tableview or between two Tableviews could be an additional method of transfer that would support both features and Standalone table records.  That would be a life saver and my ideal tool fo creating and maintaining one-to-many and many-to-many relationships in ESRI. As a final comment, I also want to point out the amount of effort and risk that is involved in editing tables where I am forced to manually copy everything but a few values from another row in a Standalone Table, especially when the table has many fields.  Something that would be done in under 15 seconds with a simple copy/paste and edit of a few columns can take more than 5 minutes for rows with 50 columns.  All but 15 seconds of that work is wasted on manual duplications of values from another row. Performing cell-by-cell copy and paste operations from another record to the new record is exhausting and subjects the original record to corruption with an accidental paste on the wrong row.  Retyping the values in the new row subjects the new record to data entry error.  If I have many rows in a table that need to duplicate an original row I am tempted to use the field calculator to do the duplication.  But often to see the original values together with the blank rows, they both have to be selected and the field calculator can corrupt the original data and the new records simultaneously if the wrong value is in the calculator.  There is no technique to do manual data duplication of many columns between an original row and a new row that does not involve excessive work or some risk of data corruption to the new row, the original, or both.  If ESRI cares as much about protecting data from corruption as they say they do, they will implement these ideas for Standalone tables.. ... View more

I totally agree.  Also, what if I want to paste table content directly to Excel for a report rather than always having to export to dBase.  Excel is much easier to format the way I want than the Crystal Reports and ESRI reporting tools that ship with ArcGIS.  I even have the full Crystal report tool available, but I don't find it very intuitive and I cannot readily export the report to others who want both the data and the ability to alter the report format for their needs. Everyone can read Excel files and a huge number of people know how to format them.  For Features Classes I can copy records from the Tableview and paste the records directly into an Excel Spreadsheet, but I cannot for Standalone Tables.    Excel only wants the tabuar data and it is a pain to do exports to dBase all the time for Standalone tables when a simple paste would do.  The raw data from ArcGIS gets pasted under an Excel tab that I never alter or format (except perhaps to do summaries at the end with the summary tool).  I create other tabs for preformatted report pages that either use formulas to point to the raw data or else I do additional Copy operations from the cells I want and use Paste Special for Values into the report slots. With these techniques I can create very elaborate detail and summary reports that I can share with anyone (and almost anyone I share them with can alter the format and report styles for their own purposes depending on their management reporting needs).  The Excel reports are easy to paste on to a map and reformatting the report with cell specific fonts and other customizations to fit the space I have available on the map is much easier in Excel.  The above ideas would make this even easier as well as improve the proecss of maintaining the data accuracy and integrity of Standalone Tables in ArcMap. ... View more

I will have to eat my words about hyperlinks at least as of Version 9.3.  There is a check box option I found after writing this idea/complaint called "When the Hyperlinks or HTML Popup tool is selected, highlight features containing clickable content:" that does what I described for the Identify tool when it is checked.  The check box setting is under the "Tools | Options" menu under the General Tab in the Tools section of the page.  I do not think it is a default setting, but perhaps it should be. So my request is implemented for hyperlinks using the Identify tool at 9.3.  My workarounds may only apply to users of versions prior to Version 9.3.  So ESRI has given one-to-many and many-to-many relates some real funcionality, at least by 9.3. However, as far as I know, my work arounds still apply to Labelling, Symbology and many other aspects of ArcMap when a user wants to use values that are stored in a table with a one-to-many or many-to-many relationship relative to a feature.  This may never completely change, but there is still a lot that potentailly can be done to simplify the process within ArcGIS.  At the very least, ESRI should document an explanation of best practices, including the required licence level and tools, that are involved in the process of transformating one-to-many and many-to-many relationships into the one-to-one and many-to-one relationships, since the latter relationships are still required for most Desktop functions like Labelling and Symbology. Rich ... View more

My bad.  I should have posted this to Service Ideas.  I am new to the site, but I see the Desktop site is fast becoming cluttered as a dumping ground for everything, because it gets most of the attention.  Many users (like myself ) are having a difficult time reorienting to the new web designs and figuring out where our ideas really fit.  Too bad I have no way to withdrawn or recategorize an idea after I have posted it to clean up some of the mess in the Desktop page. ... View more

For almost all of my text fields I would like to use a space (" ") character as a default value, but it cannot be done.  Also there is no way to add a field to existing data and make it Required without problems being generated from the Null values that initialize the field.  So please combine this suggestion with the ability to add retroactively Required fields that are prepopulated with a default value, and include the ability to specify a space (" ") character as a default value. Rich ... View more

Chris: I have implemented the use of the ValidateFieldName for making sure the User Specified field will work with the Output table.  See code below:     #Process: Add the specified ORIG_OID field name     tempOidFieldName = originalOidFieldName     if originalOidFieldName not in ["","#"," "]:         originalOidFieldName = gp.ValidateFieldName(originalOidFieldName, os.path.dirname(outputLayer))         if outShpDbfFlag == True:             gp.AddField_management(outputLayer, originalOidFieldName[0:10], "LONG")         else:             gp.AddField_management(outputLayer, originalOidFieldName, "LONG") #...... In Warnings section.     if not tempOidFieldName == originalOidFieldName:         message = "WARNING: The Original OID field was changed from " + tempOidFieldName + " to " + originalOidFieldName + "!"; showPyWarning() I would like to modify the inputLayerFieldList to be a 2 dimendional array that holds the original field name and the output field name pair.  The output field name would be processed through the ValidateFieldName tool before inclusion in the array.  The array associating the original field name with the validated output field name would be better in my mind than simply trying a truncation of each original field name, when a .dbf or .shp file is specified for the output.  The array could also be processed outside of the loop against the actual output feature class generated to verify that two validated field names are not identical and make them unique in the same way that the CreateFeatureClass_management tool did.  Since the field list of the actual output field class should be in the same basic relative order as the array it should be relatively easy to get the actual field name created for the output into the array. The loop would have to be altered to get the upper bound of the field name array and use a for loop accessing the array with index values to get both input and output field name associations in a single pass within in the loop.  Each new record read within the loop would reset the field name index to 0.  I believe this would improve the ability of the code to take an input that is not a .dbf or .shp file and directly output it to a .dbf and .shp file output without loss of data or placing data in the wrong field in the output due to tracated field name duplications.  (I would like this tool to be worthy of consideration for inclusion in the ESRI releases of their software and the current code is too fragile on this type of transformation to qualify for that).  The loop should also no longer need to test if the output is a .dbf or .shp file and alter the field names within the loop, since the input/output field name pairings and alterations would already have occured outside the loop. How does Python handle multi-dimensional arrays and index access of arrays?  I know the syntax in VBA or VB Script to do what I want, but not in Python. One other question.  I used your tool in a ModelBuilder model.  When I attached other tools to the output, the tools did not know the schema of the output to choose fields in tools like the Calculate Field tool until I ran the script one time.  Do you know of a way to notify tools connected to the script output what the output schema will be before the tool is run once?  Thanks for your help. Rich ... View more

Chris: Your right, I like it very much. :cool:  With the geometry capabilities from the shape this starts looking like a real search cursor alternative, especially for when embedded cursors would normally be used. I don't have any background in using code like this, so I will have to play with it to start really understanding the possibilities (but hopefully I can approach it as being similar to a cursor on steriods). How well do you think it would do with 120,000 features (my main dataset)?  If not that well I could do a query selection first and just use the records I needed. I am very glad I started this dialog, since we seem to be aiming at many of the same things. Rich ... View more

Jinx posting. Look at my post right above yours concerning dictionaries. This is all great stuff - I'm a hack self-taught programmer, so it's great to get someone else's suggestions, tips, tricks, etc. Double jinx posting.  My background is the same.  I am not part of IT.  I'm just a guy who wants to get the job done and transform the way my Department sees and does things through GIS. ... View more

However, I don't think there is a way to retrieve a field value in a cursor using it's index value. Maybe I'm wrong though... Seems that would be a major performance enhancement like you said. I have mostly shied away from embedded cursors (current script being an exception) since they tend to be slow. Your current code only creates a hit that is about twice as bad as using a single cursor since you are not really doing a record match on both sides of the cursors.  You are just reading a record on one side and writing a new record on the other.  The practice I mentioned for ArcObjects should still dramatically speed performance for a single cursor and for this kind of simple embedded cursor pair if Python had an efficient way to implement it.  However, for truly embedded cursor routines that have to look up records against each other or walk through a sorted pair of cursors (using multi-field keys especially), the primary hit is from maintaining the secondary query synchronization on unsorted records or records that are only sorted in memory. Your tool that permanently sorts to a new table (without reordering fields on the original to use a summary/frequency) offers me the potential to solve the dual cursor synchronization issue, which was partly why I am so interested in it.  Developing an efficient way to do a single pass on a pair of cursors against tables with permanently sorted records could speed up the embedded cursors significantly.  By operating on all fields at once in a record with the cursor pair I should be able to be much more efficient than using the field calculator on a pair of joined tables operating one field at a time (recognizing that there are diminishing returns as I need to do this with fewer fields).  The OID addition I requested means that by doing a second sort back to original OID order I could use the same technique to transfer the data back to the original data source (without having to do a memory based sort).  Linking back to the original is particularly important to me where the original cannot be overwritten because it has topologies, feature linked annotation or other advanced features (or I do not control the original data but need to maintain a duplicate that can use these techiniques to extend and enhance the source data with data that I maintain). The dictionary approach is valuable when the spatial information does not need to be taken into consideration.  Unfortunately, the spatial data almost always plays a roll in my cursor routines.  In my case I may use Visual Studio to do the embedded cursor work (or I might port your Python code to Visual Studio and see if I can get a substantial performance gain using an ArcObject cursor method I described above).  I am not really comfortable porting my VBA to Visual Studio either, but if I am going to have to do another learning curve I want others to benefit (like I have from this exchange).  Anyway, thanks for pointing me in a new direction that I think really begins to tackle some of the problems I am trying to solve. (Edit) Actually, the dictionary approach may offer some interesting possibilies for me since I am in Transportation and most of my problems relate to traversing line networks in different ways.  Linear referencing has solved some of my problems, but it would be nice to create efficient cursor routines that can quickly traverse Route IDs and measure values on joined event tables of different inventories (signals, signs, curb ramps, accidents) and perform sophisticated analysis for output to a summary table.  My bosses would like to do segment analysis based on 1000 intervals or between interesections and I don't see a way to really do it without some kind of cursor operation.  Route IDs and measures provide me with a way to understand direction of travel and network traversal without usiing the shapes (if used together with an X/Y coordinate the analysis possibilities become very interesting).  Trying to train the computer to talk with end user data and needs is where I spend a lot of my time.  Anyway, ordering data seems to be the biggest factor in making these routines really perform well so this is a subject near and dear to my heart. Rich ... View more

Rich, You are totally correct! Great observation... I thought it seemed like when I was testing this stuff yesterday it was running a bit slow when I used the ORIG_OID option... Another idea to structure it would be to append the ORIG_OID field (if the user provides it) to the inputLayerFieldList, and then have a condition in the loop that says if inputLayerField == originalOidFieldName, then attempt to write it. I perfer my version, because it allows me to distinguish three cases: 1. No user OID field name provided. 2. User OID field provided but no input OID available 3. User OID field provided, input OID available, but write attempt fails anyway. Case 2 might not be anticipated by the user.  Case 3 is especially likely to happen when a geodatabase is being ported to a .dbf or .shp file and field name truncations do not match the way the schema was imported into the output feature class/table. Case 3 could also occur because the code currently does not test if the user provided a valid field against the output data source, which could happen if the user unknowingly uses a data source keyword or included spaces in the name or something else.  A gp.ValidateFieldName probably should be used in the code and if a different name is returned by that tool the new name should be appended to the output table and the user should be warned about the field name transformation.  This report would also help for the possible errors in field tranferrance that may result from trucating field names in and geodatabase to .dbf and .shp file transformations.  The appended user OID would still allow the user to join and manually transfer any missed field data to the output when they determined there was a failure that cannot be remedied with the current implementation. I hope this helps. Rich ... View more

I wanted to also modify the loop processing to implement a standard ArcObjects practice, but I do not know if Python supports it.  In ArcObjects it is much more efficient to use a field's index number that indicates the field's relative numeric position in the table rather than its field name to read and write data.  The reason is that when a field name is passed to ArcObject methods that use a field name, the method in the background processes an invisible loop through the table schema to get at a field's index position.  However, by using methods that directly use the field index number a loop is not triggered and it operates more like accessing an array element with an index value. It is a standard practice in ArcObjects to speed up loops using dual cursors to first match up the input and output field names to obtain a key pair array of field index values outside of the loop.  Then within the loop the key pair indexes are used as directly as possible and methods that trigger the field name/field schema matching hidden loop are avoided.  Using the preloop matched field index directly in ArcObjects can speed up cursor get and set value operations by a factor that averages the number of fields divided by 2.  The effect on performance becomes very pronounced in a loop. Is there anything equivalent in Python that will provide and use a field index value with a cursor rather than a field name?  If not I believe all loops with get and set value cursor operations that use a field name in reality are processing two loops (and getting an increased hit as fields toward the end of the schema are accessed).  If no similar field index capability exists for Python, that might provide a partial explanation for the attrocious performance of Python on joined tables as opposed to VBA or VB Script in the ModelBuilder field calculator. Rich ... View more

I realized why the loop made no sense to me.  It is written so that every time the loop writes to a field that can have its value set, it also writes to the ouput OID field provided by the user (if the input has an OID field).  So if there are 10 writable fields, the user OID field is writen 10 times per record (more writeable fields only makes processing the loop worse due to twice as many write attempts).  My VBA training from ESRI on loops has taught me to place as much of my testable conditions outside of the loop as possible and then minimize writing to a cursor within the loop to maximize speed and efficiency. To achieve those objectives I set some variables outside the loop that have pre-tested whether a write of the user provided OID field should even be attemped within the loop.  I then only attempt to write to the user field when the input OID field is being read from the input (if the input has no OID field the user provided field never gets a write attempt and a related warning is reported at the end).  All other non-OID fields being read from the input will bypass the user provided field and use your normal field writing code.  This revison of the loop results in only a single write attempt for each field in a record. This should definitely improve overall speed of the cursor writing process. My revision of the loop is as follows:     inputLayerOidFieldName = dsc.oidfieldname     #Only attempt to write to the users OID field if the a field name is provided and the input has an OID field.     if originalOidFieldName not in ["","#"," "] and inputLayerOidFieldName > " ":         tryOidFieldWrite = True         origOidPopulateSuccessFlag = True     else:         tryOidFieldWrite = False         origOidPopulateSuccessFlag = False     while searchRow:         insertRow = insertRows.newrow()         for fieldName in inputLayerFieldList:             try: #you can't write the OID, length, area, etc. fields!                 if outShpDbfFlag == False:                     # Only attempt to write to the user OID field when the input OID field is being read                     if tryOidFieldWrite == True and inputLayerOidFieldName == fieldName:                         try:                             insertRow.setvalue(originalOidFieldName, searchRow.getvalue(inputLayerOidFieldName))                         except:                             origOidPopulateSuccessFlag = False                             pass                     # All fields other than the input OID field are passed to this code.  The input OID will also be passed if the user did not provide an output OID field name, but being non-writable it will fail.                     else:                         insertRow.setvalue(fieldName, searchRow.getvalue(fieldName))                 else:                     # Only attempt to write to the user OID field when the input OID field is being read                     if tryOidFieldWrite == True and inputLayerOidFieldName == fieldName:                         try:                             insertRow.setvalue(originalOidFieldName[0:10], searchRow.getvalue(inputLayerOidFieldName))                         except:                             origOidPopulateSuccessFlag = False                             pass                     # All fields other than the input OID field are passed to this code.  The input OID will also be passed if the user did not provide an output OID field name, but being non-writable it will fail.                     else:                         insertRow.setvalue(fieldName[0:10], searchRow.getvalue(fieldName))             except:                 pass         recordCount = recordCount + 1         insertRows.insertrow(insertRow)                searchRow = searchRows.next()     message = "Successfully wrote " + str(recordCount) + " records!"; showPyMessage()     # Only publish a warning if the OID field write attempt should have been made and failed or if the user provided a field but the input had no OID field.     if originalOidFieldName not in ["","#"," "] and tryOidFieldWrite == True and origOidPopulateSuccessFlag == False:         message = "WARNING: For some reason, the original OIDs could not be preserved in the output!"; showPyWarning()     if originalOidFieldName not in ["","#"," "] and tryOidFieldWrite == False:         message = "WARNING: The input had no OID field and could not be preserved in the output!"; showPyWarning() I hope that these revisions meet with your approval and can be incorporated into your final code. Rich ... View more

Didn't do much extensivee bug testing - Let me know if you find any issues... Chris: The new script did what I requested, but I noticed you did not rewrite the portion of your error checking tests to ensure that duplicate field names were not entered into multiple Sort Field choices so that the test accounted for the new sort field parameter. I rewrote the section beginning at line 133 as follows to both check for all possible field duplications and to brake it down into separate field tests to keep it more readable:     #make sure the same field isn't trying to be sorted twice     if (sortFieldName4 not in ["","#"," "] and (sortFieldName4 in [sortFieldName1,sortFieldName2,sortFieldName3])):         message = "ERROR: Duplicate field name in Sort Field #4.  Can't sort the same field twice! Exiting script..."; showPyError(); sys.exit()     if (sortFieldName3 not in ["","#"," "] and (sortFieldName3 in [sortFieldName1,sortFieldName2])):         message = "ERROR: Duplicate field name in Sort Field #3.  Can't sort the same field twice! Exiting script..."; showPyError(); sys.exit()     if (sortFieldName1 == sortFieldName2):         message = "ERROR: Duplicate field name in Sort Field #2.  Can't sort the same field twice! Exiting script..."; showPyError(); sys.exit() I noticed that when I just wanted to sort just two fields that I as long as I put the first sort field in the Sort Field #1 parameter I could add the second sort field to any of the other 3 sort field parameters (as long as I specified the sort order that paired with the sort field parameter I chose) and the script worked the same.  That is nice.  Also, thanks for the quick modification. I am still trying to understand the way the nested try and if block works in the section where you transferred the OID values.  Your code worked, but based on the comment above that section I would have thought that it would have thrown an error when the OIDFieldName tried to calculate to the output's true OIDFieldName and that the user provide OID field would not have been populated.  Obviously I made a wrong assumption somewhere, but where did I go wrong?  When both tables have a matching OID field name and the setvalue matches the names and attempts to overwrite the inserted record's true OID value, does that not cause an error?  Did I miss that somehow the code does not actually try to overwrite a true OID value in the output?  Please provide me with a little insight.  (Faulty assumptions like that can sometimes stick in my thinking and cause me to write unneeded code to work around a non-problem 😮 .) Thanks again. Rich ... View more