Build a case store for a header/detail relationship - Data360_DQ+ - Latest

Basic example

1. Create an Internal Database type data store for detail records. This data store should have:
   • All the fields your detail records will need. You can create fields within the data store Fields tab by using Import Fields, or if you are creating the data store on the fly in an analysis, they will be created automatically in the Data Store Output node's properties panel.

     Alternatively, you can use a case output internal database data store from Assure DQ. In this case, Assure DQ will populate the data store.

   • A search screen, to integrate the data store into the case store. This search screen is what will be used to find detail records from the data store while working within the case store. You can create the search screen in the data store's Screens tab.
   • A set of identity fields, with Identity fields are unique set to True. You can configure this setting in the data store's Identity tab.
2. Build an analysis that pushes detail records to your internal database data store. Do not execute it yet.

   The analysis should be one that somehow finds detail records that need to be turned into cases.

3. Create the case store:
   1. Select the Details tab and choose the Case contains record(s) from Data Store(s) case type.
   2. Select the Fields tab and create the appropriate fields for the header records that will be pushed to the case store. It is recommended that you use the Import Fields feature.
   3. Select the Screens tab and create at least one Search Screen and one View/Edit Screen. The Search Screen will be used by case workers to search for header records (i.e. cases) within the case store. The View/Edit Screen defines how cases returned by search will be displayed.
4. Save and exit the case store.
5. Build a case maker analysis:

   

   • This analysis should associate header and detail records within a Co-Group node by creating a new column using the UUID() function.
   • It should then push header records to the case store and detail records to the internal database data store.
   • The UID from the Flattened Detail node should be mapped to the OwningSubObjectUID field in the internal database data store. This field will not exist in the internal database data store until you associate the data store with the case store in its workflow. You will need to save your case maker analysis, add a workflow containing an associated data store to your case store, and then return to your case maker analysis to make this mapping.
   • The UID from the Flattened Header node should be mapped to the CaseUID field in the case store. All case stores have a CaseUID field for this purpose.
   Note: If data from pre-existing cases is being loaded, a new UID should not be assigned and the update functionality in the data store and case store outputs should be used.
6. Return to the case store, and create a workflow:
   1. Select the Workflow tab.
   2. Create the workflow for cases in the case store:

      Drag a Data Store node onto the canvas. This will be used to orchestrate execution of the analysis that loads the case store with header records with the loading of the internal database detail record data store.

      

      To associate the case store with the detail record data store created in step 1, include a Data Store node in your workflow and select the detail record data store as the Associated Data Store within the node's properties.

      To orchestrate the case store with the loading of the detail record data store that has been associated with the case store, use the Data Store node's Analysis To Execute property. The selected analysis should be one that loads the case store with header records. Once the analysis is chosen as the Analysis To Execute, it will execute automatically whenever the Associated Data Store loads new data.

7. Return to the case maker analysis and map the UID field from your detail records to the OwningSubObjectUID field in the internal database Data Store Output node.
8. Execute the analysis that loads the detail record store.

At this point, you will have performed all steps necessary to associate a detail record data store with header records in a case store. You can now execute the analysis that loads the detail record data store. Once the detail record data store loads, execution of the analysis that loads the case store with header records will be triggered.

Advanced example - Checking if records already belong to a case

In the basic example shown above, a "Case Maker" analysis is shown assigning UIDs to new header and detail records that comprise cases. This basic example does not, however, include any logic to check whether incoming header records already have a CaseUID or incoming detail records are already associated to a case via an OwningSubObjectUID value. The following example shows one way that this logic can be implemented:

 

1) Assign new records to existing cases

In this first step, a Join is performed using a common field from each input source (we will call this common field "OrderId"). This Join allows us to associate header records from our "Header Data Store" and detail records from our "Detail Data Store".

To represent this relationship with a field value, we then create a new column called "NewOwningSubObjectUID" in the Join node using the following expression:

IF(.ISNULL(OwningSubObjectUID), OwningSubObjectUID, (IF(.ISNULL(CaseUID ), CaseUID)))

