• Final

    • TUH Entity data not available for querying

    Owned by Elizabeth Storrs, created
    Last updated: Mar 01, 2024
    3 min read

    Slate Instance

    TUH

    Requestor/Reporter

    Elizabeth Storrs

    Date

    11/15/2023

    Status

    cOMPLETE

    Bug Description

    The Research Activity Entity data is not available in configurable joins. The data exists in the table, and can be queried from a CJ base Research Activity, but that query cannot be successfully linked to any other table. The fields are available for joins from person-scoped queries, but return no data.

     

    Jira: https://tuftswork.atlassian.net/browse/SLAT-134

    Troubleshooting/Research

    ROOT CAUSE: Research Activity is a Person-scoped entity, but the widget is embedded on an application-scoped form.

    Detailed review of Entity tables, via SSMS:

    In SSMS, we can see that the dbo.entity table has rows for each row in every entity:

    In TUH, there are four entities, so there are 4 different entity GUIDs in the entity field above. Lookup entity table provides the names:

    Every response for every custom field is a row in the dbo.field table. To find the entity fields and their related data, it is easiest to use a wildcard.

    The entity.record is the ‘grouping’ of the data points into a single research activity; any missing fields are not included in the field count.

    The source for the Research Activity data other than direct widget entry is an import of 11 research activities in starter upload.xls (now filed in Cleanup/Research which happened in July, 2023. Those 11 records are being returned when I try to connect the current entity data to person or application, so luckily we have some things to compare.

    How does the entity table connect to the rest of the database? That is, what is entity.record?

    For the ‘broken’ entries, it is the application GUID:

    For the ‘connected’ entries, it is the person GUID:

    I also may have accidentally borked this as a Research Experiment because I added the Application to the entity itself yesterday:

     

    But now I can access the Person data through the app data for everyone who came in the widget.

     

    Joins from Research Activity to Person to Application only get the 11 test records:

    Resolution Steps:

    (2) re-built Research Activity Review - CJ query, linking to person through application.

    Relatedly, I removed the test data from July.

     

    Also relatedly, the previous Reader form ceased to work, but that was about permissions; see https://knowledge.technolutions.com/hc/en-us/community/posts/6395720055963-Sharing-a-workaround-for-a-quirk-RE-new-Workflow-Editor-and-Reader-Permission. I have set up the population-aware Reader permission for GSBS, and that currently seems to be working.