Latest Contributions by RichardFairhurst

Since you say you want to keep the 3 latest records from the related tables that suggests that you have a date field associated with those records.  If so, the date field needs to be loaded with the other data to add logic that would publish only the latest 3 records from each table.  Anyway, limiting the number of records is possible, but there are a variety of approaches for implementing that limit and the most efficient method will be determined by the nature of the data that defines what records are the "latest 3 records".  So please explain what data you have available that you would use to choose the latest 3 records from each table. ... View more

I have not worked with Portal or tried labels with code like this in web app builder, so if your issues are specific to those platforms, I can't really help.  If it is assumed that the code will be compatible with the platform and your questions are related to making the code work for this configuration of table relationships then I should be able help. The basic structure of the code always reads the related tables first into one or more dictionaries using the key value that relates each record set to the parent table.  Then the labels are built using the primary feature class features to provide the group header in the label and the dictionaries to provide the detail listings of each label. Correct me if I am wrong, but it appears from the table set you have outlined that the OTGIS_ENTGDB_REG.SDE.TestUGVentilationStation feature class is the parent feature class controlling the labels and the other three tables all have a foreign keys related to the station field of that parent feature.  It is not clear how the three related tables relate to each other, but I would assume they are in a Many to Many relationship to each other using just the foreign related key fields you have mentioned.  As a result each related table may need its own section in the output label to avoid creating multiple copies of each related record in one table if you were to combine them with the records of another related table.  If you follow this approach then you need to build three dictionaries which will be handled independently relative to the other dictionaries when you build the label. Using a very basic label layout that just displays each record in a raw form, the core code for extracting the data should be something like: # Initialize three global dictionaries for each related feature class/table relateDict1 = {} relateDict2 = {} relateDict3 = {} def FindLabel ( [station] ):   # declare the dictionary global so it can be built once and used for all labels   global relateDict1   global relateDict2   global relateDict3   # only populate the first dictionary if it has no keys   if len(relateDict1) == 0:     # Provide the path to the relate feature class/table     relateFC1 = r"connectionfile/OTGIS_ENTGDB_REG.SDE.TestUGVentPrimaryStatSurvey"     # create a field list with the relate field first,     # followed by sort field(s), then label field(s)     relateFieldsList1 = ["parentpid", "pressure", "wetbulbtemp", "humidity"]     # process a da search cursor to transfer the data to the dictionary     with arcpy.da.SearchCursor(relateFC1, relateFieldsList1) as relateRows:       for relateRow in relateRows:         # store the key value in a variable so the relate value         # is only read from the row once, improving speed         relateKey = relateRow[0]         # if the relate key of the current row isn't found         # create the key and make it's value a list of a list of field values         if not relateKey in relateDict1:           # [searchRow[1:]] is a list containing           # a list of the field values after the key.           relateDict1[relateKey] = [relateRow[1:]]         else:           # if the relate key is already in the dictionary           # append the next list of field values to the           # existing list associated with the key           relateDict1[relateKey].append(relateRow[1:])     # delete the cursor, and row to make sure all locks release     del relateRows, relateRow   # only populate the second dictionary if it has no keys   if len(relateDict2) == 0:     relateFC2 = r"connectionfile/OTGIS_ENTGDB_REG.SDE.TestUGVentSecondStatSurvey"     relateFieldsList2 = ["parentsid", "workdrybulb", "workwetbulb"]     with arcpy.da.SearchCursor(relateFC2, relateFieldsList2) as relateRows:       for relateRow in relateRows:         relateKey = relateRow[0]         if not relateKey in relateDict2:           relateDict2[relateKey] = [relateRow[1:]]         else:           # if the relate key is already in the dictionary           # append the next list of field values to the           # existing list associated with the key           relateDict2[relateKey].append(relateRow[1:])     del relateRows, relateRow   # only populate the third dictionary if it has no keys   if len(relateDict3) == 0:     relateFC3 = r"connectionfile/OTGIS_ENTGDB_REG.SDE.TestUGVentCriticalStatSurvey"     relateFieldsList3 = ["parentcid", "quantity", levelnh3", "levelco"]     with arcpy.da.SearchCursor(relateFC3, relateFieldsList3) as relateRows:       for relateRow in relateRows:         relateKey = relateRow[0]         if not relateKey in relateDict3:           relateDict3[relateKey] = [relateRow[1:]]         else:           relateDict3[relateKey].append(relateRow[1:])     del relateRows, relateRow   # store the current label feature's relate key field value   # so that it is only read once, improving speed   labelKey = [station]   # start building a label expression.   # My label has a bold key value header in a larger font   expression = '<FNT name="Arial" size="12"><BOL>{}</BOL></FNT>'.format(labelKey)   # determine if the label key is in the dictionary   if labelKey in relateDict1:     # sort the list of the list of fields     sortedList = sorted(relateDict1[labelKey])     # process the sorted list     for fieldValues in sortedList:        # append related data to the label expression        # my label shows a list of related        # stations with sorted pressures, wetbuldtemps and humidities        expression += '\n{0} - {1} - {2}'.format(fieldValues[0], fieldValues[1], fieldValues[2])     # clean up the list variables after completing the for loop     del sortedList, fieldValues   if labelKey in relateDict2:     sortedList = sorted(relateDict2[labelKey])     expression += '\n<FNT name="Arial" size="10"><BOL>Station Count = {}</BOL></FNT>'.format(len(sortedList))     for fieldValues in sortedList:         expression += '\n{0} - {1}'.format(fieldValues[0], fieldValues[1])     del sortedList, fieldValues   if labelKey in relateDict3:     sortedList = sorted(relateDict3[labelKey])     for fieldValues in sortedList:         expression += '\n{0} - {1} - {2}'.format(fieldValues[0], fieldValues[1], fieldValues[2])     del sortedList, fieldValues   # return the label expression to display   return expression‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍ I have not tried the efficiency step outlined by Fatih Dur, so I went with the code structure I have personally used before.  Using Fatih's recommendation could result in shorter and cleaner code, so once you have a code base that you know is working you could try implementing that efficiency step and keep it if the revised code works the same. ... View more

