I have a Constellation List View or Insight that displays records from ABC-DEF-Work.
For each Case ID in the list, I want to determine whether ABC-DEF-Data-NNN contains one or more rows with the same Case ID.
The requirement is only to display the result in the list, for example:
Matching record exists → icon, mark, or true
No matching record → blank or false
I do not need to display the related records themselves. I only need an indication of whether at least one related record exists.
I have tried the following approaches:
Query-type property (impossible)
I considered using a Query-type property, but it did not appear as an available field when configuring the List View, so I could not achieve the expected result.
Data Transform (no good)
I considered setting a specific property to true using a Data Transform when related data exists. However, I could not determine the appropriate timing to load and set this value, or how to keep it updated when the related Data records are updated.
Association rule (does not meet requirement)
I also tried joining ABC-DEF-Work and ABC-DEF-Data-NNN using an Association rule. However, when multiple matching Data records exist for the same Case ID, multiple rows are listed. This does not meet the requirement because I need only one row per Case ID with an indication of whether matching Data exists.
Is there a supported way to achieve this in a Constellation View or Insight?
Instead of evaluating this dynamically at runtime, we can store a flag directly on the Case record.
You can create a boolean property (e.g., HasRelatedData) on ABC-DEF-Work and expose it as a database column. Whenever a record in ABC-DEF-Data-NNN is saved or deleted, trigger a Declare Trigger rule to update the corresponding Work instance.
This keeps your Constellation List View or Insight completely out-of-the-box and performant, as you only need to add that scalar property as a column.
Thank you very much for your suggestion. I appreciate the idea of storing the flag directly on the Case.
One concern I have with this approach is that ABC-DEF-Data-NNN does not have a direct reference to a specific Work instance. Therefore, when a Data-NNN record is saved or deleted, it is not clear how we can determine which Case should have its HasRelatedData flag updated.
Would this mean that we would need to define an association from the Data-NNN side back to the Work class in order to identify the corresponding Case?
Could you please clarify how you envision handling this part?
Outside of a List View, I was able to achieve the required result using a Data Transform by setting a property based on the resultscount, for example setting it to true when the result count is greater than 0. The difficulty is achieving the same type of result in a List View.
I have got the solution. It requires appropriate setting and time passing (1-2 hours) to complete affection to List view fields update.
Step1.
Create query type property ( NotifyCountCase).
Data transform , just count caseID on the other table (custom notice table). The case id is single clue of the Data-NNN table (there is no refereced object key nor else).
These CaseCount and Mark field (property), were coming 1 hour later after I set up.
Anyway, I could get these on the list. I did not set any trigger but I could get the update data after touching other tab or page.
Thanks.
Yeah, it works. But, we are running an additional SQL query for each row to fetch the count. (That means if there are 10 rows, we have 10 SQL queries) For small amount of data, this works fine. If we have huge data, then this starts showing performance issues.
Yeah, exactly. The base List table will have more than 10,000 records, and the joined table will also have thousands of records. I need to inform the project owner about this performance concern.
Anyway, the functional limitation itself has been proven. Thanks.
