nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

VBS 999999 Error

Liam_

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.

Find more posts tagged with

Accepted answers

RichardFairhurst

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.

All comments

Liam_

Hi,

Thanks it worked and I now have a one-to-one relationship with my ITN_RoadLinks table with the correct amount of records (1,117). What is also really useful is the frequency field as I can now easily identify roads that I travelled on in both directions, which maybe useful later on 🙂

As for my answer to your question, I did get confused as to what the problem may have been with what you were suggesting as when I quickly ran it it appeared to work. You got me looking for a problem that was not there 😛

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.

- That is correct, my model and methodology would not allow two real world costs to be associated with a single direction at this point in the process. When I had all my points spatially joined to the roads that they were attached to, I had run the summary tool to get the mean averages.

Thanks again for all your help, I can now transfer my values to the other fields the same way as before 🙂

RichardFairhurst

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.

Liam_

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.

RichardFairhurst

I am not surprised by the records loss you see if the relationship is in fact a One-to-Many relationship.

Stop joining the data together and stop introducing new names for your objects like "JOIN SAMPLE DATA:" and "INDIVIDUAL TABLE SAMPLE DATA:". Get me down to the raw data before the join.

A joined table cannot be edited while it is joined to another table. That is an intentional behavior and is especially required with a One-to-Many relationship since ArcGIS cannot interpret that kind of relationship through a join. One-to-Many relationships are not supported by joins.

Since there are usually 2 table records you must convert it to a single record to use it through a join. Either they must be split into two tables to use a join to transfer the data, with one containing the first instance of a RoadLinkTOID value and the second containing the second instance of the same RoadLinkTOID value. Then you would do two different joins and make a choice if any overwrites would occur as a result of the second join. Otherwise you need to summarize the table records again so that there is only one record in the join table. You can never see both records through the join without exporting the join while both are in the same geodatabase. But the effect of the export would be to duplicate the feature shape into two overlapping shapes, which you do not want.

I advise you to collapse the Summary table into a one-to-one relationship and get rid of the Null values. If I understand your data correctly this can be done with the Min and Max operator in a summary when only two records need to be collapsed. What I currently imagine your data to look like in reality is shown below:

ITN_RoadLinks:
RoadLinkTOID CarDataToFromTOTAL CarDataFromToTOTAL
1 Null Null
2 Null Null
3 Null Null

CarData_RoadLinks_Statistics
RoadLinkTOID ToFromTotalStats FromToTotalStats
1 10 Null
1 Null 1
2 5 Null
3 Null 7
(2 records with the same RoadLinkTOID value matching the one above)

What I want to do is convert the CarData_RoadLinks_Statistics arrangement using another Summary to:

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

This new table structure can be joined to the features as a One-to-One relationship join and unlike a One-to-Many relationship no records will fail to show up in the join. Now, tell me why I cannot do the additional summary I am proposing with your CarData_RoadLinks_Statistics table to convert your existing One-to-Many relationship into a One-to-One relationship.

Liam_

Hi,

Thanks for taking a look, sorry I didn't add in enough detail; I have updated the document and added samples of the two datasets as well. They can be found here. There was also only 1,117 records remaining after the join - which is odd.

The relationship between the roads and the stats is one to many as one road TOID in ITN_RoadLinks can have 2 entries in the CarData_RoadLinks_Statistics that contain ToFrom and FromTo car data.

Example: -
Road x is 1km long and is on a hill, therefore there will be a big increase in the fuel used going up the hill (FromTo) than down the hill (ToFrom). Therefore, Road x can have an entry in the statistics table twice, each representing the cost associated with the direction of travel:

Road x going FromTo @ 10l/km = Total Fuel Used: 10 litres
Road x going ToFrom @ 1l/km = Total Fuel Used: 1 litre.

The FromTo and ToFrom directions are calculated by Ordnance Survey and represented by a '+' for FromTo and a '-' for ToFrom in the Orientation field in ITN_RoadLinks.

Therefore the following scenarios can arise: -

Road x may have 2 entries in the statistics table as it was travelled upon in both directions.
Road y may have just one entry in the statistics table as it is a one way carriageway on a motorway.
Road z may have no entries in the statistics table as it was not travelled on in either direction.

However, the entry in ITN_RoadLinks for Road z cannot be deleted because the theorised l/km value attached to it may be used later on.

