dhis2-devs team mailing list archive

[Timothy Harding <tharding@xxxxxxxxxxxxxx>]

Thanks Bob and Jason!

I had suspicions of a dichotomy in the DB surrounding the CatOptionCombos,
but the confirmation is extremely helpful, I appreciate the walkthrough Bob.

*What maybe should have happened was that the category edit handler code
should have removed (or attempted to remove) the redundant
categoryoptioncombos. If not it is maybe something of a bug - though as you
point out there might be some merit in keeping history,  I don't think that
is by design.  I suspect that if the categoryoptions themselves were
deleted this would trigger the deletion of the catoptcombos.  If they are
allowed to lie around, orphaned, then you can have this inconsistency.*

I will investigate this more and report back. During initial config we were
making updates to catcombos left and right, so some have *many* extras that
are no longer needed. I can't verify if they did or did not have test data
in them at the time of the change, (I suspect no) so that will take some
testing on my part to see how it works. Pruning will only go so far though,
as there might be older data associated with a catcombo. As Jason noted
this is frowned upon (making a change to a production level catcombo), the
system currently allows it, so it is bound to happen.

*Did you regenerate your category combos after making changes through the
maintenance functionality?*

Every time! :D

I was able to make a single statement that gives all the necessary
information to 'build & match' these catOptionCombos, then it is just a few
loops of whatever language is preferred:

https://apps.dhis2.org/demo/api/categoryCombos.xml?paging=false&fields=name,id,categoryOptionCombos[id,name,categoryOptions[name,id]],categories[name,id,categoryOptions[name,id]]

You could probably even back this up further, to the data element, or even
dataset level to get a snapshot of how to build your form, the API is
wonderfully flexible in this regard.

Thanks for walking me though this!




*Timothy Harding*
Sr. Systems Analyst, BAO Systems
+1 202-536-1541 | tharding@xxxxxxxxxxxxxx | http://www.baosystems.com | Skype:
hardingt@xxxxxxxxx | 2900 K Street, Suite 404, Washington D.C. 20007

On Tue, Nov 17, 2015 at 9:52 AM, Bob Jolliffe <bobjolliffe@xxxxxxxxx> wrote:

