Check required fields for every row

Hi, I have 10 columns, 4 of which are mandatory.
Is there any way to validate for every row, these 4 columns do not come empty and indicate in a new column which one comes empty?

I have tried select (to check the 4 fields) and multi-field formula without success, this is the expression:

IF IsEmpty([_CurrentField_]) 
THEN [COMMENTS]==[COMMENTS]+[_CurrentFieldName_]
ELSE [_CurrentField_] ENDIF

I also tried whit iff:

IIF(IsEmpty([_CurrentField_]),[COMMENTS]==[COMMENTS]+[_CurrentFieldName_],[_CurrentField_])

Thanks for some guidance on this.

Transformation

Preparation

Accepted answers

NicoleJohnson

If I'm understanding correctly, you want to check each of 4 fields to see if it contains a value, then have a "COMMENTS" field that shows a list of any missing fields. If that is correct, then the following should do the trick?

1. Use Multi-Field tool to create a new column for each of the 4 required fields. The main issue with your original formula was likely the COMMENTS== portion. So your formula would now be as follows:

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

NOTE: Depending on how your fields are set up, you might need to use IF IsNull() rather than IF IsEmpty().

2. Add a RecordID.

3. Use Transpose tool to select the 4 new fields as your Data Fields, and all other fields as your Key Fields. This will put the 4 new fields into the same "Value" column.

4. Add a Summarize tool to Group By all fields except the Name & Value field, and then Concatenate the Value field (Concat) with a comma delimiter. This will take anything in the Value field and concatenate it together with a comma separating the fields if there is more than 1 missing. If no fields are missing, this concatenated field will be blank. You can rename this concatenated field to "COMMENTS" in the Summarize tool if you'd like, or rename it later with a Select.

Let me know if that's not quite the result you're looking for and we can tweak it further!

All comments

NicoleJohnson

1. Use Multi-Field tool to create a new column for each of the 4 required fields. The main issue with your original formula was likely the COMMENTS== portion. So your formula would now be as follows:

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

NOTE: Depending on how your fields are set up, you might need to use IF IsNull() rather than IF IsEmpty().

2. Add a RecordID.

3. Use Transpose tool to select the 4 new fields as your Data Fields, and all other fields as your Key Fields. This will put the 4 new fields into the same "Value" column.

Let me know if that's not quite the result you're looking for and we can tweak it further!

MarqueeCrew

If you want this in a single field, I'd go binary and create a Integee field, presence of data.

1111 = all data present
1110 = first 3 fields present
....
0001 = only last field
0000 = no fields.

Here's my logic assuming missing is null:

For each field create an IIF statement and the calculate the sum.

IIF(IsNull([field1),0,1)

Then sum the results in your single field.

PField1*1000 + PField2*100 + PField3*10 + PField4

There is redundancy to having the individual results, but it is nice having that final field for review.

Cheers,
Mark