VBS 999999 Error

2247
18
Jump to solution
10-04-2013 03:39 AM
Liam_
by
New Contributor II
Hi,

I have written a script for the model builder's calculate field function in VBScript but I keep getting the 999999 error.

I have gone over my code several times but cannot seem to find a reason for it not to be able to run.


INPUT TABLE: JOIN_CarData_RoadLinks
FIELD NAME: JOIN_CarData_RoadLinks.OrientationOfCar
EXPRESSION: CarOrientation
[PHP]
Dim CarOrientation

Dim RoadOrientation
RoadOrientation = [JOIN_CarData_RoadLinks.Orientation]

Dim RoadCompass
RoadCompass = [JOIN_CarData_RoadLinks.CompassA]

Dim RoadCompassMax
RoadCompassMax = RoadCompass + 170

If RoadCompassMax > 360 Then
RoadCompassMax = RoadCompassMax - 360
ElseIf RoadCompassMax < 0 Then
RoadCompassMax = RoadCompassMax + 360
End If

Dim RoadCompassMin
RoadCompassMin = RoadCompass - 170

If RoadCompassMin > 360 Then
RoadCompassMin = RoadCompassMin - 360
ElseIf RoadCompassMax < 0 Then
RoadCompassMin = RoadCompassMin + 360
End If

Dim PointBearing
PointBearing = [JOIN_CarData_RoadLinks.Bearing]

If PointBearing > RoadCompassMin And PointBearing < RoadCompassMax Then
CarOrientation = RoadOrientation
End if

If PointBearing < RoadCompassMin Or PointBearing > RoadCompassMax Then
If RoadOrientation = "-" Then
  CarOrientation = "+"
End if

If RoadOrientation = "+" Then
  CarOrientation = "-"
End if
End if
[/PHP]
I have tried including and excluding the "JOIN_CarData_RoadLinks." in my field allocations and replacing the declared variables used in the body of my code with the actual table's fields. I have copied my code into a VBScript IDE to check the syntax and it couldn't find anything wrong.

Any help would be appreciated.

Thanks,


Liam.
0 Kudos
1 Solution

Accepted Solutions
RichardFairhurst
MVP Honored Contributor
Hi,

Thank you, I now understand why I cannot see both the entries for each TOID with a join.

I think I have the answer to your question: the summary cannot work with the null values as null is not automatically assumed to be 0 in a numeric field? - Therefore it would be looking for the min or the max of nothing?

Thanks again,

Liam.


Try the summary.  You are wrong about the assumption of the Summary.  0 is not used in the place of Null.  Null is preserved for Min and Max in a Geodatabase table if Null is the only choice.  It is disregarded if there is any non-Null choice.

So, I lied a little about the output.  If there is a Null in all input entries then the output of both Min and Max is Null, as I showed.  However, if one of the two entries is not Null and the other is Null, the Null entry is ignored and the Min and Max of the two records would just be the non-Null value.  That is what you want anyway, since if you have a non-Null value you don't care about the Null value.  You really only need the Max of FromTo and ToFrom, since you don't care about the Null if a non-Null value exists.  The only time you might care about a Min and Max is if you somehow had two different non-Null values for a single direction, which as I understand it, makes no sense for your model.

So if this was the input:

CarData_RoadLinks_Statistics
RoadLinkTOID ToFromTotalStats FromToTotalStats
1 10 Null
1 Null 1
2 5 Null
3 Null 7

This would actually be the output to a geodatabase table.

CarData_RoadLinks_Statistics_Statistics
RoadLinkTOID Min_ToFromTotalStats Max_ToFromTotalStats Min_FromToTotalStats Max_FromToTotalStats
1 10 10 1 1
2 5 5 Null Null
3 Null Null 7 7

You only have one slot to fill for each direction per ITN_RoadLinks feature and it is impossible to fill a single directional slot with two different values.  You must make a choice if Min and Max are different or take their sum or their average.  However, you should always disregard the Null value if there is any non-Null value.  You would only preserve the Null output if all the entries are Null.  That is exactly the behavior of the Summary with Min and Max when Null values are involved. 

Even if you requested the Mean, the Null values are disregarded when you do a Mean of two records where one has a Null value and the other record has a non-Null value.  In that situation, the Mean will just be the non-Null value for those two records.  The Mean will not be half of the non-Null value, which is what the assumption of 0 in the place of Null would produce.