> It looks to me like a consequence of the fact that from a given
> categorycombo there are two ways to derive the underlying
> categoryoptioncombos.
>
> The "direct" way, which is almost certainly what the rest api call does,
> is to look to the relation between categorycombo and categoryoptioncombo
> (the categorycombo_optioncombos table in the database).  And it finds the
> 12 culprits you mention.
>
> The indirect way, which is probably being used by the section form, looks
> first for the categories in the categorycombo (it needs to do this to
> layout the form correctly) and then finds the categoryoptions within those
> categories.  These are then used to look up the categoryoptioncombo through
> the categoryoptioncombos_categoryoptions database table.  This is the route
> you then followed in your explanation.
>
> So starting from the same categorycombo you have two ways to get the
> categoryoptioncombos, each utilizing completely different database tables
> for the lookup (that is why in the past i have described this relationship
> as a lattice, rather than say a tree).  In "theory" they should arrive at
> the same result, but I think your example shows that there are cases when
> they don't.  Though in fact there is no theoretical reason that they should
> agree as their is no relationship between the two routes at the database
> level.
>
> My guess is the 12 catoptcombos were generated once upon a time.  Then 4
> of the categoryoptions were removed from the participating categories, but
> not deleted.  So following the first lookup route you get all 12.
> Following the second you get 8.
>
> What maybe should have happened was that the category edit handler code
> should have removed (or attempted to remove) the redundant
> categoryoptioncombos. If not it is maybe something of a bug - though as you
> point out there might be some merit in keeping history,  I don't think that
> is by design.  I suspect that if the categoryoptions themselves were
> deleted this would trigger the deletion of the catoptcombos.  If they are
> allowed to lie around, orphaned, then you can have this inconsistency.
>
> Bob
>
> PS.  At the risk of resurrecting a very old discussion ...
>
> Perhaps the underlying flaw/complication is that the same categoryoption
> can appear in multiple unrestricted categories.  This is what makes
> implementing consistency difficult, despite a bundle of java code trying to
> apply governance to the database.  There is a solution to that, but it
> requires some remodelling.
>
> On 17 November 2015 at 14:33, Jason Pickering <jason.p.pickering@xxxxxxxxx
> > wrote:
>
>> So, I guess the question in this case is why are the other ones not
>> showing up when they should? This is in fact not what should happen I
>> think, but a dev who is familiar with the section forms could answer why
>> this is the case. I can see no reason from the database perspective how
>> DHIS2 might be able to "know" what is current versus what is new.
>>
>>  Did you try a custom data entry form? Are the "old" ones still available?
>>
>> I am pretty certain it is not intentional, and if the catcombos are still
>> there, you can still enter/import data against them (which is likely not
>> intentional).
>>
>> Also, Did you regenerate your category combos after making changes
>> through the maintenance functionality?
>>
>> Regards,
>> Jason
>>
>>
>>
>>
>> On Tue, Nov 17, 2015 at 3:15 PM, Timothy Harding <tharding@xxxxxxxxxxxxxx
>> > wrote:
>>
>>> Thanks Jason,
>>>
>>> It is unfortunate there is no turnkey. I've added a few responses inline:
>>>
>>> * I think the design, as it is at the moment, is not how the category
>>> option combos are intended to be used, because you have overlapping options*
>>>
>>> Yup, this is an artifact of changing a catcombo, never will someone be
>>> reporting to all 12, just the 8 "current" ones.
>>>
>>> *there is no real way to know what is historical versus what is current*
>>>
>>> There *has* to be, because the data entry forms all adjust to the
>>> "current" after you've edited the catcombo. The older ones are there just
>>> in case someone has entered data towards them. If we check the data entry
>>> page for an element using the above catcombo, we find only the 'newest':
>>>
>>> Before:
>>> [image: Inline image 1]
>>>
>>> After:
>>> [image: Inline image 2]
>>>
>>> Instead of 12 CatOptCombos (the 8 "current" ones and 4 old ones) we only
>>> see the 8 current ones. If you look at the catcombo in the API, however, it
>>> will still show all 12. This makes sense, because there could be back data
>>> associated with the previous catoptioncombos.
>>>
>>> So the only way I can figure to do this with a purely API approach is to:
>>>
>>> 1. Ask a Cat Combo what Options are assigned to it's categories
>>>
>>> https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn.xml?fields=name,id,categories[name,categoryOptions]
>>> <categoryCombo name="Morbidity Cases" id="t3aNCvHsoSn">
>>>     <categories>
>>>         <category name="Morbidity Age">
>>>             <categoryOptions>
>>>                 <categoryOption id="FbLZS3ueWbQ" name="0-11m"
>>> created="2011-12-24T12:24:24.149+0000"
>>> lastUpdated="2015-01-08T10:15:06.710+0000"/>
>>>                 <categoryOption id="rEq3Hkd3XXH" name="12-59m"
>>> created="2011-12-24T12:24:24.149+0000"
>>> lastUpdated="2015-03-11T11:10:02.159+0000"/>
>>>                 <categoryOption id="dUm5jaCTPBb" name="5-14y"
>>> created="2011-12-24T12:24:24.149+0000"
>>> lastUpdated="2011-12-24T12:24:24.149+0000"/>
>>>                 <categoryOption id="ZZxYuoTCcDd" name="15y+"
>>> created="2011-12-24T12:24:24.149+0000"
>>> lastUpdated="2015-03-11T11:12:23.883+0000"/>
>>>             </categoryOptions>
>>>         </category>
>>>         <category name="Gender">
>>>             <categoryOptions>
>>>                 <categoryOption id="apsOixVZlf1" name="Female"
>>> code="FMLE" created="2011-12-24T12:24:24.149+0000"
>>> lastUpdated="2011-12-24T12:24:24.149+0000"/>
>>>                 <categoryOption id="jRbMi0aBjYn" name="Male" code="MLE"
>>> created="2011-12-24T12:24:24.149+0000"
>>> lastUpdated="2011-12-24T12:24:24.149+0000"/>
>>>             </categoryOptions>
>>>         </category>
>>>     </categories>
>>> </categoryCombo>
>>>
>>> 2. Multiply the categories together, Example: FbLZS3ueWbQ (0-11m) &
>>> apsOixVZlf1 (Female). If we do this down the line we will have 8 possibles.
>>>
>>> 3. Query the entire list of optionCombos ever associated with that
>>> Catcombo
>>>
>>> https://apps.dhis2.org/demo/api/categoryOptionCombos.xml?paging=false&fields=name,id,categoryCombo[name,id],categoryOptions[name,id]&filter=categoryCombo.id:eq:t3aNCvHsoSn
>>>
>>> 4. Parse through the results looking for an optionCombo with the two
>>> UIDs above
>>> [image: Inline image 2]
>>>
>>>
>>> 5. Once found, note the id, use for whatever you needed it for, repeat
>>> for all possible combinations.
>>>
>>> When you are done, you will have a list of 8 "current" CatOptionCombos
>>> vs, all 12 that the API initially reports. I don't know if the dataentry
>>> module has hooks into the database to run direct SQL, but if not, something
>>> very similar must be happening via the API and Java. *Sounds like a
>>> blue print opportunity to have a Boolean for "current" optionCombos to skip
>>> all 5 steps above and have the results immediately.* It would certainly
>>> be useful for all the apps that are being written to not have to do this
>>> process every time they want to pull down a data entry page.
>>>
>>> *Changing category combos after the fact usually leads to all sorts of
>>> problems*
>>>
>>> This is a normal use case during configuration work though. You might
>>> need to make many changes before you are happy with the final product.
>>> Should there be some kind of warning on the Category Combo creator warning
>>> not to edit existing combinations?
>>>
>>> I appreciate you talking this out with me, I was hoping to find some
>>> sort of "trick" to get to the end of the process quicker, but I guess once
>>> written once, with proper caching, it is only a few API calls.
>>>
>>>
>>>
>>> *Timothy Harding*
>>> Sr. Systems Analyst, BAO Systems
>>> +1 202-536-1541 | tharding@xxxxxxxxxxxxxx | http://www.baosystems.com | Skype:
>>> hardingt@xxxxxxxxx | 2900 K Street, Suite 404, Washington D.C. 20007
>>>
>>> On Mon, Nov 16, 2015 at 11:52 PM, Jason Pickering <
>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>
>>>> Hi Tim,
>>>>
>>>> I think the short answer to your question is there is no API to tell
>>>> you this directly. The database does not version category option combos, so
>>>> there is no real way to know what is historical versus what is current. In
>>>> your particular case, you might be able to leverage the "created" time
>>>> stamp, which differs between the two sets of dis-aggregations, but again,
>>>> you might have to make some assumptions there.
>>>>
>>>> I think the design, as it is at the moment, is not how the category
>>>> option combos are intended to be used, because you have overlapping
>>>> options, namely 12-59m contains "12-59m, Male" and "12-59m Female". If all
>>>> three of these are reported (which they should be from the data entry form
>>>> looking at the design), you will end up with duplicates in the analytics.
>>>> Category option combos should be exclusive, and not overlap.
>>>>
>>>> So, I think what should have been done in this case, is to create a
>>>> totally new category combo, and then change the data element's
>>>> disaggregation to the new combo. Changing category combos after the fact
>>>> usually leads to all sorts of problems, but creating new ones is pretty
>>>> easy. DHIS2 will not track this change, but you could implement a trigger
>>>> function on "dataelement" to audit when a category combo changes for a
>>>> given data element, to give you a historical view of the data element, and
>>>> how its disaggregations may have changed over time. But, there is no API in
>>>> DHIS2 which would do this for you.
>>>>
>>>> Regards,
>>>> Jason
>>>>
>>>>
>>>> On Mon, Nov 16, 2015 at 8:17 PM, Timothy Harding <
>>>> tharding@xxxxxxxxxxxxxx> wrote:
>>>>
>>>>> Hello DHIS2 Devs!
>>>>>
>>>>> API question for the API Gurus:
>>>>>
>>>>> During configuration on a recent project, maybe dis-aggregation groups
>>>>> were changed and updated after their Category Option Combos were initially
>>>>> generated. They do not have data associated with them, but I think that is
>>>>> beside the point for my question. Even if they did have data, I'm asking
>>>>> for a why to get the "current" CatOptCombos, rather than "every"
>>>>> CatOptCombo that has ever been assigned to this Catcombo.
>>>>>
>>>>> Using the demo server, say I change:
>>>>> Morbidity Cases
>>>>> to
>>>>> Morbidity Cases + Gender
>>>>>
>>>>> https://apps.dhis2.org/demo/api/categoryCombos/t3aNCvHsoSn
>>>>>
>>>>> The API response for this one will go from:
>>>>> <categoryOptionCombos>
>>>>>     <categoryOptionCombo id="S34ULMcHMca" code="COC_358963"
>>>>> created="2011-12-24T12:24:25.319+0000"
>>>>> lastUpdated="2011-12-24T12:24:25.319+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/S34ULMcHMca";>
>>>>>         <name>0-11m</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="jOkIbJVhECg" code="COC_358966"
>>>>> created="2011-12-24T12:24:25.319+0000"
>>>>> lastUpdated="2011-12-24T12:24:25.319+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/jOkIbJVhECg";>
>>>>>         <name>15y+</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="wHBMVthqIX4" code="COC_358964"
>>>>> created="2011-12-24T12:24:25.319+0000"
>>>>> lastUpdated="2011-12-24T12:24:25.319+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/wHBMVthqIX4";>
>>>>>         <name>12-59m</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="SdOUI2yT46H" code="COC_358965"
>>>>> created="2011-12-24T12:24:25.319+0000"
>>>>> lastUpdated="2011-12-24T12:24:25.319+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/SdOUI2yT46H";>
>>>>>         <name>5-14y</name>
>>>>>     </categoryOptionCombo>
>>>>> </categoryOptionCombos>
>>>>>
>>>>>
>>>>> To:
>>>>> <categoryOptionCombos>
>>>>>     <categoryOptionCombo id="Q1wnnh5N6xV"
>>>>> created="2015-11-16T19:07:02.228+0000"
>>>>> lastUpdated="2015-11-16T19:07:02.228+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/Q1wnnh5N6xV";>
>>>>>         <name>0-11m, Female</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="wHBMVthqIX4" code="COC_358964"
>>>>> created="2011-12-24T12:24:25.319+0000"
>>>>> lastUpdated="2011-12-24T12:24:25.319+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/wHBMVthqIX4";>
>>>>>         <name>*12-59m*</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="uTaBiwZwiPL"
>>>>> created="2015-11-16T19:07:02.230+0000"
>>>>> lastUpdated="2015-11-16T19:07:02.230+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/uTaBiwZwiPL";>
>>>>>         <name>0-11m, Male</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="Up9OdlykfWA"
>>>>> created="2015-11-16T19:07:02.233+0000"
>>>>> lastUpdated="2015-11-16T19:07:02.233+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/Up9OdlykfWA";>
>>>>>         <name>5-14y, Female</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="iKsHpNDzlwC"
>>>>> created="2015-11-16T19:07:02.237+0000"
>>>>> lastUpdated="2015-11-16T19:07:02.238+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/iKsHpNDzlwC";>
>>>>>         <name>15y+, Female</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="OvNfjvOJCoK"
>>>>> created="2015-11-16T19:07:02.231+0000"
>>>>> lastUpdated="2015-11-16T19:07:02.231+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/OvNfjvOJCoK";>
>>>>>         <name>12-59m, Female</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="wIZ6pgCauQZ"
>>>>> created="2015-11-16T19:07:02.232+0000"
>>>>> lastUpdated="2015-11-16T19:07:02.232+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/wIZ6pgCauQZ";>
>>>>>         <name>12-59m, Male</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="SdOUI2yT46H" code="COC_358965"
>>>>> created="2011-12-24T12:24:25.319+0000"
>>>>> lastUpdated="2011-12-24T12:24:25.319+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/SdOUI2yT46H";>
>>>>>         <name>*5-14y*</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="S34ULMcHMca" code="COC_358963"
>>>>> created="2011-12-24T12:24:25.319+0000"
>>>>> lastUpdated="2011-12-24T12:24:25.319+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/S34ULMcHMca";>
>>>>>         <name>*0-11m*</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="jOkIbJVhECg" code="COC_358966"
>>>>> created="2011-12-24T12:24:25.319+0000"
>>>>> lastUpdated="2011-12-24T12:24:25.319+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/jOkIbJVhECg";>
>>>>>         <name>*15y+*</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="YfbYe5fnM0M"
>>>>> created="2015-11-16T19:07:02.234+0000"
>>>>> lastUpdated="2015-11-16T19:07:02.235+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/YfbYe5fnM0M";>
>>>>>         <name>5-14y, Male</name>
>>>>>     </categoryOptionCombo>
>>>>>     <categoryOptionCombo id="wSlHZb6eO6B"
>>>>> created="2015-11-16T19:07:02.239+0000"
>>>>> lastUpdated="2015-11-16T19:07:02.239+0000" href="
>>>>> https://apps.dhis2.org/demo/api/categoryOptionCombos/wSlHZb6eO6B";>
>>>>>         <name>15y+, Male</name>
>>>>>     </categoryOptionCombo>
>>>>> </categoryOptionCombos>
>>>>>
>>>>>
>>>>>
>>>>> So *rightly*, it retains its older CatOptCombos, because of course
>>>>> there might be data associated with those in the datavalue table. So I'm
>>>>> not looking to prune these. Without writing something to crawl through the
>>>>> api to figure out the "current" state, is there an existing API call that
>>>>> can do this? I know there must be, or someone has done it in JAVA because
>>>>> the data entry forms all can tell the "current" dis-aggregations without
>>>>> issue. Thanks and let me know if I can clarify my ask any better.
>>>>>
>>>>> Cheers!
>>>>>
>>>>> *Timothy Harding*
>>>>> Sr. Systems Analyst, BAO Systems
>>>>> +1 202-536-1541 | tharding@xxxxxxxxxxxxxx | http://www.baosystems.com
>>>>> | Skype: hardingt@xxxxxxxxx | 2900 K Street, Suite 404, Washington
>>>>> D.C. 20007
>>>>>
>>>>> _______________________________________________
>>>>> Mailing list: https://launchpad.net/~dhis2-devs
>>>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> Jason P. Pickering
>>>> email: jason.p.pickering@xxxxxxxxx
>>>> tel:+46764147049
>>>>
>>>
>>>
>>
>>
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+46764147049
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>