Validating open related cases (no parent-child link) across multiple case types before allowing a case to close

General
, ,
1

I have a case type call it Order Case type with an optional “Close” case action. Before allowing the close, I need to verify there’s no open related work across 5 different case types that have no parent-child case relationship with the Order case:

- 4 “Request” case types (each a separate case type, e.g. Request-TypeA, Request-TypeB, Request-TypeC, Request-TypeD)

- 1 “Invoice” case type

Each of these stores a reference ID back to the Order (equivalent to a foreign key) inside a different nested embedded page, the property path is different in each case type (e.g. `.RequestADetails.OrderID`, `.RequestBDetails.OrderID`, `.InvoiceDetails.OrderID`, etc.), so there’s no single shared flat property I can filter on across all of them.

Constraints:

- Can’t add a schema/data model change (a common flattened property) because there are many in-flight cases already in these tables and no way to retrofit them.

- Also need to check for open assignments tied to any of those related cases, which could sit in either Assign-Worklist or Assign-WorkBasket.

- Need to surface a dynamic list to the UI showing exactly which related cases/tasks are still open, so the user knows what to resolve before they can close.

What I’m looking for:

best-practice pattern in Pega for this kind of cross-case-type “are there any open related records” check when:

1. There’s no case hierarchy to leverage

2. A Class Group / common ancestor property isn’t an option due to in-flight data

3. Performance is a concern running multiple Report Definitions per validation

Is running one Report Definition per case type (5 total) plus one unioned query against `Assign-` for assignments the standard approach here, or is there a cleaner OOTB pattern (e.g. Declare Index, Search rule, Data Page chaining) that avoids the multiple-RD fan-out…

2

Hi Swati,Given your use case & constraints, I would not consider “5 Report Definitions + assignment query” as the best long-term pattern. The key question is whether you are optimizing for, Correctness only (close action runs occasionally), Scalability and maintainability (high-volume solution)

What I would suggest is, Declare Index + single reporting surface. If you’re allowed to add a new index table (not modify existing work tables), a Declare Index is generally the optimal approach.
Create an index class such as:
Plain Text
MyCo-Data-RelatedWorkIndex
Columns: Plain Text, OrderID, RelatedCaseID, RelatedCaseClass, RelatedCaseStatus, HasOpenAssignment, AssignmentID, AssignmentType

Every Request/Invoice case maintains index rows. Then your Close validation becomes:
SQL
SELECT *
FROM RelatedWorkIndex
WHERE OrderID = ?
AND (
RelatedCaseStatus NOT IN (‘Resolved-*’)
OR HasOpenAssignment = true
)
Why I like it is, it has a Single RD, Single DB hit, UI list already available, Easy to show blocking items, Decouples Order from knowledge of all 5 case types. No need to query Assign-Worklist / Assign-WorkBasket separately at runtime
In-flight case concern You mentioned:
Can’t add a common property because of existing data
A Declare Index is different. You are not modifying the case schema. You are adding a projection/index table. The only question becomes:
Can you backfill index rows for existing cases? If historical cases are relevant, you’d need a one-time reindex/backfill. If that’s not feasible, then this approach may be ruled out.

I hope this helps.

3

I would recommend using external mapping on class definition to map a.b property reference (in all case types) into an orderid column.
For your use case, I don’t think you can avoid running report on 5 classes, but try using elastic search(use search data if available, checkbox in data access tab) in your reports, so your case reports will be much faster. And this requires indexing on all your work objects and hope this is already configured in your environment.