Anyway, how would you make a single ITN_RoadLinks feature hold both a Null and a non-Null value at the same time for the ToFromTotalStats?  How would you make a single ITN_RoadLinks feature hold both a Null and a non-Null value at the same time for the FromToTotalStats?  You can't.  You would have choose one or the other.  The summary makes the choice the same way you would by only considering Null values when no non-Null values exist.  If any non-Null values exist the Nulls are disregarded entirely.  Null and non-Null values are never mixed together by the Summary tool.

Just in case you ever have two different non-Null values for a specific direction on a specific segment, how would you make a single ITN_RoadLinks feature hold two different non-Null values at the same time for either of those directional fields?  You can't.  You would have choose the Sum, the Min, the Max, or the Mean.  Those summary values are what the Summary Statistics tool gives to you to translate two or more values into a single value for a single slot.

If this finally answers your question, be sure to mark the thread as answered for the benefit of others.

View solution in original post

0 Kudos
18 Replies
RichardFairhurst
MVP Honored Contributor
The first thing that comes to mind is that you may be failing to deal with Null values in the calculation.  Since this code involves a join, any record in the primary table that is unmatched in the joined table will have Null values passed to the calculation.  Nulls often generate this kind of error when comparisons are attempted in VB Script.  So you may need to add logic to the model to ensure that no Null value records get processed by the calculation or add IsNull checks to the calculation and ensure that a valid CarOrientation output occurs for those cases without comparing the record values.

Other than that, my approach with these kinds of problems is to take the script intermediate results up to that point and use the actual feature classes/tables in Desktop to run the field calculation there.  I would cut out all of the code except for the first logical test and only process that to see if it generates an error, then I would paste back each additional test until I hopefully threw the error so I could isolate the part of the code that is responsible for the problem.  Tedious, but it usually works.
0 Kudos
Liam_
by
New Contributor II
Thanks again for your help, I will give your suggestions a whirl and report back.
0 Kudos
Liam_
by
New Contributor II
Hi,

I managed to solve the problem: -

I checked for null values throughout the database but the database had none. I then decided to export the table with the join to the same gdb, just called it: 'JOIN2_CarData_RoadLinks'. This made the join 'permanent', my code then whizzed through and completed successfully.

It would appear that you cannot perform comparative VBScripts using the field calculator on a joined table. Or at least my installation won't :)

Thanks again for your help, another 'bug' to add to the 'book of workarounds' ;)

Liam.
0 Kudos
RichardFairhurst
MVP Honored Contributor
Hi,

I managed to solve the problem: -

I checked for null values throughout the database but the database had none. I then decided to export the table with the join to the same gdb, just called it: 'JOIN2_CarData_RoadLinks'. This made the join 'permanent', my code then whizzed through and completed successfully.

It would appear that you cannot perform comparative VBScripts using the field calculator on a joined table. Or at least my installation won't :)

Thanks again for your help, another 'bug' to add to the 'book of workarounds' ;)

Liam.


Join calculations work and are the most common calculation I do.  GDBs are very good with them.  They have worked for 9.2 to 10.1 for me.  So it could be something peculiar to the complexity of the calculation or to the database configuration.  I have written if then logic for them, but I don't normally have as many conditions as your calculation included.  This particular calculation is more complex than most and has many failure points, null values being only one of them.  It is also not clear that you checked for an incomplete match of all records in the join itself, which would create join records with Nulls, or how much you broke the calculation down to its component parts to test each calculation step.

You have a solution and workarounds are common in ArcGIS, but I don't want you or anyone else to conclude that VBScript join calculations commonly do not work.  They most certainly do work and I have never had to use a workaround for them.  So, as far as I am concerned, the probability is that the bug is in the calculation you wrote, not ArcGIS.

Rereading your post I realize how ambiguous your description of your set up and objectives are.  I now believe that you may have used the incorrect Join table name in the calculation, since it looks more like an output variable, relationship class, or joined layer name than the names of the unjoined feature classes you actually have on disk.  You have to use the underlying feature class names of the original unjoined feature classes/tables on disk as the table names in Join calculations, not the name of the joined layer or table.  You should write this calculation with the Calculation editor and use it to insert all field names into the calculation.  I rely exclusively on the Calculation Editor for field names of joined data and never custom write joined field names into any calculation I compose when bugs like this appear.  I only custom write the comparative logic part.

