Category Options

Support - Assistance technique
1

Hi Community,

On which table are Category Options stored in the dhis2 database

2

Hi Sakhile (@1c699b409a68f19fba95),

Would you like to share more about the use case? Is getting this info necessary from the database or maybe there’s a better alternative such as using the API? If you could explain the use case further maybe we can find a solution.

However, to answer your question, I asked the AI chat tool in the dhis2 developer portal for a quick step by step way to get the tables that are related to Category Options. I tested them and they’re working fine, so here are the step by step including the SQL Query:

  1. Navigate to the Maintenance app in DHIS2.
  2. Click on “SQL Views” under the “Other” section.
  3. Click on the “Add new” button to create a new SQL View.
  4. Give your SQL View a name, such as “Category Table Names and Primary Columns”.
  5. In the SQL query section, enter the following:
SELECT 
    t.table_name, 
    c.column_name
FROM 
    information_schema.table_constraints AS t
JOIN 
    information_schema.key_column_usage AS c
ON 
    t.constraint_name = c.constraint_name
WHERE 
    t.constraint_type = 'PRIMARY KEY'
AND 
    t.table_name LIKE '%category%'
  1. Click on “Save” to save the SQL View.

The tool forgot to mention the following steps:
7. Click on ‘Sharing Settings’ in the option of the view you created, and make sure your user account has ‘can capture and view’ for data access.
8. Click on ‘Execute Query’ and then ‘Show SQL View’

Additionally, the AI mentioned that making changes to the database is sort of a ‘danger zone’ because it could corrupt the database so make sure not to make direct changes unless directed by an expert or that you’re well aware of the process.

Thank you! I still hope to hear more about the use case.

P.S.
The word ‘support’ in your greeting was updated to ‘community’ because this is what the CoP is about. Thank you for being part of this community. <3