Product: PostOmatic and Post to General Ledger
Description: Exception of "Field FieldValue:mapping_cell_data:summarized" does not have a valid date value summarized"
Environment: All
Version: All


When a message that is similar to the follow occurs it can be the result of different issues.

Field FieldValue:mapping_cell_data:summarized" does not have a valid date value summarized

Ultimately the issue is because the value of a field is not a legitimate date and there is logic applied to format the value of the field to a specific date format.  For example, trying to format the value "baddate" to MM/dd/yyyy will result in this error as "baddate" is not a valid date value.

This often times happens when choosing to summarize and a date field is not part of the summarization.  Take the following example:  Suppose the following gifts are included in the output and you want to summarize on Batch and Account Number and a field where the values are different are given the value of "Summarized". 

BatchFundPost DateAccount NumberAmountConstituent
1113Unrestricted2/5/20231111-111150Sam SmithOnline giving
1113Unrestricted2/6/20231111-111175Josie WalesOnline giving

These two rows would be summarized together into the following single record:

BatchFundPost DateAccount NumberAmountConstituent
1111-1111125SummarizedOnline giving

How could that Post Date with a value of Summarized, due to being two different post dates, be formatted?  It is not a valid date.

So what do you do?

There are multiple options in this case for summarization:

1.  Use If then logic on the Post Date field in the output to change Post Date to a known date such as Current Date when the value is Summarized.

2.  Change the summarization to reflect the accurate post dates and add Post Date as a field to look at in the summarization

3.  Change the data entry process to ensure all dates are the same for how you wish to summarize

4.  A variation of #1 is to use a different post date that is derived from the original date, such as the last day of the month.  This can be calculated with If..then.. logic and would result in the example above both dates being 2/28/2023.  They would result in valid date when summarization occurs and the date formatting would function as expected.  Note - in order for this last option to work, this calculation of the last day of the month should be created in the PreProcessing area of the formula configuration and could result in needing to rebuild the formula from scratch if PreProcessing is not already being used.

If the error is occurring and summarization is not used in a formula, then this means the value being used in a field is not a valid date.  The If..then.. logic would need to be reviewed to ensure that it is accurately defined and the source data should be interrogated and any issues with invalid dates being added to the source data should be corrected.