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.
Solved! Go to 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
Nicole,
You're a genius!
Thanks a lot!
Hi Mark,
Interesting solution, I'll try later.
Hi Nicole,
It'll be very helpful if you can provide the sample workflow for this.
Appreciate your support.
Regards,
Mahadev