The other possibility is that you were trying to calculate values for one of the fields of the Joined table, not the primary table.  That is not allowed and is not a bug, it is by design.  Only fields of the primary table can be calculated in a join.  If calculating values into a joined field was what you were trying to do, that would explain why an export worked while the calculation didn't.  Once the feature class was exported the field you were calculating would be an actual part of the feature class itself and not in an externally joined table/feature class.  If calculating a joined field was your objective than the only solutions are to reverse the join order so that the field to be calculated is in the primary feature class/table or to export the feature class as you did.
0 Kudos
RichardFairhurst
MVP Honored Contributor
I decided to construct two tables based on how I interpreted your set up would have to be in order for the calculation to work and used your exact posted calculation on it.  The calculation worked in Desktop.

Here is how the data was configured:

Primary table fields (any gdb compliant primary table name would work.  I have named mine "OrientationOfCar"):
ID (Unique ID for the join)
OrientationOfCar (text field to be calculated)

Join Table fields (only a join table named "JOIN_CarData_RoadLinks" will work for this particular calculation):
ID (Unique ID for the join)
Orientation (text field)
CompassA (double)
Bearing (double)

I have attached two screenshots of the joined tables before and after the calculation.

If your configuration differed from this in any significant way that would be a problem.  For example, if Bearing was not in the join table but was in the OrientationOfCar table (which seems more logical to me than what I set up) that would be a problem.  If the OrientationOfCar field being calculated was in the JOIN_CarData_RoadLinks table, that would be a problem.  If the join order of the tables was reversed that would be a problem.  If the name of the joined table was not actually "JOIN_CarData_RoadLinks" that would be a problem.

I also assumed you were running this in Model Builder.  I don't use VBScript scripts and at 10.1 I do not have the option to export Models to VBScript.  Only Model Builder models and Python scripts are supported at 10.1.

Model Builder had no problem with this calculation.  I built a model to emulate what I did in Desktop and it ran fine.  A screenshot of the model is attached.

I also assumed you knew that only Layers and Table Views, not feature classes on disk, can be the primary layer/table in a Join.  So before creating a Join the Make Feature Layer or Make Table View tool has to be run if the data is pulled from ArcCatalog.  You will note that the model I built uses the Make Table View tool before creating the join.

I exported the script to Python and it also ran.  Prior to 10.0 the Python script export would have failed, because codeblocks using VBA were not supported.  At 10.0 and after codeblocks use VBScript, not VBA, and should work (at least they do now at 10.1).  Here is the Python script Model Builder created.

# -*- coding: utf-8 -*-
# ---------------------------------------------------------------------------
# testVBScriptCodeBlock.py
# Created on: 2013-10-05 12:40:22.00000
#   (generated by ArcGIS/ModelBuilder)
# Description: 
# ---------------------------------------------------------------------------

# Import arcpy module
import arcpy


# Local variables:
OrientationOfCar = "C:\\Documents and Settings\\rfairhur\\My Documents\\ArcGIS\\Default.gdb\\OrientationOfCar"
JOIN_CarData_RoadLinks = "C:\\Documents and Settings\\rfairhur\\My Documents\\ArcGIS\\Default.gdb\\JOIN_CarData_RoadLinks"
OrientationOfCar_View = "OrientationOfCar_View"
OrientationOfCar_View__3_ = "OrientationOfCar_View"

# Process: Make Table View
arcpy.MakeTableView_management(OrientationOfCar, OrientationOfCar_View, "", "", "OBJECTID OBJECTID VISIBLE NONE;OrientationOfCar OrientationOfCar VISIBLE NONE;ID ID VISIBLE NONE")

# Process: Add Join
arcpy.AddJoin_management(OrientationOfCar_View, "ID", JOIN_CarData_RoadLinks, "ID", "KEEP_ALL")