I do not believe there is any built-in functionality to do labeling using tables in a 1:M relationship.  My solution is posted in my Creating Labels with Related Table Data blog.  Using this technique the labels will update every time the display refreshes. ... View more

You should try Cast(tot_value as float) > 500000 and cast(tot_value as int) > 500000. It is almost certainly the field type part of the argument that is throwing the error, since for a long time only float worked with fgdbs even though that type was not even in the documentation.  I just stumbled on it randomly. ... View more

Thankfully Paste works the way it does, since I have never wanted to have it do the behavior you want and always want an exact copy.  What you want would be like copying text and having Paste change the verb tense when it is pasted into Word.  Someone may want that, but it would require much more than copy/paste is designed to do.  However, Excel and Word do offer a few choices like copy values only or copy formatting only and do paste based on cursor position and selection, so the concept of paste options is not out of the question. For this your best option if this is a pressing need would be to create your own tool or have someone design a custom tool for you using python.  The likely tool design would require a toolbar with a pair of dropdowns to choose the source and target layers rather than relying on the clipboard and a popup.  Using the map extent for coming up with a position would be too vague to get a good result in my opinion and is not as consistent with standard tool behavior.  Instead the tool could be triggered by clicking on the map.  Whatever point you had selected in the source could be transferred to the position you clicked on the map.  If you selected a group of points the click could act as the centroid for the group. There are code snippets throughout the forums that can be adapted to implement this tool.  I have done similar tools in the past, but I can't offer my services to design one any time soon.  I find designing my own tools is far better than waiting on Esri for something like this.  Even if Esri eventually does come up with a tool, designing my own usually gives me the functionality I need about 2 to 10 years before Esti ever adds it.  Esri tools are usually more generic and adapted to be used with any random data, but I still end up using mine since they are targeted to my exact data and workflow needs and typically still eliminate the need for multiple steps after the Esri tool is released. ... View more