Roads x, y & z all have theoretical l/km stored in the FromToTotal and ToFromTotal fields - these are independant from the real world l/km values stored in the statistics table.

Therefore, to compare the statistical correlation between the theoretical and real world observations - the real world values (held in the statistics table) need to be copied to the correct fields in the ITN_RoadLinks table: CarDataToFromTOTAL & CarDataFromToTOTAL.

I have tried manually editing the fields, but it won't allow me to type anything into the cells. I tried placing the tables in the same geodatabase, but I still couldn't edit at all - not even just a small selection of records.

Thanks again,

Liam.

RichardFairhurst

I looked at the hyperlink of the join with some data in Number 4 of your post that responded to my questions. It did not answer some of the questions I need answered.

1. I could not tell what fields were coming from what data source in the example. If you used aliases (which you almost certainly did) convert the example to show the actual full field names so I can see how they would have to be used in a calculation. Also you mentioned fields that I do not see in the example data: ToFromTotalStats & FromToTotalStats from CarData_RoadLinks_Statistics and CarDataToFromTOTAL & CarDataFromToTOTAL in ITN_RoadLinks. What field in your example data corresponds to the RoadLinkTOID field?

2. You also said you only Keep Matching Records using the join. How many features actually appear once the join is in place? The answer to this question would partly help answer the next question.

3. You also did not answer another critical question: What is the relationship between the ITN_RoadLinks feature class and the CarData_RoadLinks_Statistics table?

One-to-One - the RoadLinkTOID values are unique for each feature and each table record and only one matched table view record is possible for any given RoadLinkTOID value.
Many-to-One - the features do not have unique RoadLinkTOID values, but every table record has a unique RoadLinkTOID value.
One-To-Many - every feature has a unique RoadLinkTOID value, but every table record is not unique for the RoadLinkTOID values.
Many-To-Many - the RoadLinkTOID values are not unique for the features or the table records.

If the relationship is One-to-One, then the ITN_RoadLinks feature class only contains a small subset of data related to the CarData_RoadLinks_Statistics table. At most 1,328 features are matched in the table. The join would not hide any records of the CarData_RoadLinks_Statistics table that are actually associated with the features. This relationship is unlikely.

If the relationship is Many-to-One, then CarData_RoadLinks_Statistics could potentially relate to all or any subportion of the ITN_RoadLinks feature class and the matches would most likely greatly exceed 1,328 features. Potentially every record in the ITN_RoadLinks feature class could be matched by the CarData_RoadLinks_Statistics table. The join would not hide any records of the CarData_RoadLinks_Statistics table that are actually associated with the features. The effect of the join is that in memory you are presented in essence with a table view that shows a one-to-one version of the feature and table records combined. This relationship is the most likely and can contribute to memory instability.

A One-to-Many relationship would mean that less than 1,328 features are involved in the join and potentially many of the records in the CarData_RoadLinks_Statistics table are not seen through the join. This relationship is the least likely.

A Many-to-Many relationship would involve a multiplication of features if fully joined that could be in the millions of features if the relationship was converted to a One-To-One relationship. Many of the records in the CarData_RoadLinks_Statistics table associated with the features are not actually seen through the join. This relationship is the second most likely, but the worst to deal with and the most likely to result in memory instability.

--------

If the relationship type is Many-to-One then a python script that uses two cursors can be fairly easily written that would convert the CarData_RoadLinks_Statistics table to a dictionary to transfer the data. This script should have a much lower memory footprint than a join if written properly. A Many-to_many relationship would be almost impossible to work out without blowing out the memory.

Liam_

Hi,

Thank you for your quick reply 🙂

This is a different problem from the original post, I kept it in the same thread as it is the same project I didn't want to clog up the forum with all my problems.

Your solution for the first problem should be correct but it didn't work for me. I had to export the join by saving it as one single feature class. But this could point to a system resource issue or a deeper problem with my installation.

In my current problem, the join executes successfully and I am able to view the attributes table (after a long wait) but I cannot run any field calculations via model builder, toolbox or the attributes table: calculate field without ArcMap crashing. I also always run Arc in administrator mode, as I have found that read/write errors occur frequently without Arc having admin rights.

I suspect you could be right about the resources though, but despite my computer being old; it has had a few upgrades. Here are my specs: -

Processor: Intel Core 2 CPU X6800 @ 2.93GHz Overclocked to 3190 Mhz.
Installed Physical Memory (RAM): 4.00 GB (32-bit Windows 7 though)