# Process: Calculate Field
arcpy.CalculateField_management(OrientationOfCar_View__3_, "OrientationOfCar.OrientationOfCar", "CarOrientation", "VB", "Dim CarOrientation\\n\\nDim RoadOrientation\\nRoadOrientation = [JOIN_CarData_RoadLinks.Orientation]\\n\\nDim RoadCompass\\nRoadCompass = [JOIN_CarData_RoadLinks.CompassA]\\n\\nDim RoadCompassMax\\nRoadCompassMax = RoadCompass + 170\\n\\n    If RoadCompassMax > 360 Then\\n    RoadCompassMax = RoadCompassMax - 360\\n    ElseIf RoadCompassMax < 0 Then\\n    RoadCompassMax = RoadCompassMax + 360\\n    End If\\n\\nDim RoadCompassMin \\nRoadCompassMin = RoadCompass - 170\\n\\n    If RoadCompassMin > 360 Then\\n    RoadCompassMin = RoadCompassMin - 360\\n    ElseIf RoadCompassMax < 0 Then\\n    RoadCompassMin = RoadCompassMin + 360\\n    End If\\n\\nDim PointBearing\\nPointBearing = [JOIN_CarData_RoadLinks.Bearing]\\n\\nIf PointBearing > RoadCompassMin And PointBearing < RoadCompassMax Then\\n    CarOrientation = RoadOrientation\\nEnd if\\n\\nIf PointBearing < RoadCompassMin Or PointBearing > RoadCompassMax Then\\n    If RoadOrientation = \"-\" Then\\n        CarOrientation = \"+\"\\n    End if\\n\\n    If RoadOrientation = \"+\" Then\\n        CarOrientation = \"-\"\\n    End if\\nEnd if  ")


So something you are doing is different from what I did.
0 Kudos
Liam_
by
New Contributor II
Hi,

Thank you again for your help and insight, I have reviewed the process that I used to calculate the field.

I sorry I didn't give more details about my database, I did not realise that the problem could be in the Join itself.