"OwningSubObjectUID" is a detail record field that holds the UID values of case header records that own detail records. This expression will therefore check to see whether a detail record has an "OwningSubObjectUID". If it does, it will keep and use that value for "NewOwningSubObjectUID". If it does not, it will check to see whether the header record has a "CaseUID". If the header record does have a "CaseUID", then it is a preexisting case and the detail record should use that "CaseUID" as the value of "NewOwningSubObjectUID". If, on the other hand, the header record does not have a "CaseUID", then it is a new case entirely, meaning it will need to be assigned a new "CaseUID" further on in the analysis and that the detail record "NewOwningSubObjectUID" field should not be assigned any value yet.

2) Perform filtering to separate preexisiting and new records

Two Filter nodes are used to separate preexisting header records from new header records that need a new "CaseUID".

The first (top) Filter node New Sales Order Filter uses the following expression to find preexisting header records that have new detail records that should be associated to them:

ISNULL(OwningObjectId) && (ISNULL(SysDeleted ) || (.SysDeleted))

This expression finds records that do not yet have a value for the "OwningObjectId" field, meaning new detail records that have been associated to header records via a "NewOwningSubObjectUID" value but that have not yet been part of the case store (otherwise they would have a non-null "OwningObjectId").

Additionally, we will know that these are new detail records because they have not been assigned a SysDeleted value yet or have a SysDeleted value of False.

The second (bottom) Filter node New Sales Order w/out Cases Filter uses the following expression to find entirely new header records that will need a new CaseUID:

ISNULL(NewOwningSubObjectUID) && ISNULL(OwningObjectId)

This expression finds detail records that have been associated to header records via a common OrderID but have not yet been assigned a value for "NewOwningSubObjectUID".

We know that any header and detail records that enter the filter are entirely new to the case store because the expression used to assign a value to "NewOwningSubObjectUID" returned null and also because "OwningObjectId" is null.

3) Assign new CaseUIDs to new cases

Once we have distinguished preexisting cases from new cases, we can find distinct records using the OrderId field in a Distinct node. Having identified distinct, new cases, we can then assign each case a new CaseUID by creating a new column called "NewCaseUID" and using the UUID() function to populate this field's values.

4) Assign a FinalOwningSubObjectUID

At this point we can once again perform a Join using the field that is common to both header and detail record data sets, OrderId. We can then create a new column using the following expression to create a "FinalOwningSubObjectUID" value for detail records:

IF(.ISNULL(NewOwningSubObjectUID), NewOwningSubObjectUID, NewCaseUID)

This expression assigns the "NewCaseUID" that was created in step 3 to new detail records that should be associated to new cases. It will also retain any non-null "NewOwningSubObjectUID" values that were created in step 1, assigning these values to detail records that are associated to preexisting cases.

5) Isolate detail record fields with a Select node

Now that all detail records have a "FinalOwningSubObjectUID" value, we can isolate all of our fields that belong to detail records so that we can eventually push them to our Data Store Output in step 8.

6) Isolate header record fields with a Group By node

We also need to isolate header record fields that should be pushed to our case store output. This is done by grouping by OrderId with a Group By node, and then creating a new column for each header record field.

Since we have used a Group By node, these header record fields must be populated with aggregation functions. However, since each OrderId group should only have one record within it, aggregation will effectively pass a single value. For example, to pass our "FinalOwningSubObjectUID" created in step 4, we can create a new column in the Group By node called "CaseUID", using the following aggregate expression:

MAX(FinalOwningSubObjectUID)

Similarly, we can use the MAX() function to generate values for each field that needs to be pushed to the case store output from our header records.

7) Map to outputs and set to update

Once we have isolated the detail fields that need to be pushed to our detail data store and the header fields that need to be pushed to our case store in steps 5 and 6, we can map to these fields in their respective output nodes.

Since some records may be preexisting, we also need to set our outputs to update on a key field, so that preexisting records are updated rather than added again and duplicated.