Open Related Table Tab from EditForm

[EricMcPhee]

Is it possible to open the 'related table tab' from a button click on an EditForm in ArcPad 10?

The only way I can see to access the related table tab is to make the Page Tabs visible on the EditForm. I would like to keep the Page Tabs hidden from the user to avoid confusion, because the second and third pages of the EditForm need to be filled out after the Related TableForm has been filled out. But, the related tableform shows up at the end (right, and off the screen) of the Page Tabs. So the user would need to scroll over to the Related Table tab, enter the data and then scroll back to enter the remaining info in the EditForm.

This seems like it should be possible, but I have not been able to figure it out. Anybody have any ideas???

[EricHajek1]

I looked at doing this for one of my recent projects, and I think the current answer is "no". Looking at how the object model for ArcPad is set up and looking at how the XML view of the forms is, at this time I think it's unsupported:

If you open your AXF file and look at a feature class that has a related table, the related table's form is listed under the <EDITFORM> tag as a <TABLEFORM>. Trying to reference it via Forms("EDITFORM").Forms("TABLEFORM") or Forms("EDITFORM").Pages("TABLEFORM") doesn't work, and when I iterate through the "Forms"/"Pages" collections, the tableform isn't listed.

Since there is normally a 1:1 comparison between the XML and the object model, (ie, pages within forms, controls within pages, etc) I just think the objects for table forms haven't been implimented.

I was also bummed about telling people "and then you go to this useless listview page, and click this blue plus sign". 🙂

Good luck,
Eric

[EricMcPhee]

Thanks for the response Eric. I posed the same question to ESRI through Tech Support and their response was very similar...'there is actually no current method to do this out-of-the-box'. Well, there you have it!
I guess I'll have to try to dream up some type of workaround.

[EricHajek1]

The work-intensive alternative is always to just create a generic form that outputs its contents to the related table. There's a good example of this in the Riverside developer's sample (ArcPad10.0\Developer\Samples\RelatedTables\RelatedTablesForm - the Subs UpdateInspection and InsertInspection specifically). Basically you have to handle all the data validation and SQL statements that update the table. It's all centered around building a long SQL statement and then feeding it to the table's datasource via the .Execute command. If you only have a page's worth of controls with simple validation rules it wouldn't be too bad to implement since you can still hook into the form's onvalidate events... but having done that previously for related tables in 8.0, I jumped at the chance to avoid that, even if it means that there's an extra page change click involved.

[DanaC]

I would like to see this functionality also. I added this as a suggestion to the ideas page here:

https://c.na1.visual.force.com/apex/ideaView?id=087300000008FYJ&returnUrl=%2Fapex%2FideaList%3Fc%3D0...

If we get enough votes, it might get implemented!

[ForrestKaye]

Any developments?  I am looking to do the same thing.  It has been nearly a year now since the last post.  Has anything changed?  I am currently trying to migrate a project from ArcPad 8 to 10.  There are a large amount of related tables currently handled by custom insert and update SQL scripts and was hoping to make my project less static by allowing Arc to handle more of these interactions.

It seems like there should be a way to call the "blue plus" button method to add a record to a related table from a custom from and allow ArcPad to handle the load and insert.  Similar to a AddFeature("MyFeature") method.  But there doesn't seem to be a AddRelatedTable("MyRelatedTable")?

[LastingerBrooks]

Hey All,

I take it you're basically trying to get values from a related table to show up in controls on a form. It is my guess that because you are trying to programmatically access the tabs on ArcPad to get to the "related tables" tab, it probably would be waaay easier to just make your values from your related tables just show up in the controls of your Page1 form. Am I feeling you?

Here is how you do it.

You're gonna have to use some SQL for this, so buckle your safety belts. (It's real easy once you get the syntax.)

First, you're going to query your related table dataset for the records that are relevant to whatever you're looking at in your parent dataset. You do this by making DIMming some variable, and setting the following text statement:

Dim QueryVariable
QueryVariable = "SELECT [RelatedField1], [RelatedField2], [RelatedField3], ... [RelatedFieldN] FROM [RelatedTableName] WHERE [RelatedTableKeyValue] = " + Cstr(ParentKeyValue)

Just so you know,

RelatedField = The fields in your related table that contain the values you're trying to access.

RelatedTableName = The ArcCatalog name of your related table

RelatedTableKeyValue = The Field that is related to the key value of your parent table, i.e. If you have 5 related Inspection records for Lamppost number "00001," All 5 Inspection records should contain a field named "Lamppost_Number" that contains the value "00001." This is your "key" value..

ParentKeyValue = The field in the Parent Table (i.e. Lampposts) that contains the values that the related table (i.e. Inspections) can "relate" to. ("Cstr" turns any numerical value that might be in the table into a string so that SQL can parse it.)

Okay! So we got a nice string here set to a variable! Now we have to EXECUTE it.

We're going to store our results in another variable, but this one will end up being a two-dimensional array. (If you're really green, an array is simply a list of values.)

Oh, and before we do this, we need to set one more variable to call a reference to our Dataset's "Datasource" object. (This will allow VBScript to "lock on" to our dataset and perform operations.)

