Calculating fields from related tables

287
1
Jump to solution
09-07-2021 06:26 AM
KenBuja
MVP Honored Contributor

I have a table with several M:N related tables where I would like to make field calculations to concatenate all the related attributes into a comma-separated string for each record. The table has relationship classes set up for each related tables as shown in this part of the documentation. This table is used in a map service where I can do an on-the-fly calculation using the queryRelatedFeatures in Javascript:

const getRelatedInfo = async (relationshipName: string, field: string, SourceID: number) => {
  const array: string[] = [];
  await sourceTable.queryRelatedFeatures({
    outFields: ['*'],
    relationshipId: getRelationshipId(sourceTable.relationships, relationshipName),
    objectIds: [SourceID]
  }).
    then(async (results: esri.FeatureSet[]) => {
      if (results[SourceID]) {
        results[SourceID].features.forEach((feature: esri.Graphic) => {
          array.push(feature.attributes[field]);
        });
      }
      await array;
    });
  return array.join(', ');
};

However, this takes too long for each record (about half a second), so I'd rather calculate this in my ArcGIS Pro document before publishing it as a service.

Is there a Python equivalent to queryRelatedFeatures? The closest I've come across is looping through each record with a SearchCursor and using the intermediate table to get Destination Keys of the related records. Then I'd have to do another search of the related table to get the actual related attribute from those keys. I'm hoping there's a better way of doing this out there.

0 Kudos
1 Solution

Accepted Solutions
JeffK
by MVP Regular Contributor
MVP Regular Contributor

In your javascript code, do you need to return all of the fields, or could you pair it down to just those that you want to use (if possible)?  Limiting to just those that are needed (if possible) may speed things up on that end.

As for doing it before publishing, it would help to see what you have coded already but I would think that you could easily grab the related attributes using a nested SearchCursor like this so you're not storing a list  to iterate over again:

with arcpy.da.SearchCursor(maintble, ['objectid', 'joinkey'] as sCur:
    for row in sCur:
       with arcpy.da.SearchCursor(attrtble, ['fkfield', 'other fields'], f'fkfield = {row[1]}') as attrCur:
           for row in attrCur:
               Logic here to iterate over the M:N and calculate your field

 

View solution in original post

0 Kudos
1 Reply
JeffK
by MVP Regular Contributor
MVP Regular Contributor

In your javascript code, do you need to return all of the fields, or could you pair it down to just those that you want to use (if possible)?  Limiting to just those that are needed (if possible) may speed things up on that end.

As for doing it before publishing, it would help to see what you have coded already but I would think that you could easily grab the related attributes using a nested SearchCursor like this so you're not storing a list  to iterate over again:

with arcpy.da.SearchCursor(maintble, ['objectid', 'joinkey'] as sCur:
    for row in sCur:
       with arcpy.da.SearchCursor(attrtble, ['fkfield', 'other fields'], f'fkfield = {row[1]}') as attrCur:
           for row in attrCur:
               Logic here to iterate over the M:N and calculate your field

 

0 Kudos