ModelBuilder SQL Expression Using Inline Variable %Value% for OD Cost Matrix

ShannonMurphy2 · ‎10-27-2020

Hello GeoNet Community,

I originally posted a related question (Does anyone have experience with OD Cost Matrix and Iterate in ModelBuilder?) back in May. I eventually figured out the problem, but since then we had our computers replaced and I lost the model and cannot figure out how I made it work last time. This is my model:

The problem is with the expression to select Unique ID for Origins.

The OUSEQID is the common field between the Origin and Destination. It is a string field, that is why I have quotations around the %Value%. This doesn't verify fully, but if I replace %Value% with a value of 56 it does work.

It feels to me at this point that the iterator is not feeding the value to this expression. Maybe I am off base, but that is my impression. Here are my model parameters:

Any ideas would be very appreciated. Thanks!

Drew Flater‌ - Do you know where else I can get help?

curtvprice · ‎08-11-2021

I was looking for unanswered questions and came across this. My advice is to use the Calculate Value tool to build the SQL expression:

"OUSEQID = '{}'".format('%Value%')

Then set the output to type SQL Expression and connect it to the Select Layer By Attribute tool. I would also connect the variable named Value to the Select Layer By Attribute tool as a precondition to make sure the Calculate Value tool runs first.

Preconditions can sometimes be the issue with iterators as you want to make sure all inputs are up to date for that iteration. You may be able to just connect Value to Select Layer By Attributes tool and that would solve your problem without the intermediate step, but I like the clarity and flexibility of using Calculate Value to build expressions.