Auto-populate field depending on value of other fields - ArcPad

charlesprowse · ‎12-02-2014

I need to collect data for structures, part of which is a score-based risk assessment.

The risk assessment has three selectors, the total of which give the overall score.

I would like to have a totals field which adds up the values of the three selectors, is this possible and if so how do I achieve it?

I am able to create layers, add attributes and use arcpad studio to create a form but beyond that I am stuck.

The three selectors are:

Target Value

Size (of structure or part of)

Failure Risk

If anyone is able to provide any help whatsoever I would be extremely grateful.

MosquitoGIS · ‎12-03-2014

Alright, I threw this together pretty quick, but it should give you some basics to get started on and give you the basic ideas on how to work with vbscript in ArcPad.

I created a simple form that with the four fields. Target, Size and Failure and RoH. I then Just gave each (Minus RoH) a drop down with High, Medium, and Low per your example. I have used simply a point Shapefile in mine, but did not create a field for RoH. And in order for it it to store the RoH you would simply have to create a new field in my shapefile and just attach the field I have the on the form to the field of the Shapefile.

In the example I used, it stores the text info in the database (High, medium, low), but if you decide to do numbers, that should be fairly easily to figure out and maybe even simplify.

In my code, I showed how to pull data from form, place data in forms, and even how to display that data in a msgbox (Including one with a second line in the msgbox in case you ever need to do that in the future). It also show examples of if/then statements and case statements. I think with all that, it should be a very helpful set of tools to quickly look at and at least get you started. Hopefully that helps. If you get caught up in anything or have any additional questions, let me know, and I will do what I can to help.

Anyway, I will post the code here as well as attach the files I created to work with including the Shapefile (Which I never tested if it stored, but it should), my .apl (Form), and the vbscript along with that. I also in my vbscript, tried to give instructions on what each piece of code is doing.

.Apl (Form File) Code:

<?xml version="1.0" encoding="UTF-8"?>

</TEXTSYMBOL>

</SIMPLELABELRENDERER>

</SIMPLEMARKERSYMBOL>

</SIMPLERENDERER>

</SYMBOLOGY>

<FORMS>

</LABEL>

</LABEL>

</LABEL>

</COMBOBOX>

</COMBOBOX>

</COMBOBOX>

</PAGE>

</EDITFORM>

</LABEL>

</EDIT>

</LABEL>

</EDIT>

</LABEL>

</EDIT>

</LABEL>

</EDIT>

</PAGE>

</QUERYFORM>

</FORMS>

</LAYER>

</ArcPad>

VBScript:

Option Explicit

'Create Used Variables

Dim RoHV, TxtTargetV, TxtSizeV, TxtFailureV, ValTarget, ValSize, ValFailure

Sub SumFunction

'Set my number Value Varaibles to 0

RoHV = 0

ValTarget = 0

ValSize = 0

ValFailure = 0

' Set my text variables to whatever info is on page1 of the form in that particular name of text field

Set TxtTargetV = EDITFORM.Pages.Item("page1").Controls.Item("CboTarget")

Set TxtSizeV = EDITFORM.Pages.Item("page1").Controls.Item("CboSize")

Set TxtFailureV = EDITFORM.Pages.Item("page1").Controls.Item("CboFailure")

'Using case selectment, Set the Number Varaible for Target to a number based on what was in the "Target" field

Select Case TxtTargetV

Case "High"

ValTarget = 3

Case "Medium"

ValTarget = 2

Case "Low"

ValTarget = 1

Case Else

ValTarget = 0

End Select

'Using case selectment, Set the Number Varaible for Size to a number based on what was in the "Size" field

Select Case TxtSizeV

Case "High"

ValSize = 3

Case "Medium"

ValSize = 2

Case "Low"

ValSize = 1

Case Else

ValSize = 0

End Select

'Doing the same thing above for the "Failure" Field, just using If/Then Statements to introduce to those

If TxtFailureV = "High" then

ValFailure = 3

End if

If TxtFailureV = "Medium" then

ValFailure = 2

End if

If TxtFailureV = "Low" then

ValFailure = 1

End if

' Calculate out what the RoH would be

