How do I create a SQL query to download data element form names, short names, descriptions and UIDs per programme

Implementation - Implémentation
1

I’d like to import some new translations to our site (v2.30). To do this, I know I need to create a SQL query which gives me all the following:

  • DE short name
  • DE form name
  • DE description

per programme.

Also, if possible:
Programme stage names
Programme stage descriptions
Section names/descriptions
Category and Option names
Tracked entity attributes.

Would someone be able to help me out with the SQL query code, or point me to some guidance on how to do this (I’m not a developer so don’t really understand SQL coding!)?

Thanks!
Anna

2 Likes
2

Hope this helps:

select 
pg.name as program_name, 
de.shortname as dataelement_shortname,
de.name as dataelement_name, 
de.formname as dataelement_formname, 
de.description as dataelement_description,
pgs.name as stage_name, 
pgs.description as stage_description, 
pgss.name as section_name, 
pgss.description as section_description 
from programstagedataelement as pgsde inner join programstage as pgs on pgsde.programstageid=pgs.programstageid
inner join programstagesection as pgss on pgsde.programstageid=pgss.programstageid 
inner join dataelement as de on pgsde.dataelementid=de.dataelementid 
inner join program as pg on pgs.programid=pg.programid 
order by pg.name asc;

SQL View

2 Likes
3

This is amazing- thank you so much for such a quick response!

Would i have to do a separate SQL query for Categories and Options and for tracked entity attributes?

Anna

1 Like
4

And also how would I add in the UID for each element?

1 Like
5

For tracked entity attributes, you’ll do it separately. But for categories and category options you won’t need them as tracker dataelements don’t have category combination. It is default for both.

1 Like
6

for the uid

add de.uid as dataelement_uid on the select query

2 Likes
7

I’ve added the UID as you recommended so it now looks as I have pasted below.

However for some reason this doesn’t add the UID to the table when i run the query. Have I missed something out?

SELECT
pg.name as program_name,
de.shortname as dataelement_shortname,
de.name as dataelement_name,
de.formname as dataelement_formname,
de.description as dataelement_description,
de.uid as dataelement_uid,
pgs.name as stage_name,
pgs.description as stage_description,
pgss.name as section_name,
pgss.description as section_description
from programstagedataelement as pgsde INNER JOIN programstage as pgs ON pgsde.programstageid=pgs.programstageid
INNER JOIN programstagesection as pgss ON pgsde.programstageid=pgss.programstageid
INNER JOIN dataelement as de ON pgsde.dataelementid=de.dataelementid
INNER JOIN program as pg ON pgs.programid=pg.programid
order by pg.name asc;

1 Like
8

The query looks good. This might be an issue of caching, try creating a new sql view (no caching) and see the output.

2 Likes
9

Creating a new SQL view worked- thank you!

1 Like
10

Wondering if anyone has managed to order the Data Elements within a Program by the order in which they appear?

11

Update to this; i have been able to use the sort_order under programstagedataelement table to a small success, however when DEs are in a sectioned program, the sort_order isn’t accurate (seems to pull the basic form for the sort_order, regardless of if there is a section format which takes precedents when viewing/filling out the form). Seems like programstagesection, programstage, and programstagedataelement with dataelement should get me a workable solution, however I can’t seem to find a sort_order that actually gets the order of the DEs within the sections. @banga any quick ideas? Happy to do the legwork if you can send me up the right tree to bark at!