Using Select Layer by Attributes and Calculate Field together in Model Builder

4737
11
Jump to solution
10-09-2013 02:11 PM
ThomasDark
New Contributor
Hey,

I'm making a model in Model Builder inside ArcGIS for Desktop 10 and running into the following problem:

I have a feature class that I want to select a portion of based on the entry in a specific field and then modify a different field in the same feature class for just the rows that were selected using Calculate Field.  I've made a model that uses the "Select Layer By Attributes" to select the rows I wish to modify, and implemented the "Calculate Field" tool immediately after the "Select Layer By Attributes" tool to modify another field within the feature class.

My problem is the "Calculate Field" tool modifies every row in the attribute table for the feature class instead of just the rows that were selected in the "Select Layer by Attribute" tool.  How can I make the "Calculate Field" tool work on just the selections from the preceding tool?

I have been trying to learn VB If-Then statements and Select-Case statements to try to eliminate the "Select Layer by Attributes" portion of the model and simply do it all in the "Calculate Field" tool but my VB skills are basic at best and every time I try I get an error saying "No records within table". 

I am using VB code in the "Code Block" window that is the following:

If [DDP_OID.Tim_Permission] = "Yes"

and in the "Expression" portion of the Calculate Field tool I have:

Int ( 2 )

Any help would be absolutely wonderful.

Thanks in advance,

Tom
[ATTACH=CONFIG]28207[/ATTACH]
0 Kudos
1 Solution

Accepted Solutions
RichardFairhurst
MVP Honored Contributor
The model you put in the screen shot does not have any select by attributes operation and I would want to see the model layout before I could help you.  I do selections based on Joins all the time and calculations since at least ArcGIS 8 (currently I am on ArcGIS 10.1 SP1) and they always have worked.  But joins are potentially tricky.  You cannot join a feature class or table as the source record set, only a layer or table view (different things, with the first being what you see in ArcCatalog and the second being what you see after adding Catalog items to an actual map in Desktop).

Your problem is most likely at the join level.  You have to back it up to the way you set up the join.  Are you sure the tables share common field values to join?  Examine the join configuration with us thoroughly first so we can see what you have done before continuing to work on the calculation or selection.  What are the join fields (names, types, value examples).  Not one character or digit can be different between the two table's field values for a pair of records to join and only identical field types can be joined.  If this is Excel, convert it to a real table and stop the madness (I detest using Excel as a database.  It just isn't a database and open to far too much wierdness).

What version of ArcGIS are you using?

View solution in original post

0 Kudos
11 Replies
RichardFairhurst
MVP Honored Contributor
Hey,

I'm making a model in Model Builder inside ArcGIS for Desktop 10 and running into the following problem:

I have a feature class that I want to select a portion of based on the entry in a specific field and then modify a different field in the same feature class for just the rows that were selected using Calculate Field.  I've made a model that uses the "Select Layer By Attributes" to select the rows I wish to modify, and implemented the "Calculate Field" tool immediately after the "Select Layer By Attributes" tool to modify another field within the feature class.

My problem is the "Calculate Field" tool modifies every row in the attribute table for the feature class instead of just the rows that were selected in the "Select Layer by Attribute" tool.  How can I make the "Calculate Field" tool work on just the selections from the preceding tool?

I have been trying to learn VB If-Then statements and Select-Case statements to try to eliminate the "Select Layer by Attributes" portion of the model and simply do it all in the "Calculate Field" tool but my VB skills are basic at best and every time I try I get an error saying "No records within table". 

I am using VB code in the "Code Block" window that is the following:

If [DDP_OID.Tim_Permission] = "Yes"

and in the "Expression" portion of the Calculate Field tool I have:

Int ( 2 )

Any help would be absolutely wonderful.

Thanks in advance,

Tom
[ATTACH=CONFIG]28207[/ATTACH]


The calculation is structured incorrectly for VB.  It needs to be like this:

Expression:  Output

Code Block (Optional):
If [DDP_OID.Tim_Permission] = "Yes" Then
  Output = 2
Else
  Output = [Parcels20120128_1.RuleID]
End If


This will only overwrite the Parcel20120128_1.RuleID with 2 when the value of DDP_OID.Tim_Permission is "Yes".  Otherwise it will just write the current value of Parcel20120128_1.RuleID into itself unchanged.  Then even if the records were not selected they will only be changed when the condition is met. 

You must always provide an output value for every record the calculation might process, even if all you want to do is leave the value of the calculated field unchanged for some of those records.
0 Kudos
ThomasDark
New Contributor
The calculation is structured incorrectly for VB.  It needs to be like this:

Expression:  Output

Code Block (Optional):
If [DDP_OID.Tim_Permission] = "Yes" Then
  Output = 2
Else
  Output = [Parcels20120128_1.RuleID]
End If