RoHV = ValTarget + ValSize + ValFailure

'Display the RoH in the RoH field on the form

' EDITFORM.Pages.Item("page1").Controls.Item("TxtRoH") = RoHV

EDITFORM.Pages("page1").Controls("TxtRoH").Value = RoHV

'Display the RoH in a message box to show an example of message boxes.

msgbox "RoH: " & RoHV & " Number." & vbCrlf & vbCrlf & _

"Have a Great Day!", apOKOnly, "RoH Number Title"

End Sub

MosquitoGIS · ‎12-03-2014

Oh and the REM'd out statement towards the end of the vbscript : "'EDITFORM.Pages.Item("page1").Controls.Item("TxtRoH") = RoHV". was a mistake of mine. Ignore it, it shouldn't even be in there.

charlesprowse · ‎12-03-2014

Devin

Thank you so much for doing all of that! I will not be able to try it out until back at work tomorrow but it looks far more technical than I would have cobled together.

Kind regards

MosquitoGIS · ‎12-03-2014

No problem. When I first got started, I remember how hard and frustrating it was to find resources, let alone make sense of the ones I could find. Once I had someone sit down with me and show me some basic stuff, it made it more possible to understand the more complicated stuff and get me started on my own. So I take pride in helping others get their starting point. Plus, who knows? Maybe one day you will be answering my questions. Let me know how it turns out.

And if you open the code I sent in ArcPad Studio it is way easier to see, read and understand.

charlesprowse · ‎12-04-2014

Devin

I have tested the form and it works which is fantastic. I now just need to put together a shp file and form with all the other info we need to collect and incorporate your stuff into it. Hopefully I should be able to do that with all your help.

One question, I notice there is a query form, how do I create one of those?

Thanks

MosquitoGIS · ‎12-04-2014

To put things together fast, what I do is create a shapefile that will hold the data that I want. Once that is created, I use the ArcPad toolbar in ArcMap (The same one you use to transfer data in and out to a mobile unit), and if you select the shapefile in your table of contents and then press the dropdown on the ArcPad toolbar (where it says "ArcPad Data Manager"), and press "Export Shapefile Symbol for ArcPad" it will create an Apl (Form) file that contains all your fields and uses the symbology setup you have in ArcMap as well as that query form.

