Hi Everyone,
I need some help with creating a Data Document to fetch orders that were imported as cancelled into the system. Here’s the scenario:
Scenario
Our business needs to identify orders that have been imported as cancelled in our system. Specifically, we need to find those orders where:
-
There is exactly one record in the OrderStatus entity for each order.
-
The status_id in that record must be set to “ORDER_CANCELLED”.
To achieve this, we need to fetch the status_id field twice from the OrderStatus entity:
-
One time to ensure that only one status record exists for each order.
-
Another time to ensure that the status is “ORDER_CANCELLED”.
Challenge
In the past, we used a View to accomplish this by selecting the status_id field multiple times with different conditions. However, now I’m trying to do the same thing using a Data Document.
In this View, I was able to use the count function to ensure there’s only one status record for each order, and the max function to filter for the cancelled status.
Now, I am facing difficulties trying to achieve the same result using a Data Document, starting from the OrderHeader entity. Since Data Documents don’t allow selecting the same field multiple times, I’m unsure how to replicate this logic effectively. The problem is that in a Data Document, I cannot select the same field (like status_id) multiple times with different conditions or functions.
Question
Has anyone worked on a similar use case where they needed to select the same field multiple times under different conditions in a Data Document? If so, how did you approach it? Any help would be greatly appreciated!