Sum Column in Related Table and Populate EDITFORM Field

[jeffclonts]

Hi

I have a point feature with a related table. The user populates the related table within the field form. One of the columns is called "volume_removed". The user will enter multiple rows until the parameters stabilize. There could be as many as 20 rows (or more). I have a field, not in the related table, on the last page of the form called "Total_Volume_Removed". I would like to create a script that would populate this field from the sum of the "volume_removed" field in the related table. I do not know how to call values from the related tables to create a script.

Thanks,

JC

[JasonTipton]

ESRI is supposedly getting back to me on a very similar issue. Basically, you need to be able to access the recordset and filter it to obtain all of the records that are related to the current point. Then, basically you need a sum(volume_removed).

The problem is that I do not know how to access the recordset of a related table.

[EricMcPhee]

Are you using shapefiles and related .dbfs or .AXF?
I have had success querying a related table in .axf. The query is just a SQL statement.

Sub SummarizeTrees

dim DataSource
set DataSource = Map.Layers("MFIPlotsContractor").DataSource 'this is the feature(parent) table

dim query
dim queryRows
             'this query will select the rows I want from the related table (ENVIRO_MFITREESCONTRACTOR)
             query = "SELECT SUM(dbh) from ENVIRO_MFITREESCONTRACTOR where [NEWKEY] = " + CStr(Stand)
             set queryRows = DataSource.execute(query)
           
             queryrows.movefirst

            msgbox "The Sum of DBH for Stand#" & CStr(Stand) & ":" & (Chr(13)) & queryRows.fields(1).value

end sub

[JasonTipton]

Thank You Thank You Thank You Thank You!

Yes. .axf. I figured that it would be something fairly simple like that. I just couldn't find any documentation for it.

[jeffclonts]

slcemcphee33

Thanks a lot for your input. It worked great! I ended up creating a "Summarize" button on my form that will allow the user populate the Total Volume field.

Code:
Sub SummarizeVolume

   dim DataSource, objControl, objPage
   set DataSource = Map.Layers("Wells").DataSource     

     Set objControl = ThisEvent.Object
     Set objPage = ThisEvent.Object.Parent

   dim query
   dim queryRows

   query = "SELECT SUM(vol_removed) from TABLE_PARAMETERS"
   set queryRows = DataSource.execute(query)

   queryrows.movefirst

   objPage.Controls("txtTotVolRemoved").Value = queryRows.fields(1).value

end sub

You da man!

jc

[JasonTipton]

jclonts, Hold on, I don't think you're out of the woods yet!

You have no WHERE in your SQL statement. That will give you the sum of all the volume removed from all your wells.

so if you had a total of 10, 15 and 20 gallons removed from well 1, that would be 45. Then from well Two, 5 and 20 gallons for 25 gallons. Instead of return 25 on well Two, you will 10 + 15 + 20 + 5 + 20 = 70.

Also, I assume that you are using the ObjectID for your relates. . .

slcemcphee33,

I assume that you are making your own Primary/Foreign Key "NewKey" that you store in the value Stand? I haven't reached that point yet. I am still using the ObjectID for my relationships which is scary in my opinion. The problem is that I can't figure out how to get the AXF_OBjectID for the current point to use in my WHERE statement. If you use fields(AXF_OBJECTID), it won't return anything.  I may have to just turn to using my own keys...

[EricMcPhee]

Jason-
You are correct, I am using my own primary key (NEWKEY). I have always been a little spooked by ObjectIDs....call me paranoid if you like. Try using Fields("AXF_ObjectID").value. This should return the value of your AXF_ObjectID that you can store in a variable. Then in your SQL statment add the where clause like this:
          "......where [AXF_ObjectID] = " + CStr(variable)


Eric

[JasonTipton]

Right, right. See it doesn't appear to create the AXF_ObjectID until it saves the point. Once you close the point and reopen it, you can query for AXF_ObjectID, but not before.

So, when you create your New_id for the feature class, does ArcPad maintain the relationship on the table side? (Do you have to set the foreign key = new_id or does it handle that?) And what are you using for your "newKey" so that if multiple users go out at the same time, they don't come back w/ duplicate newKeys?

[EricMcPhee]

I am working with pre-assigned plots (my points already exist), which are assingned a "newkey" value based upon which stand(polygon) they reside in. This is all done before it gets to ArcPad.
So, in my geodatabase I have a point featureclass with all of my plots (which have their "newkey" and a PlotID pre-assigned) and Related Table that has PlotID as the foreginkey and a relationship class between the featureclass and standalone table. When a new Tree (related table row) is added, I copy the NEWKEY to the new related record and also assign a new TreeID, which is a sequential number. Since I have setup a relationship class in my Geodatabase, the software takes care of adding the PlotID to each new record added to the related table.

[JasonTipton]

I see.

Before long, I plan on abandoning the OBJECTID as well. Not only do I not trust it, if you try and move the data, the objectID's get reordered so when ArcMap doesn't import your data for some reason, you have to change all of the pk/fk.

What I found, is that you can always access the foreign key even before the original feature class is committed.  So, in the related form, I can query and find the information I need.

This is my code to query and find how many related [Lithology] records there are and to increment that number by 1 to give me a [LithOrderNumber] starting with 1 instead of 0:

sub countRecords
Dim ds
Dim query
Dim qRows

Set ds = Map.Layers("FieldPoints").DataSource
set flds = ThisEvent.Object.Fields

fid = flds("FIELDPOINTS_ID").VALUE    'Sets fid to the fk
query = "SELECT COUNT(AXF_OBJECTID) FROM [LITHOLOGY] WHERE [FIELDPOINTS_ID] =" & fid
Set qRows = ds.execute(query)     'Execute Query
qrows.movefirst         'Read results
countRec = qRows.fields(1).value
flds("LITHORDERNUMBER").value = countRec + 1
End Sub