Getting and setting caught exceptions in the ImportWorkflowResult is deliberate and necessary for implementing a SAGA design pattern for handling transactions atomically. Changing the error messages and status codes is fine, but do not change the error handling to simply halt execution and propagate exceptions up the layers to be handled. This short-circuits the SAGA pattern and prevents compensating actions from being performed.

I understand. I tried having PendingObservationUnit::brapiRead call setProcessError and then return an empty list in the case where invalid ObsUnitIds are supplied, but that's resulting in a NPE exception at a later step in the workflow. I'll see if I can get the workflow to run to completion anyways, it may be that other steps in the workflow could handle null or empty input more gracefully.

After discussions with Nick, Dave, and Shawn, I've made several changes.

1. Switched to using ValidationErrors to build a detailed tabular error which the frontend will display to the user.
2. Created MissingValuesException as a way to efficiently pass a set of strings up the call chain.
3. Updated the error message to match Shawn's documentation, https://cornell.box.com/s/67i3q3d3jkw5zenxngqufm4ft0boi3mf.

Caught exceptions are handled in the middleware instead of the lower layers, so just pass the exception through PendingObservationUnit#brapiRead and move the ValidationError handler up to the middleware.

The first middleware step in the appendOverwrite workflow is AppendOverwriteIDValidation.java which currently checks for empty values or duplicated values in the ObsUnitID column, throwing an exception if any are found which appears as a banner message on the front end. Validating that every valid ObSUnitID value is also found in the BrAPI service should be done in this step. This involves simply moving this line from WorkflowInitialization.java to AppendOverwriteIDValidation.java, and placing the ValidationError handler here.

MissingValuesExcepton is a confusing name because there is already a check for missing ObsUnitID values being done, and this error type is not used for that case. The error type is really for the case where there isn't a BrAPI entity stored for the given id, so a different name like EntityNotFoundException would be appropriate. Also, instead of placing the class for this exception type at the root of the experiment package, experiment/model seems like a better place for it.

ExperimentUtilities#collateReferenceOUIds is the method currently called to both validate against missing or duplicated ids and collect all the ids if all rows pass validation. This method should be split into two methods, ValidateReferenceOUIdValues and collateUniqueOUIds. This way validation errors can be generated for missing or duplicated values and displayed in the preview table instead of just a banner message. Any remaining unique OU ids can then be collected and corresponding Obs Units initialized. if an EntityNotFoundException is thrown then the exception propagates up to the AppendOverwriteIDValidation middleware where validation errors are built and added to the others by calling ExperimentUtilities#addValidationErrorsForObsUnitsNotFound, which contains your handler logic.

There's a bug in ObservationUnitService#getObservationUnitsByDbId. This method is fetching the OUs for a collection of Deltabreed ids, not for a collection of BrAPI Service DbIds. This method needs to search the xrefs, not the dbIds, of the OUs returned from the service. The method should also be renamed to ObservationUnitService#getObservationUnitsById.

I spoke with @shawnyarnes about the error messaging for the case of missing or duplicated ObsUnitID values and the source of truth has been updated and should be used for the validation errors.

There are a couple of problems with setting all summary statistics counts to zero in ImportTableProcess here.
The edge case being addressed is where an experiment exists without a dataset defined (no observation variable, no observation data) and the user selects the append workflow but imports a sheet with no new data (still no obs vars and no data). There isn't a check in place for this edge case, and the action taken is not correct for this edge case. The experiment still has germplasm and study counts that should appear in the summary preview, but are incorrectly being displayed as zero everywhere.

The expected summary for this edge case should look like the image below, instead.

Each import row and each cell of observation data can be in one of many possible states that requires its own set of validation steps, pending object processing, and statistics calculation, and there is a ProcessedDataFactory for creating instances of each data class containing the specific methods for validation, processing, statistics, etc., so that there isn't a confusing morass of nested if-else blocks and switch cases in the main import table process method. Instead of tacking on a bit of code to ImportTableProcess#process that sets the statistics to empty values(which isn't correct), you'll need to create a new class appendoverwrite/factory/data/UndefinedDataset.java and update ProcessedDataFactory to produce beans of that type.

Then in the import process method just before looping over any observation variables defined add a check for this edge case where there are no observation variables and set processedData to a new instance of UndefinedDatasetBean using the factory.

Then have processedData call the same methods to update statistics as is used when processing rows with observation variables. It would be good to place these lines in a helper method updatePreviewStatistics.