Lease renewal workflow fails for real estate contracts due to null date values in calculated fields

ERP Workday
, , , , , , , ,
1

Our lease renewal workflow is erroring out when calculating renewal dates for real estate contracts. The issue occurs specifically when optional date fields in the lease agreement are null. The calculated field that determines the renewal notice date can’t handle null values properly.

We have custom workflow steps for lease renewals that worked fine in testing with complete data, but production leases often have optional fields left blank. When the calculated field encounters a null renewal option date, the entire workflow fails with “Invalid date value in calculated field” error.

<wd:Renewal_Notice_Date>
  <wd:Calculated_Value>NULL</wd:Calculated_Value>
  <wd:Error>Cannot calculate date from null input</wd:Error>
</wd:Renewal_Notice_Date>

This is blocking renewals for about 30% of our real estate portfolio. Any ideas on handling null dates in calculated field logic?

system 2

Here’s a comprehensive solution addressing all three focus areas:

Calculated Field Null Handling: Modify your renewal notice date calculated field to include explicit null checking with fallback logic:

<wd:Calculated_Field wd:Descriptor="Renewal_Notice_Date">
  <wd:Formula>
    IF(ISNULL(Renewal_Option_Date),
       DATE_ADD(Lease_End_Date, -90, "DAYS"),
       DATE_ADD(Renewal_Option_Date, -60, "DAYS"))
  </wd:Formula>
</wd:Calculated_Field>

This formula checks if Renewal_Option_Date is null. If it is, it calculates the notice date as 90 days before the lease end date (standard notice period). If the renewal option date exists, it uses that minus 60 days. This prevents the “Cannot calculate date from null input” error.

For more complex scenarios with multiple optional dates, nest your IF statements:

IF(ISNULL(Renewal_Option_Date),
   IF(ISNULL(Lease_End_Date), TODAY(), DATE_ADD(Lease_End_Date, -90, "DAYS")),
   DATE_ADD(Renewal_Option_Date, -60, "DAYS"))

Lease Renewal Workflow: Restructure your workflow to include validation steps before calculated field execution:

  1. Add a “Validate Lease Data” step as the first step in your renewal workflow
  2. This step should check for critical required fields and set a workflow variable indicating data completeness
  3. Add a conditional branch: if data is incomplete, route to manual review; if complete, proceed to automated calculation
  4. Place your calculated field execution after validation passes
  5. Add error handling steps that catch calculation failures and route to exception queue

The workflow sequence should be:

  • Trigger: 120 days before lease end date
  • Step 1: Validate required fields
  • Step 2: Branch based on validation result
  • Step 3a: If valid, execute calculated fields
  • Step 3b: If invalid, route to property manager for data completion
  • Step 4: Generate renewal notice
  • Step 5: Route for approval

Custom Workflow Steps: Enhance your custom steps to be more resilient:

  1. Add a custom validation step that checks not just for null values, but also for date logic validity (e.g., renewal option date should be before lease end date)
  2. Create a custom calculated field specifically for lease renewals that includes business rule logic
  3. Add a fallback step that handles calculation failures gracefully by routing to a work queue rather than erroring out
  4. Implement workflow notifications that alert lease administrators when optional fields are null, so they can be populated proactively

For your specific error, update the Renewal_Notice_Date calculated field immediately. Then modify your lease renewal workflow to add the validation step before calculation. This combination ensures that null values are handled programmatically while also giving users visibility into data quality issues that should be addressed.

This draft is based on general Workday knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

Calculated fields in Workday don’t automatically handle nulls gracefully - you need explicit null checking in your formula. Use ISNULL() or ISBLANK() functions to test the field before performing date calculations. If the optional date field is null, your formula should provide a default value or alternative calculation path.

4

We encountered this with our equipment leases. The solution was restructuring the calculated field formula to check for null values first, then branch to different calculation logic. For example, if the renewal option date is null, calculate based on the original lease end date plus a standard notice period instead. This way the workflow continues even when optional fields are blank.

patel_sage 5

Tested this on Workday 2023R2 and the ISNULL wrapper in the calculated field formula successfully prevented workflow failures when Renewal_Option_Date was unpopulated in our real estate contracts.

6

That makes sense. So I need to modify the calculated field formula to include conditional logic. But I’m also wondering if the custom workflow steps themselves need adjustment. Should the workflow validate data completeness before triggering the calculated field, or should all the null handling be in the formula itself?

7

Best practice is to handle null checking in both places. Add validation steps in your workflow that check for required data before proceeding to calculation steps. But also build defensive calculated field formulas that won’t crash if unexpected nulls get through. This layered approach is more robust than relying on a single point of validation. Think of workflow validation as preventing bad data from entering the process, and calculated field null handling as protecting against edge cases.

8

Are all your lease renewal workflows using the same calculated field, or do you have different formulas for different lease types? We found that having lease-type-specific calculated fields made null handling easier because each formula could account for the specific optional fields relevant to that lease type.

9

Don’t forget about the business logic implications. If a renewal option date is null, what should the renewal notice date actually be from a business perspective? The technical solution needs to align with your lease agreement terms. Maybe certain lease types shouldn’t auto-renew if optional dates are missing, and the workflow should route for manual review instead of calculating a default date.