Using Field Calculator to Cast Long and Short Data to Strings

1097
6
04-12-2017 04:42 AM
AdrienHafner
Occasional Contributor

I'm trying to use Field Calculator to concatenate a long string of information including several of the fields in the same table.  The fields are in text, long and short data format.  I've tried and tried to cast the long and short data to strings to calculate this and I keep getting an error about Line 1 not being in the proper format.  I've double-checked that all data that should be casted to a string is - if it's not below it's because it's already in a text data format.  Does anyone see anything I'm not seeing?  The script is below.

"2017 PUC XFRM I&M - CIRCUIT: "+ !CIRCUIT_NO!+", SITE #: "+ !SITENO!.str()+", XFRM TYPE: "+ !XFRM_TYPE!+", KVA: "+ !KVA!.str()+", PHASE: "+ !PHASE!+", HIGH VOLT "+ !HIGHVOLT!+", LOW VOLT "+ !LOWVOLT!+", CONNECTION "+ !Connection!.str()+".  LOCATION INFO: "+ !REV_Street!+","+ !REV_City!+", "+ !REV_State!+".   GEO CODE: "+ !WAMGeocode!+".  |FIELD INSPECTION SUMMARY: "+ !DefectNotes!+ !OperNotes!+ !SubNotes!+" | ISSUES: PUBLIC SAFETY "+ !PublicIssue!.str()+" | EMPLOYEE SAFETY "+ !EmployeeIssue!.str()+" | OIL LEAK "+ !OilIssue!.str()+" | OPERATIONAL "+ !OperationalIssue!.str()+" | DEGRADED EQUIPMENT "+ !DegradedIssue!.str()+"  Contact Asset Management for additional details."

Tags (2)
0 Kudos
6 Replies
DanPatterson_Retired
MVP Esteemed Contributor

To get you started

"2017 PUC XFRM I&M - CIRCUIT: {}, SITE #: {}, XFRM TYPE: {} etc".format(1,2,3)‍

Where I have substituted 1,2,3 in form your field names

it would probably be best to replace your !fieldname!.str() stuff with variables

args =  [!field1!, !field2!, !field3! etc] and then you use the format string as

"fluff {} more stuff {} to match the args {}".format(*args)
AdrienHafner
Occasional Contributor

Thanks, Dan.  I will try that.  Do you have any idea why my original script won't work though?  What about the syntax is wrong?  I'm just trying to understand.  

0 Kudos
XanderBakker
Esri Esteemed Contributor

Syntax of the formula seems to be OK. May be due to the data. Do you have any special characters in the text fields? If so, you are combining string and Unicode and that will yield errors. Could you share some records to validate?

The other thing I am wondering is if the output field contains enough "space" (width) to receive the combined data?

However, the most important question imho is ... why do you want to create this type of description in a single field?

AdrienHafner
Occasional Contributor

Thanks, Xander.  To give you a little more background, the steps in the workflow prior to getting to this point include a spatial join. In the spatial join, I have tried creating new text fields in the Field Map window and populating those with the number formats, which then calculates the field above properly.  I can't use that method though, because some of these fields are short data format because they're coded value domains and I need the descriptions to show up (not the coded values) and I'm just using the ModelBuilder Environment settings to accomplish this.  When you create a new field, that setting doesn't carry over, so I'm trying to use the original fields.  All of that being said, I feel like the error has something to do with the long and short data fields specifically and trying to cast them, as I didn't have the issue when all of the fields were in text data format.

Here's a sample output in the correct format:

2016 PUC XFRM I&M - CIRCUIT: D23857, SITE #: 41209 XFRM TYPE:  PAD, TE KVA/PHASE 75ABC HIGH VOLT 13.2/23kV LOW VOLT 120/208 CONNECTION WYE - WYE LOCATION INFO:  256 Main Street, Anytown, California, 12345. GEO CODE: 196.  |FIELD INSPECTION SUMMARY: LARGE RUST HOLES INSIDE; BARRIER BOLTS BROKEN<Null><Null>| ISSUES:  PUBLIC SAFETY No | EMPLOYEE SAFETY No |OIL LEAK No  |OPERATIONAL No |DEGRADED EQUIPMENT Yes  Contact Asset Management for additional details.

And to answer your question as to why this is wanted in a single field, it's because this data is being read by another program and needs to be in this format, and having it pull from a single field is easier than having to pull and concatenate from multiple fields in another software.

0 Kudos
XanderBakker
Esri Esteemed Contributor

Thanks for the explanation. Seems to me that using Python (instead of ModelBuilder) would be a more stable way to obtain the desired results. With Python you can obtain the domain as a dictionary and easily replace the coded values with the corresponding descriptions and include some more validation and error handling.

At the end of this document: https://community.esri.com/docs/DOC-1927 you can find some examples.

AdrienHafner
Occasional Contributor

Update:  I got everything to work properly using ModelBuilder (with some Python written into the Field Calculations).  The cause of the issue appears to be that rather than calling the .str() method on the variables, I needed to use the function str(*Variable*).  I ended up going back and removing all of the domains that existing prior to data import, creating new fields that were all in text data format, then calculating those fields using the Reclass() function to change the coded domain values to the description I was actually looking for (that's where I used the Python).  I also had to run the Reclass() function on my *Notes fields listed above, as they had <Null> values which prevented the final ExpectedOutput shown above in red from populating.  Tons of issues, but I learned a lot working through all of them.  Thank you all for your suggestions.