Recently I have faced the problem when I couldn’t generate any event reports. The report generator just freezes and stood just by showing the loading/processing spinner on the screen. The server side was unhappy as well. The DEs which I tried to get the report on them, have been linked to the optionSets.
Eventually, it was worked out, that if by the time of usage, you add, remove some elements from the optionSet, it becomes unordered. It can’t be seen externally, only if you have an access to the optionSet and optionValue table you will be able to see it.
So I have a request to the MAINTENANCE app builder, each time before to save the list of elements inside any optionSet, re-order them and save them automatically. Otherwise many users have no idea, what is going on.
And additionally, on this, I also would ask you to add an option for the UI, so the developer could choose the appearance type of the optionSet (radio button or list type).
Hopefully, @phil or @Gintare will pay attention to this proposal.
Thank you guys for the great job you do!
Regards, Ulanbek
How did you work out your problem? I have an option(female/male) attached to attribute gender and other options attached to data elements but when I try to get an event report by selecting the gender attribute and some data elements, it loads forever and I can not view data.
NB: excluding attribute and data elements with option sets attached to them, the event report loads and I can see the data
Actually, it started when I created an optionSet for my previous project, and adding new, deleting some optionValue elements. And I remember in one moment I even couldn’t add any element to that optionSet. So decided to look at DB table, how it works, and paid attention to that, if the order has been destroyed, DHIS2 starts to behave strangely.
As soon as I reorder manually the optionValue everything started to work as intended. Since that time I am always trying to keep an eye on. But it would be OK if you have few elements on your optionSet. What if you have options such as IDC-10? Some countries have their own version of IDC-10 and need to amend it. Therefore I came up with this proposal to the DHIS2 developers.
So just in case check it or recreate your optionSet.
I almost forgot about this issue and recently @dhuser remind me back. Thank you @dhuser!
Thank you for sharing your experience. I think recreating the optionSet will be good. As data has already been captured with these optionSets, won’t there be any problem if I delete and recreate the optionSets?
I would recommend to update the sort_order of an optionSet’s options to start from 1 until N (N meaning the amount of options of the optionSet):
UPDATE optionvalue SET sort_order = 1 WHERE optionvalueid = 2720 and optionsetid = 123;
UPDATE optionvalue SET sort_order = 2 WHERE optionvalueid = 2721 and optionsetid = 123;
UPDATE optionvalue SET sort_order = 3 WHERE optionvalueid = 2722 and optionsetid = 123;
You can get view the sort order per each optionSet with a query like:
SELECT os.uid AS optionset_uid,
os.name AS optionset_name,
ov.optionvalueid AS option_optionvalueid,
ov.sort_order AS option_sortorder
FROM optionset os
JOIN optionvalue ov ON os.optionsetid = ov.optionsetid
GROUP BY optionset_uid, optionset_name, option_sortorder, option_optionvalueid
ORDER BY optionset_name, option_sortorder;
Which you can inspect the actual sort order to build the UPDATE queries.
There are various jira issues related to options & sort orders:
I think there is an issue with import/export which needs to be reproduced in a new issue, referring also to this comment: [DHIS2-7287] - Jira – which matches to my experience in a recent 2.34 migration. After import/export the optionvalue were not sequential anymore. To detect this a bit earlier I added a check to this command-line tool of dhis2-pk data-integrity