Although my system is overclocked, it is very stable. The CPU takes a beating from some of my other applications quite regularly. The RAM has been in a while and shows 100 faults per second which is normal for the amount I have. The GPU is powerful, not that it will have much to do with Arc and my hard drive seems to be healthy. I also compact the database regularly but I'm not sure if it performs the same function as 'Compact and Repair' in MS Access. On a side note, the Compact and Repair function in MS Access usually fixes most issues like this.

I will try to perform some manual edits on the table, cutting it down in size and your suggestion on combining the geodatabases tomorrow and report back.

Thanks again for your help,

Liam.

RichardFairhurst

Thank you. That information is a much better starting point to work out your problem. I am assuming that this information is relevant to the original calculation you proposed in your very first post. My comments are based on that assumption. However, I am concerned that you may have resolved that problem and are giving me information that relates to a different problem that you are now having. So please let me know if your original calculation is still the problem you are trying to solve.

My tests showed that your original calculation worked fine when it was applied to a feature class and table that matched the prefix and field names used in the calculation. However, in your very first post you proposed a calculation that used a prefix to the fields called JOIN_CarData_RoadLinks. That prefix makes no sense with the data you have described in your last post and definitely would result in an error. So lets start there.

Since both of these data sources mentioned in your last post are within File Geodatabases, after the join is created, the calculation field prefixes that would reference fields within the Feature Class would have the form: ITN_RoadLinks.field. Calculation field prefixes that would references fields within the Table would have the form: CarData_RoadLinks_Statistics.field.

For now I will assume that everywhere you wrote JOIN_CarData_RoadLinks you really meant to reference fields in the CarData_RoadLinks_Statistics table. If that is true than the original calculation should have been written:

Dim CarOrientation

Dim RoadOrientation
RoadOrientation = [CarData_RoadLinks_Statistics.Orientation]

Dim RoadCompass
RoadCompass = [CarData_RoadLinks_Statistics.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 = [CarData_RoadLinks_Statistics.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

So which data source actually contains the fields named Orientation, CompassA and Bearing? Are these fields all in the CarData_RoadLinks_Statistics table? If not than any field in the calculation above that actually is found in the ITN_RoadLinks feature class should have the CarData_RoadLinks_Statistics prefix changed to the ITN_RoadLinks prefix.

===========

I am concerned that you said in one of your posts that ArcMap is crashing when you do this manually. Your data is local to your machine, which is good, so you don't need to consider network communications as a factor for the crash.

I have a parcel database with approximately the same number of records you mentioned and ArcMap crashes frequently when I do joins or relates to it as well, usually during record selection or calculation through the join. This is probably because joins and relates are built in memory, and if memory resources hit a wall, ArcMap crashes. Some peoples seem to be able to work with even larger data sets, but the differences could be due to differences in the resources of the workstations processing the data.

The crash could also be due to the fact your data sources reside in separate geodatabases and the resources needed to deal with that are managed differently than resources used when both are within a single geodatabase. I would suggest manual tests in Desktop where the feature class and table are in the same geodatabase, just to see if that makes a difference.

One way to make sure the problem is with resource management only is to do all of your tests on a dummy data base that uses a small fraction of your actual data first before scaling it up to your full dataset. If errors trigger on the small scale data it is a user fault that needs to be resolved first, but if problems only arise when you attempt to go to a larger scale than it is likely a resource issue.

When does ArcMap crash? As soon as you attempt the join? That is a definite resource issue.

Does it crash only after beginning a field calculation? Did you try a simple calculation on the data first or have you only attempted a complex calculation on the joined data? You should try a field calculation after the join is created using a simple value like " " or 0 into a dummy field, just to see if ArcMap would let you do that. If you could not do that on your full dataset, than resource management is the issue. However, if you can get past these basic steps and crashes only occur when you do more complex calculations, then more small scale testing followed by large scale experimentation is required to determine what actually triggers the crash.

When crashes due to resource use come into play, instability is difficult to predict, since you really don't know what is happening in the background and what the trigger point for the crash is. It could indicate a memory leak on the part of ESRI in some cases. You may actually have to provide ESRI with your actual data to determine if the problem you are having is reproducible or resolvable if you get to a point where you know it should work and every step works with a much smaller subset of data.

Liam_

Hi,
Thank you again for your assistance, I am really grateful for your time and efforts in helping me on my project.

The earliest version of ArcGIS I have used was 9.3. When I was transferred to 10.1 and they had changed over from VBA to VBScript it ruined all my models and scripts! Still, they are useful to keep for reference

. I was not sure if the linear referencing tool was appropriate for a road network as I had only used it previously on a river project on the Norfolk Broads tracking the salt water that was brought in with each tide. However, the results from the analysis seem to be correct.

I have had a rummage through my project and collected the information you requested: -

1)

The first table, the target of the Join is the ITN_Network Geodatabase: -

Data Type:
File Geodatabase Feature Class
Database:
C:\Users\Liam\Desktop\ArcMapData\_FinalisedMethod\FinalMethod\FinalMethod.gdb
Feature Dataset:
ITN_Network
Feature Class:
ITN_RoadLinks
Feature Type:
Simple
Geometry Type:
Line
Coordinates have Z values:
No
Coordinates have measures:
No
Projected Coordinate System:
British_National_Grid

The second table is the CarData_RoadLinks_Statistics standalone table:-

Data Type:
Standalone Table
Table Name:
CarData_RoadLinks_Statistics
Table Type:
File Geodatabase Table
Database:
C:\Users\Liam\Desktop\ArcMapData\_FinalisedMethod\FinalMethod\CarDataProcessed.gdb
Has Object-ID Field:
Yes

2)