This will only overwrite the Parcel20120128_1.RuleID with 2 when the value of DDP_OID.Tim_Permission is "Yes".  Otherwise it will just write the current value of Parcel20120128_1.RuleID into itself unchanged.  Then even if the records were not selected they will only be changed when the condition is met. 

You must always provide an output value for every record the calculation might process, even if all you want to do is leave the value of the calculated field unchanged for some of those records.


So I changed the code in my "Calculate Field" tool to:

If [DDP_OID.Tim_Permission] = "Yes" Then Output = 2 Else Output = [Parcels_20130128_1.RuleID] End If


But I am still getting a "No Records in Table" reply when Model Builder completes running the model.  It is Warning 000405.  When I look up the warning code it says the warning can occur when using an empty feature class or working with a layer of table view containing an empty selection set.

I have verified that I do not have an empty feature class.  Earlier in the model I am using the "Add Join" tool to join an access database query to a feature class attribute table. 

Any idea why if I use the "Select Layer by Attribute" tool immediately before the "Calculate Field" tool the CF tool does it's calculation on all rows in the attribute table instead of just the rows selected by the "Select Layer by Attributes" tool?

I have since tried adding a "Make Feature Layer" tool between the "Add Join" tool results and the "Calculate Field" tool but it did not resolve anything for me...

HELP!
0 Kudos
ThomasDark
New Contributor
I just checked the Attribute Table for the feature class and found that the column I'm trying to run the If-Then on, [DDP_OID.Tim_Permission], is a String with a length of 255.  Can I use a % wildcard in the VB to allow it to find rows that have 252 blank spots and 3 characters?

Also the Parcels_20130128_1.RuleID field is a field controlling some representations so the Output needs to be an integer.  I'm going to change the code to
 Then Output = Int ( 2 ) 
and see what happens...
0 Kudos
ThomasDark
New Contributor
Still getting "Warning 000405: No Records within table"...

Also switched the = to a LIKE in the If-Then.  Still the same result though
0 Kudos
RichardFairhurst
MVP Honored Contributor
The model you put in the screen shot does not have any select by attributes operation and I would want to see the model layout before I could help you.  I do selections based on Joins all the time and calculations since at least ArcGIS 8 (currently I am on ArcGIS 10.1 SP1) and they always have worked.  But joins are potentially tricky.  You cannot join a feature class or table as the source record set, only a layer or table view (different things, with the first being what you see in ArcCatalog and the second being what you see after adding Catalog items to an actual map in Desktop).

Your problem is most likely at the join level.  You have to back it up to the way you set up the join.  Are you sure the tables share common field values to join?  Examine the join configuration with us thoroughly first so we can see what you have done before continuing to work on the calculation or selection.  What are the join fields (names, types, value examples).  Not one character or digit can be different between the two table's field values for a pair of records to join and only identical field types can be joined.  If this is Excel, convert it to a real table and stop the madness (I detest using Excel as a database.  It just isn't a database and open to far too much wierdness).

What version of ArcGIS are you using?
0 Kudos
ThomasDark
New Contributor
Ok. In the field that I'm doing the "Select Layer by Attributes" there are a significant amount of rows that are <null>.  I'm going to eliminate them from the feature class and see.  I will be posting later today with more screenshots and such.  Do you know if I can post actual feature classes, .mxd files, etc. on here or are those attachments not allowed?

thanks for your ongoing help.

td
0 Kudos
RichardFairhurst
MVP Honored Contributor
Ok. In the field that I'm doing the "Select Layer by Attributes" there are a significant amount of rows that are <null>.  I'm going to eliminate them from the feature class and see.  I will be posting later today with more screenshots and such.  Do you know if I can post actual feature classes, .mxd files, etc. on here or are those attachments not allowed?

thanks for your ongoing help.

td


Can you name the fields and field types that you used to create the join.  That is the starting point.  Then some sample values that should be identical in both tables.  A relate can test for record matches and difference as well (although don't try it with a huge table).  If the rows are Null in the source table verify that is true in the unjoined table, and not just in the join.  Joins are built in memory and sometimes do have strange behaviors if you run out of resources.
0 Kudos
ThomasDark
New Contributor
I got it to work.  What I did was modified the inputs used in the "Add Join".  I went through the layer to which the join table was to be joined and deleted any row that did not have a corresponding row in the table I was seeking to join.  This removed all the <null> values from the field that I was running the VB script If-Then statement on.  I removed the "Select Layer by Attributes" tool from the model and relied on the VB script to do the selection for me.  Once all the <null> rows were eliminated everything worked just fine and the "Calculate Field" tool only did the calculation as directed by the VB script.

Thanks again for all your help.  I'm not sure which one of your comments deserves to be the "answer", but I'm going to deem the one about the join and such as the answer because when I looked into that I resolved the issue.

Thanks again,

Tom
0 Kudos
ThomasDark
New Contributor
So it worked once, but now it's giving me the Warning again.  I"m going to get it but thought I'd update you.  If I need to I'll post Join info and we'll go through it.

td
0 Kudos