So, resuming from above:

Dim RelatedArray, MyDatasource

Set MyDatasource = Layers("ParentTableName").Datasource

RelatedArray = MyDatasource.Execute(QueryVariable).ToArray(FALSE)

To access an item in your array, simply call the array variable, in this case "RelatedArray,"
and list the row (record) you want, followed by a comma, and then the number of the field you want, remembering that the first row or column value starts at "0". For example, if I wanted to list all the returned values of, say, the third field I specified in my query in a listbox on my form, I would use the following:

Dim lstResults, ResultArrayCounter, JustSomeVariable
Set lstResults = Applet.Forms("NameOfForm").Pages("Page1").Controls("NameOfListBoxInMyForm")
ResultArrayCounter = 0

For each JustSomeVariable In RelatedArray
      lstResults.AddItem RelatedArray(ResultArrayCounter, 2), RelatedArray(ResultArrayCounter, 2)
      ResultArrayCounter = ResultArrayCounter + 1
Next

What this code will do is loop through every record that was returned from my initial query of my dataset, and add the result as an item in a listbox.

Now some explanation:

lstResults = This variable I set to reference a ListBox object in my form. "AddItem" is a method that adds an item that I specify, with the first "argument" being the actual value (code), and the second argument being what actually shows up in my listbox (alias). I want those values to be the same, that's why both arguments are the same.  You can find a full list of these "methods" you can use to act upon objects in the ArcPad help.

RelatedArray(X,Y) = RelatedArray, again, is a reference to the array I created with my SQL Statement. Because I am attempting to access the third field in my array, I reference "RelatedArray(X,2)" because I know that my first field is zero, my second field is one, and so on.

ResultArrayCounter = This is a numerical value that I use to iterate through the rows (records) in my returned array with the "For/Next" statement. It gets set to 0 to begin with, so, remembering that the first value in an array is always 0, it returns the first value, and then the second, until there are no more records left in the array.

JustSomeVariable = "For/Next" loops require some variable that they can use to iterate through the array. Just Some Variable will start at zero, and count through the array records until it finishes. It's not really that important though.

When this is done, and you enclose all of this in a Sub, whenever you fire this Sub, whether via a button's "OnClick" event, or some other way, like on FormOpen or RecordChange, this will fill your listbox with values from your related table.

Those SQL commands can also be used to do stuff like add records to your related dataset, update records of your related dataset, and all sorts of other stuff, and you can use them all the "Datasource.Execute(SQLSTATEMENT).ToArray(False)" command. If you'd like to see what kind of SQL statements are available, google SQL Statements.

INSERT INTO is a method that allows you to insert new records into a related table.
UPDATE is a method that allows you to update existing related records.

Hope this is helpful to you!

Love,
Brooks

[ForrestKaye]

This is very helpful, Thank you.

The method previously described is basically how I handle things currently but do not use the .ToArray() method. Rather I keep the records in a recordset and utilize the MoveNext() Method. I have heard that using an Array is quicker than cycling through a recordset object.

However my intention was to skip this process entirely by allowing Arc to handle it.  This would eliminate seemingly redundant code since ArcPad already has a process for handling this.  Furthermore it would make custom applets much more dynamic.

[LastingerBrooks]

The reason I had to go with the ".ToArray" method was that I really couldn't find a way of manipulating of accessing or manipulating a related dataset without using a SQL command with "Datasource.Execute."

I guess we could directly modify the related dataset, but only if we could capture a reference to it.

I can normally Set a reference to a dataset using the Set Variable = Layers.("Layername").Datasource method, but my related table is not a layer- It's a table! (Tables.("Tablename").Datasource doesn't work- I tried.) As of right now, I don't believe that related tables are a declared class of objects that we can work with in ArcPad. If you know how to do it though I'd love to hear your solution!

Also- the other advantage you gain with the .ToArray method is that it's a way around ArcPad's stubborn "I can only select one feature 😛 😛 :P" deal.

What I was trying to do with this was keep a running count of all features that were within a specified polygon around my GPS position. Using any sort of Select command on the dataset itself returned me with the "best" result, but not "all" the results. Using an array to store selected values- even though you can't really see them without programming allowed me to select more than one feature at once.

[ForrestKaye]

They way you instanciate a related table as an arcpad object is throught the CreateAppObject("DataSource") method.  Here is an example.

JScript:

var ds = Application.CreateAppObject("DataSource")
ds.Open(Application.UserProperties("dbPath")) //Set User prop earlier as...Appliction.UserUserProperties("dbPath")=Map.Layers("MyLayer").DataSource.FilePath
var strSQL = "SELECT * FROM [MY_TABLE]"
var recs = ds.Execute(strSQL)
if (recs.RecordCount >0){
     recs.MoveFirst()
     while (!recs.EOF){
          Mylistbox.addItem(recs.Fields("NAME").Value)
          recs.MoveNext()
          }
     }

This would create a recordset object and loop through the entire recordset adding all the names to a listbox if you had a column called "NAME".