The joined field data types: -

Target Field:
[ITN_RoadLinks.RoadLinkTOID]

Type:
String
Allow NULL Values:
Yes
Length:
16

Although NULL values are allowed for this field, there are no records with NULL in this field.

Join Field:
[CarData_RoadLinks_Statistics.RoadLinkTOID]

Type:
String
Allow NULL Values:
Yes
Length:
16

Again, although NULL values are allowed for this field, there are no records with NULL in this field.

3)

The tables are joined with these settings: -

Target Table:
ITN_RoadLinks
Target Field:
RoadLinkTOID

Join Table:
CarData_RoadLinks_Statistics
Join Field:
RoadLinkTOID
Join Type:
Keep only matching records
Data Type:
Standalone Table
Database:
C:\Users\Liam\Desktop\ArcMapData\_FinalisedMethod\FinalMethod\CarDataProcessed.gdb

4)

ITN_RoadLinks

Number of records:
729,157

CarData_RoadLinks_Statistics

Number of records:
1,328

Example of the join with some data: -
Here

I am trying to copy the ToFromTotalStats & FromToTotalStats from CarData_RoadLinks_Statistics into CarDataToFromTOTAL & CarDataFromToTOTAL in ITN_RoadLinks. All of the fields are double data types and allow null values.

I agree with not using Excel spreadsheets as a direct data source, they are just not designed for that and have advised many people not to. Even in MS Access, they can cause trouble as they have no record (or row) level read/write locking or efficient data structure past about 5000 rows. I have made specialist bespoke relational databases in MS Access for work, that�??s possibly where I get my bad terminology from

. When I said an ordinary table, I meant a table without any spatial data i.e. like within an MS Access database. Sorry for causing the confusion.

It is only my intention to export to excel so I can perform statistical analysis for my results. This will be the last step in the project and Arc will not be required to then read anything resulting from the statistical analysis. I was intending to do the statistics within Arc, however I need the Route Network to be fixed in order to do so. That would have been the best solution, but I guess the productivity suite has trouble processing the whole of South-East England's roads 🙂

I think I have covered all of the data you requested in your post, please let me know if I have missed anything out.

Thanks again,

Liam.

RichardFairhurst

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.

RichardFairhurst

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.

Liam_

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_

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-different-gradients.-OS-ITN-.) 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: -

Performed linear directional mean on RoadLinks.
Add field to CarData to store positive/negative orientations
Joined the CarData to Roadlinks to copy across the Road TOIDs
Removed Join
Joined the CarData to Linear Directional Mean table
Calculated the orientation (+/-) of the car data based on bearings.
Ran summary statistics for each road cased on TOID and Orientation
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.

RichardFairhurst

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.

AfterCalculation.jpg

CalculationModel.jpg

BeforeCalculation.jpg

RichardFairhurst

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.

Liam_

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.

Liam_

Thanks again for your help, I will give your suggestions a whirl and report back.

RichardFairhurst

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.