Auto Field Issues

[PeterAP]

I'm trying to create a fully automated process which transposes fields prior to an autofield. As such, I've had to use a Multi-Field formula on all fields to replace all nulls and blanks with null as below. However, the autofield still isn't picking up one of my fields as numeric. I can't select this manually as the data will change each time it is imported.

Has anybody experienced this before or can anybody help?

IF (Isempty([_CurrentField_]) OR ISNULL([_CurrentField_])) THEN NULL() ELSE [_CurrentField_] ENDIF

[FrederikE]

Most likely there is some sort of letter or anything else in that field that the Auto Field Tool doesn't like. Maybe try to clean it with the "Data Cleasing" Tool first.

Can't you just change the datatype within the "Multi-Field" Tool? This should work dynamically with new data. 

[michelle_mathews]

Adding onto @FrederikE 's response - you can use the data cleansing tool to rid your numeric fields of any letters. 

[PeterAP]

@FrederikE @michelle_mathews  The problem I would have with the above is I have a dynamically changing number and type of fields, so I won't want to strip out letters from all string fields - so I don't think I'll be able to use the datacleansing or multi-field tools in this case unless I can apply it to all fields... I think I'm stuck as I'm fairly confident there are no rogue letters in that field as the original input data recognised as a double.

[michelle_mathews]

@PeterAP , can you attach your workflow and some dummy data? I can see if there might be a workaround 

[ddiesel]

Hi @PeterAP!

There has got to be something non-numeric in your records to prevent the Auto Field tool from recognizing the field as numeric. Is the issue that it is then not passing to Auto Field since Auto Field only works on strings?

If you're not able to resolve, I can think of one possible workaround. You could use RegEx to identify your numeric vs. non-numeric fields and then force the data type with a Select tool (with Dynamic / Unknown option).

Let us know what you find out. I'm curious as to the resolution.

Thanks,
Deb

[Matt_D]

Hi @PeterAP if you're using the Multi-Field Formula Tool, do you have the field selected? Also ensure the "Change Output Type to" is not ticked.

If it's the same data from your previous post and you're taking about the Amount- Source and Target it works my end.

IF IsEmpty([_CurrentField_]) THEN Null() ELSE [_CurrentField_] ENDIF

If it's different data then please supply an example.

Matt