Auto Field Issues

Question

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

Matt_D · Answer

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

ddiesel · Answer

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

michelle_mathews · Answer

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