Nowhere do you say what database you are using.  The answer is very dependent on the database.  Asking this question without ever revealing the database you are using makes this an impossible question to troubleshoot.  If this is a file geodatabase then CAST(TOT_VALUE AS INTEGER) > 500000 is valid and works. I just ran Select By Attribute on a text field using a Cast on a Substring in a file geodatabase with the expression CAST(SUBSTRING(LINE_LINK FROM 2 FOR 12) AS INTEGER) > 7000000 I ran it and got back the expected result.  Therefore all I know is that you are not using a file geodatabase.  This may also work for a shapefile, but I did not test that.    The same expression does not work for SQL Server.  For that I have to use: CAST(SUBSTRING(LINE_LINK,2,7) AS INT) > 7000000    The answer will be different for ORACLE and may be different for a shapefile.  You have to search online for the syntax that specifically applies to each database to use methods like CAST or SUBSTRING, since there is no standard SQL implementation for these methods that works across databases. I have never used an AGOL Feature Layer, but you need to know which database that layer uses and find the syntax for it online  to come up with a valid Cast expression. ... View more

Your data is in a Many to Many relationship and you need to create a Many to Many relationship class to do this properly.  You would not store the relationship in either feature class, instead the Many to Many relationship class creates an intermediate table where each related project and pole are listed out like a normal One to One relationship (one record holds exactly one project related to one pole, so there are 4 records with a ProjectID of "A,B,C,D" with each pole in only one of the 4 records).  The intermediate table is held inside of the relationship class object in ArcCatalog, and you can drag that object into the map to see the intermediate table.  When you select any pole (say pole "A") and activate the relate you will get all of the related projects (Projects "A,B,C,D", "A,C" and "A,D").  For the data you have shown there would be 13 intermediate table records to relate the 6 projects to 4 out of the 7 poles (A,B,C and D). Populating of the intermediate table is done through the Attribute pane. A script could be written to create a standalone table with all of the records required for the intermediate table and then you could use that table to create and populate the relationship class with the Table to Relationship Class tool. I believe you would then be free to use a truly unique ID for each project (say like an ObjectID, GlobalID, Attribute Assistant Generate ID or anything you like such as PROJ-2018-01, PROJ-2018-02, etc) as your Project ID that has nothing to do with the poles and still populate a list field on each project of the related poles (or a list on the poles of the related projects) derived from the relationship class table using a script. ... View more

In Attribute Assistant use the X_Coordinate and Y_Coordinate methods.  The coordinates are based on the feature class coordinate system and can be the Centroid or for lines the start or end of the line.  It is listed under the All Methods help page for Attribute Assistant.  Just Google it. Like all Attribute Assistant methods, the X and Y coordinate methods can trigger at feature creation and/or in response to geometry (or attribute) changes. ... View more

The Field Calculator is the wrong tool for this task and cannot be used to solve the problem.  As others have mentioned a SearchCursor should be used to read all of the records except the summary record to sum the values and then an UpdateCursor can write the result to the summary record if it exists or an InsertCursor can create a summary record if it doesn't exist. ... View more

The particular set of data I am currently working with could disregard the spatial component, but the value assignment makes much more sense as shown than using a random assignment.  However, I have other problems I would love to tackle that I have never attempted, because they are only worth tackling through this kind of spatial algorithm.  The data in my example is current and historic parcels and the output I am trying to create mimics parcel history.  I do not have access to parcel history data from my Assessor and if I did it would only complicate the problem by adding another many to many relationship I would have to manage. If the spatial problem was removed I would still be open to suggestions on how to approach the non-spatial portion of the problem, especially if they involved modules beyond native python and arcpy.  I have avoided learning numpy up to now, but could be persuaded to learn it if it has application to solving problems like this. ... View more