Querying for custom forms/fields from DB

Accessing custom fields/forms records directly at the DB level is not recommended, as the DB schemas can change without prior notice. This might cause any queries, or reports built around the same, to fail.

Introduction

Custom forms and field values are stored in dynamically created tables, so it can difficult to find and access them directly from the DB. However, you can follow the steps below to get the query access to the custom fields/forms records.

Step 1: Build the OpenSpecimen query

Build a query in OpenSpecimen, which contains all the required fields, including custom (form) fields. (Refer to the https://openspecimen.atlassian.net/wiki/x/B4AhAQ  page for more info)

Step 2: Save and run the query

Save the query

Go to ‘Actions’ → ‘Save’ → Enter ‘Title’ → Click ‘Save

Run the query

Go the ‘Queries’ page → Click on the saved query to run it. This will add the query to the query audit logs.

 

Step 3: Retrieve the backend SQL

Retrieve the SQL used by OpenSpecimen to display the results by following this wiki page: https://openspecimen.atlassian.net/wiki/x/GwDiGw

Step 4: Use the SQL directly on DB

Run the query directly on the DB.