I then open the form in ArcPad Studio and remove, adjust, and change out whatever I want (Don't need ID field, so delete textbox and label for it. Name field should be a dropbox, so I will delete the textbox, and put a ComboBox, but then enter the settings for that ComboBox, give it a name, and make sure it is attached to the correct field of the database, has the values I want, etc., etc., Move a textbox and label up that I want to keep, to replace the ID field I just removed, etc.) There may be an easier way to do it nowadays, but I have been making forms since ArcPad 6/7 and so I know most everything code wise (No ArcPad Studio, just a text editor). So I have no idea at all how to create a query form through ArcPad Studio. ArcPad Studio confuses me on how to create anything. So I just use it for visual and light editing of my form basically just because it is quicker and easier to test that way than doing it via code.

I actually don't use the query forms and typcially take them out even though I should probably take advantage of them, as they look awesome. I just wasn't introduced to them until later in my career and I'm an old dog stuck in my ways enough to not decide to adopt them in yet.

Short Version:

I don't know how to put a Query form in using ArcPad Studio. It is there because I have ArcMap Symbology Exporter build a basic form and transfer my ArcMap symbology from my newly built shapefile and it creates the query form automatically with it.

charlesprowse · ‎12-05-2014

Devin

I have managed to make up what I need and get it working but am stuck on one final thing. When I get a numerical answer to my risk assessment I want it to give me a corresponding text answer in another field but I cannot work out the script to do it. If I get say, 11 in the RoHTotal field I want it to display '1/3 Risk' in the RoH field of the form and DB, is this possible?

Thanks in advance

My working script so far is:

Option Explicit

'Create Used Variables

Dim RoHTotal, RoHV, TxtTargetV, TxtSizeV, TxtFailureV, ValTarget, ValSize, ValFailure

Sub SumFunction

'Set my number Value Varaibles to 0

RoHTotal = 0

ValTarget = 0

ValSize = 0

ValFailure = 0

' Set my text variables to whatever info is on page1 of the form in that particular name of text field

Set TxtTargetV = EDITFORM.Pages.Item("page1").Controls.Item("CboTarget")

Set TxtSizeV = EDITFORM.Pages.Item("page1").Controls.Item("CboSize")

Set TxtFailureV = EDITFORM.Pages.Item("page1").Controls.Item("CboFailure")

'Using case selectment, Set the Number Varaible for Target to a number based on what was in the "Target" field

Select Case TxtTargetV

Case "Target 1"

ValTarget = 1

Case "Target 2"

ValTarget = 75

Case "Target 3"

ValTarget = 140

Case "Target 4"

ValTarget = 210

Case "Target 5"

ValTarget = 280

Case "Target 6"

ValTarget = 350

Case Else

ValTarget = 0

End Select

'Using case selectment, Set the Number Varaible for Size to a number based on what was in the "Size" field

Select Case TxtSizeV

Case "Property"

ValSize = 0

Case "1 = >450mm"

ValSize = 1

Case "2 = 450-260mm"

ValSize = 2

Case "3 = 250-110mm"

ValSize = 3

Case "4 = 100-25mm"

ValSize = 4

Case Else

ValSize = 0

End Select

'Using case selectment, Set the Number Varaible for Failure to a number based on what was in the "Failure" field

Select Case TxtFailureV

Case "1 = 1/1->1/10"

ValFailure = 10

Case "2 = 1/10->1/100"

ValFailure = 20

Case "3 = 1/100->1/1K"

ValFailure = 30

Case "4 = 1/1K->1/10K"

ValFailure = 40

Case "5 = 1/10K->1/100K"

ValFailure = 50

Case "6 = 1/100K->1/1M"

ValFailure = 60

Case "7 = 1/1M-1/10M"

ValFailure = 70

Case Else

ValFailure = 0

End Select

' Calculate out what the RoHTotal would be

RoHTotal = ValTarget + ValSize + ValFailure

'Display the RoHTotal in the RoHTotal field on the form

' EDITFORM.Pages.Item("page1").Controls.Item("TxtRoHTotal") = RoHTotalV

EDITFORM.Pages("page1").Controls("TxtRoHTotal").Value = RoHTotal

End Sub

MosquitoGIS · ‎12-05-2014

If they are exact numbers you can use "Case" to do it, but if you are wanting ranges, then you will have to use IF/Then statements. Since I am assuming you are going to be working with ranges, this is what you would want to do.

If RoHTotal > 100 and RohTotal < 200 then

EDITFORM.Pages("page1").Controls("TxtRoHTotal").Value = "1/3 Risk"

End if

This basically says, if the value of RoHTotal is between 100 and 200 then the TxtRohTotal textbox should say 1/3 RIsk. And as long as you have that Text box linked up to a field in your database, it will store it. I would recommend putting the text box read only, so that users can't edit it maunally.

Give that a try and let me know how it goes.

MosquitoGIS · ‎12-05-2014

Oh and just a note, anything in the code that has an apostrophe in front of it is called a REM statement. REM stands for remarks. It is basically a way for you to tell the computer to skip that line and not run it as code as it is a human remark and not actual code. They are mainly supposed to be used to type comments or remarks in your code for other people to read for helping explain what a certain part of a code is doing or what not. You can also use it to have it skip a certain line of code when you are troubleshooting (That way you don't have to completely remove that line and then put it back in later.).

I'm saying this, as there is a Rem'd out piece of code I left in accidentally (I referenced it earlier that it could be removed). I saw in your code post back, it was updated with new info and still there. You can remove it, Since it is Rem'd out, it isn't running anyway. And all the other Rem'd statements are just comments I left and are not actually part of the code, so technically are not required to update, although recommended for future use. Just a little lesson on Rem statements. They are not all the same in all programming languages (Some use a pound sign then a space, some you type REM then a space, etc.). In vbscript, it is just a good 'ole apostrophe.

charlesprowse · ‎12-07-2014

Devin

I now have a fully functioning form thanks to you!

I am very grateful for the time you have taken to help me out and also for explaining the basics.

Kudos