Export Output of Collect Values Tool in Model Builder

[ShinoRah]

Hi There,

I am new to model builder, I have built a model which calculates mean and minimum distance between features. It also counts how many of those near features fall within a given 'buffer' around a feature.

The user can select upto 6 buffer (/radius) lengths.  The tool will run and the final result is an excel file.  The excel file includes how many features fell within that given buffer but does not include the buffer distances that the user manually input in the beginning. 

So for those forgetful users, I'd like to add a column in the excel file that shows the buffer length.  I have added an image to this post, which shows visually what I want, because I'm not sure how coherent this explanation is.

Hopefully there is someone out there who can help me.  I've tried the merge, union and calculate values tool to somehow bring it all together, but no luck.

Thanks in advance!!

[curtvprice]

I've tried the merge, union and calculate values tool to somehow bring it all together, but no luck.

I think Calculate Value is the tool you need to use.

Make the output for your merge (right before you export to Excel, renamed "OutputTable" below) a precondition to Calculate Value - then have your calculate value tool look something like this:

Expression:

AddRadiusField(r"%OutputTable%","%RadiusColumns%")


Code block:

import os
import arcpy
def RadiusTable(rc,tbl):
  arcpy.AddField(tbl,"RADIUS","FLOAT")
  rc = ";".split(rc)
  Rows = arcpy.UpdateCursor(tbl)
  k = 0
  for Row in Rows:
    Row.setValue("RADIUS",rc)
    k += 1
  del Row, Row
  return tbl

Data Type:

table

Then connect its output to your export to Excel tool.

[ShinoRah]

Expression:

AddRadiusField(r"%OutputTable%","%RadiusColumns%")

Hi Curtis,

Thank you for your response.  I have included another photo to show how I am trying to incorporate your great advice.

After reading up on how inline model variables and iterators work, I am thinking I need to change all the blue ovals ("Seach Radius") to "RadiusColumns" as defined in your expression.

But I am unable to duplicate this label since model builder only allows unique names.

Also, Following your suggestions would allow me to delete the 'collect values' tool at the top, yes?

Thanks again Curtis,



Shehin

[curtvprice]

After reading up on how inline model variables and iterators work, I am thinking I need to change all the blue ovals ("Seach Radius") to "RadiusColumns" as defined in your expression.

But I am unable to duplicate this label since model builder only allows unique names.

Also, Following your suggestions would allow me to delete the 'collect values' tool at the top, yes?

No, you need to set the Collect Values output (named RadiusColumn) as a precondition to the Calculate Value tool. It is read by Calculate Value as a ";" delimited string, hence the "split" to convert it into a list lf values. The Collect Values is not an iterator, it will just collect those values in one step.

The output of the Calculate value is a table, it should be the input for your Export Table To MS Excel tool.

The first input should be the table that comes out of your other processing (the one with the automatic name that I suggested renaming).  I'm assuming in my calc value code (you should check) that the table rows are coming out in the correct order to match the order of the values in RadiusColumns.

Thanks again Curtis,

Hope this helps!

[ShinoRah]

Expression:

AddRadiusField(r"%OutputTable%","%RadiusColumns%")


Code block:

Code:
import os
import arcpy
def RadiusTable(rc,tbl):
  arcpy.AddField(tbl,"RADIUS","FLOAT")
  rc = ";".split(rc)
  Rows = arcpy.UpdateCursor(tbl)
  k = 0
  for Row in Rows:
    Row.setValue("RADIUS",rc)
    k += 1
  del Row, Row
  return tbl

Data Type:

table

Hi again Curtis,

The code block you so graciously provided for me,

I'm just wondering if I need to define 'AddRadiusField' somewhere in the model?

Reason being, I get this error when trying to run:

ERROR 000539: Error running expression:
AddRadiusField(r"Z:\Scratch\1303\130114_temp1.dbf","%RadiusColumns%")
<type 'exceptions.NameError'>: name 'AddRadiusField' is not definedFailed to execute (Calculate Value).
Failed at Mon Mar 25 12:07:18 2013 (Elapsed Time: 8.00 seconds)
Failed to execute (130117).
Failed at Mon Mar 25 12:07:24 2013 (Elapsed Time: 5 minutes 30 seconds)

Thanks again!!

[curtvprice]

I get this error when trying to run:

ERROR 000539: Error running expression:
AddRadiusField(r"Z:\Scratch\1303\130114_temp1.dbf","%RadiusColumns%")
<type 'exceptions.NameError'>: name 'AddRadiusField' is not definedFailed to execute (Calculate Value).

I'm sorry, I made a mistake in my post - the function names need to match.

One more thing, those two inputs should be preconditions to the Calculate Value tool so you are sure they have been calculated before the Calculate Value tool runs.

Calculate Value parameters:

Expression:

RadiusTable(r"%OutputTable%","%RadiusColumns%")


Code block:

import os
import arcpy
def RadiusTable(rc,tbl):
  arcpy.AddField_management(tbl,"RADIUS","FLOAT")
  rc = ";".split(rc)
  Rows = arcpy.UpdateCursor(tbl)
  k = 0
  for Row in Rows:
    Row.setValue("RADIUS",rc)
    k += 1
  del Row, Row
  return tbl

Data Type:

Table

[ShinoRah]

Hi again Curtis,

Thanks for the corrected code,

I have a python specific error when I incorporate your suggestions, I am not at all familiar with python and so a little overwhelmed with this attached error message,

Perhaps you can point in me in the direction I would take to begin solving this error?

Thanks!

[curtvprice]

I had the parameters in the wrong order, sorry. The parameters are passed in the order they are listed.

Here's  my fix:

Calculate Value parameters:

Expression:

RadiusTable(r"%OutputTable%","%RadiusColumns%")


Code block:

import os
import arcpy
def RadiusTable(tbl,rc):
  arcpy.AddField_management(tbl,"RADIUS","FLOAT")
  rc = ";".split(rc)
  Rows = arcpy.UpdateCursor(tbl)
  k = 0
  for Row in Rows:
    Row.setValue("RADIUS",rc)
    k += 1
  del Row, Row
  return tbl

Data Type:

Table

[ShinoRah]

Hi Curtis,

Thank you for your fix, I apologizefor continuing to bother you,

I am getting an 'invalid parameters' error again.  I've tried to investigate what python is doing here but I'm not sure why this error is occuring.

I've attached the error and the model image once again.

Perhaps you could point me in the right direction?

Thanks again,

[curtvprice]

You need to use preconditions to control the order of processing. Your merge must run first before the Calculate Value so the "Output Table" dbf will have been created before the CV starts.

The output of your Calculate Value is a table -- which you then want to provide as input to your export to Excel function.

Hope this helps.

[ATTACH=CONFIG]23043[/ATTACH]