I am working with the OS ITN road network dataset and although I have had a very poor success rate with the network analyst (http://forums.arcgis.com/threads/88084-Productivity-Suite-Not-recognising-that-roads-are-on-differen...) the underlying tables and feature classes are very well thought out and easy to work with.

I am attempting to test the feasibility of a vehicle navigation solution in ArcMap using the Network Analyst extension that can calculate the best route to a destination based on fuel economy variables (as oppose to drive times or just the length of journey). Fuel economy values have been calculated based on information collected from peer reviewed literature for average light duty vehicles. The said values have been added to the roadlinks dataset as an impedance value so that each roadlink has a cost associated with its transversal. As the route finder transverses each roadlink, the impedance values accumulate which results in a total �??cost�?? for the journey in litres per kilometre.

An overview of my database semantics can be seen here.

To verify and assess the accuracy of the solution, routes have been driven in a Volkswagen Golf with GPS tracking and logging of vehicle engine management system outputs. This second dataset was created by myself and converted from KML points to 8 point datasets stored in an Arc Geodatabase according to the day/route taken. All of the points feature classes were then amalgamated into a single CarData feature class for statistical analysis to test the correlation of real world data against literature data.

The original intention was to compare impedance values with real world route values, problems with the Productivity Suite have forced me to do a comparison on a road segment by road segment level (but that's a completely different problem).

Therefore, in order to compare the data (I intend to use MS Excel), I have processed the CarData points feature class: -


  1. Performed linear directional mean on RoadLinks.

  2. Add field to CarData to store positive/negative orientations

  3. Joined the CarData to Roadlinks to copy across the Road TOIDs

  4. Removed Join

  5. Joined the CarData to Linear Directional Mean table

  6. Calculated the orientation (+/-) of the car data based on bearings.

  7. Ran summary statistics for each road cased on TOID and Orientation

  8. Multiplied the average l/km by the length of the roads.


So I currently have [CarData_RoadLinks_Statistics] as a standalone table and [ITN_RoadLinks] as a File Geodatabase Feature Class. Now I just need to copy the ToFrom and FromTo totals in the stats table to the RoadLinks F.Class where the TOIDs match. Then I can export to excel and run correlation tests. There will be nulls in these field calculations because a FromTo value cannot be put in a FromTo value and vice versa. I joined the tables together Roadlinks to Stats so I am only writing to the primary table. My data looks a little something like this: -

The calculation:
[RoadLinks.FromTo] = [Stats.FromTo]
[RoadLinks.ToFrom] = [Stats.ToFrom]

Sample Data:
 _____________________________________________________________________________
|            RoadLinks                       |                Stats           |
|_____________________________________________________________________________|
|TOID         FromTo              ToFrom     |      FromTo             ToFrom |
|_____________________________________________________________________________|
|1            null                null       |      10                 null   |
|2            null                null       |      null               10     |
|3            null                null       |      null               5      |
|4            null                null       |      null               6      |
|5            null                null       |      3                  null   |
|                                            |                                |


Therefore if I copy the values from Stats.FromTo to RoadLinks.FromTo it will have to copy a null across. To combat this I wrote into the calculate a test for null: If Not IsNull([FromTo]) then .....

Even so, it still cannot copy, I get an ArcMap has crashed error and the whole program closes. I has occurred to me that it may not like to join a feature class to a standalone table. However, at the end of the day it is just a simple append query. I am now faced with the task of exporting all the databases into MS Access and finishing my project there. But this would defeat the object of doing the ArcMap project as I could have then done the entire project in Access (especially as the productivity suite has a bug [TFL reports that they get the same bug] which means I cannot use network analyst to it's full extent).

Again, thank you for your help and insight, I cannot express how much I appreciate it!

Liam.

EDIT: I have also tried running the field calculations outside of the model builder. I have tried turning editing mode on an off, right clicking in the attributes table and running straight from the toolbox. All of which cause ArcMap to crash.
0 Kudos
Liam_
by
New Contributor II
I've had a look around on the web and I'm sure that the problem is to do with joining an ordinary table to a feature class, but I still have had no luck finding a solution.

Has anyone come across this problem before and found a way around it?

Thanks,

Liam.
0 Kudos
RichardFairhurst
MVP Honored Contributor
I've had a look around on the web and I'm sure that the problem is to do with joining an ordinary table to a feature class, but I still have had no luck finding a solution.

Has anyone come across this problem before and found a way around it?

Thanks,

Liam.


Liam:

I work in transportation and, although I don't use Network Analyst, I do use linear referencing of point and line events almost everyday.  So I am very familiar with working with data that has From and To direction dependent information.  I also transfer data between feature classes and tables almost everyday using Joins and the Field Calculator with no problems.  I have worked this way since at least ArcGIS 9.0 and currently am using ArcGIS 10.1 SP1.

I looked at your semantics doc and at your example, but you have not clearly explained the key items I need to understand.  At some level you went from too little information to information overload using a shot gun approach of dumping everything.  I need to focus to several key things.  So lets take them one by one.

1.  I need to know the database type of the feature class and table you are working with.  This is the biggest factor affecting the join.  Do you have the feature class and table in a geodatabase.  If they are in a geodatabase are they in the same geodatabase or different geodatabases?  What type(s) of geodatabase are involved?  SDE, file geodatabase, or personal geodatabase?  Is one in a geodatabase and the other isn't, or are both outside of a geodatabase?  If the feature class is not a in a geodatabase is it in a shapefile, a coverage, or a CAD file?  If the table is not in a geodatabase is it in a dbf table, an info table, an Excel table, or a text file?  Are both in an editable workspace directory or does either have edit restrictions on their workspace directory?  There is no such thing as an "ordinary" table and each specific table type and feature class/table type mix may introduce different problems.

2.  What fields are you joining between the tables?  Name the fields in the feature class and the table and give their data type(s) (long, short, text, double, float, etc).  Does the join field ever have Null values in it in either the feature class or table?  If it is a text field, do the fields share exactly the same value in both tables?  Provide an example of a shared field value, especially if the fields are text fields.  Do you have to join the feature class to the table on two different feature fields, with one join based on the From direction data and the other join based on the To direction data?  More than one join could cause several problems related to Null values and records matching.

3.  What is the relationship type between the feature class and the table?  One-to-one, many-to-one, one-to-many, or many-to-many?  Joins only support one-to-one and many-to-one relationships, meaning that you can have only one table record match any given feature when you use the feature class as the original table and join the table to it.  If the relationship is one-to-many or many-to-many, a join will not support that relationship fully.  Meaning that ArcMap does not fully support one feature having two or more table records joined to it.  It will randomly choose one table record associated with a given feature and ignore all of the others table records when you create a join.  The complex relationships can cause several problems and may need several modifications to your workflow to achieve a successful data transfer.

4.  How many features are in the feature class.  How many records are in the table?

So focus on giving clear answers to these questions.  They are critical to knowing how to work with your data correctly in everything you intend to do.  I have dealt with almost every one of these scenarios and have found the ones that work and workarounds for many that don't work directly.
0 Kudos
RichardFairhurst
MVP Honored Contributor
I would never incorporate Excel into a GIS process.  It is not a database and it is unreliable.  I never use it directly in ArcGIS and recommend that no one use it directly in ArcGIS.  I always convert it to a real database.  ArcGIS can be very particular about what works well and what does not, especially when you use joins, and almost every day I have to convert data from one format to another to get it to work reliably.  IMHO, Excel is only for those that cannot use a database and GIS professionals should never rely on it as a direct data source without conversion and data scrubbing.

I would never call any Excel spreadsheet an "ordinary table" in any sense of those words.
0 Kudos