Check required fields for every row

[Rafa_B]

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.

[NicoleJohnson · Accepted solution]

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! :)

NJ

[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

[Rafa_B]

Nicole, 

You're a genius!

Thanks a lot!

[Rafa_B]

Hi Mark, 

Interesting solution, I'll try later. 

[Mahadeva]

Hi Nicole,

It'll be very helpful if you can provide the sample workflow for this.

Appreciate your support.

Regards,

Mahadev