topic Re: Sum Column in Related Table and Populate EDITFORM Field in ArcPad Questions

Sum Column in Related Table and Populate EDITFORM Field

jeffclonts — Wed, 02 Mar 2011 16:18:20 GMT

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

Re: Sum Column in Related Table and Populate EDITFORM Field

JasonTipton — Wed, 02 Mar 2011 20:11:08 GMT

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.

Re: Sum Column in Related Table and Populate EDITFORM Field

EricMcPhee — Thu, 03 Mar 2011 13:38:34 GMT

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

Re: Sum Column in Related Table and Populate EDITFORM Field

JasonTipton — Thu, 03 Mar 2011 18:36:20 GMT

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.

Re: Sum Column in Related Table and Populate EDITFORM Field

jeffclonts — Thu, 03 Mar 2011 19:44:50 GMT

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

Re: Sum Column in Related Table and Populate EDITFORM Field

JasonTipton — Fri, 04 Mar 2011 15:50:50 GMT

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...

Re: Sum Column in Related Table and Populate EDITFORM Field

EricMcPhee — Mon, 07 Mar 2011 13:45:41 GMT

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

Re: Sum Column in Related Table and Populate EDITFORM Field

JasonTipton — Mon, 07 Mar 2011 15:43:43 GMT

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?

Re: Sum Column in Related Table and Populate EDITFORM Field

EricMcPhee — Mon, 07 Mar 2011 16:35:43 GMT

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.

Re: Sum Column in Related Table and Populate EDITFORM Field

JasonTipton — Sat, 11 Dec 2021 12:47:59 GMT

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