dhis2-users team mailing list archive

[Ant Snyman <ant@xxxxxxxx>]

Hi Randy,
There is actually a constraint on the trackedentitydatavalue table and you
cannot create two entries with the same programstageinstance and
dataelementid. I notice that you are grouping on dataelement name, is it
possible that you could have two dataelements with the same name in your
dataelement table? I.e different dataelementids but the same name?
Regards
Ant

On 9 October 2015 at 10:22, Wilson, Randy <rwilson@xxxxxxx> wrote:

> Hi all,
>
> We have an big issue with several of Rwanda's event reporting systems that
> have been set up using Tracker/Individual records.  For some reason, users
> are able to create duplicate records in the system through normal data
> entry.  It seems to happen when they enter a value for an attribute and
> then a couple of seconds or milliseconds later they change that value.  The
> system then stores both records with last updated several milliseconds
> apart.
>
> This is a big issue because analytics fails to run when it finds these
> duplicates.
>
> Here is the query that finds the duplicates:
>
> select programstageinstanceid, dataelementname, count(*) as numrecs from
> (
> SELECT
>   program.name AS programname,
>   program.description,
>   programinstance.dateofincident,
>   programinstance.trackedentityinstanceid,
>   programinstance.status,
>   programstageinstance.programstageinstanceid,
>   programstageinstance.programinstanceid,
>   programstageinstance.programstageid,
>   trackedentitydatavalue.value,
>   dataelement.name AS dataelementname
> FROM
>   public.programstageinstance,
>   public.programinstance,
>   public.program,
>   public.trackedentitydatavalue,
>   public.dataelement
> WHERE
>   programstageinstance.programinstanceid =
> programinstance.programinstanceid AND
>   program.programid = programinstance.programid AND
>   trackedentitydatavalue.programstageinstanceid =
> programstageinstance.programstageinstanceid AND
>   dataelement.dataelementid = trackedentitydatavalue.dataelementid
>   ) as foo
>   group by programstageinstanceid, dataelementname
>   having count(*) >1
>   order by numrecs desc;
>
> Here is the typical output of the query when there are duplicate records:
>
> Programstageinstanceid, dataelementname,numrecs
> 777921;"TB Sample type_morning Sputum";2
> 776886;"TB Date of collection_sample";2
> 773132;"TB History of contact with TPB+ case";2
>
> Here is an example of the duplicates we see when we select the duplicated
> records from the trackedentitydatavalue table.
>
> prograstageinstanceid,
> dataelementid,value,lastupdated,providedelsewhere,user
> 773132;35105;"true";"2015-10-02 09:32:48.334";FALSE;"abibarimana"
> 773132;35105;"true";"2015-10-02 09:32:48.508";FALSE;"abibarimana"
> 776886;31879;"2015-07-17";"2015-10-07 16:25:26.341";FALSE;"mugeni"
> 776886;31879;"2015-07-14";"2015-10-07 16:25:26.546";FALSE;"mugeni"
> 777921;35106;"true";"2015-10-08 09:55:36.133";FALSE;"emufasha"
> 777921;35106;"true";"2015-10-08 09:55:36.278";FALSE;"emufasha"
>
> In order to clean up the trackedentitydatavalue table, I need to write
> these records to a temporary table, delete them
> from trackedentitydatavalue, then decide which of the duplicate pairs to
> delete before copying the correct ones back into trackedentitydatavalues.
>
> This error seems to happen almost daily when the systems are in full use.
>
> Is there a way we can put a constraint on the trackedentitydatavalue table
> so that it will not accept duplicate values for programstageinstanceid and
> dataelementid?  I also think there must be an error in the java code that
> should be updating the first saved record with the one entered by the user
> later rather than inserting a new record.
>
> This is a major bug because it causes analytics to fail and when it fails
> we cannot run any event reports to get data out of the system.
>
> --
> *Randy Wilson*
> *Team Leader: **Knowledge Management, Data Use and Research*
> Rwanda Health System Strengthening Activity
> Management Sciences for Health
> Rwanda-Kigali
> Direct: +250 788308835
> E-mail: rwilson@xxxxxxx
> Skype: wilsonrandy_us
> <http://www.msh.org/>
> Stronger health systems. Greater health impact.
> <https://www.facebook.com/ManagementSciencesForHealth>
> <https://twitter.com/MSHHealthImpact>
> <https://www.youtube.com/user/MSHHealthImpact>
> www.msh.org
>
> *This message and its attachments are confidential and solely for the
> intended recipients. If received in error, please delete them and notify
> the sender via reply e-mail immediately.*
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 

*Ant Snyman*

*Cell: 0824910449*

*Landline: 028 2713242*


Health Information Systems Program - SA

-- 


*This message and any attachments are subject to a disclaimer published at 
http://www.hisp.org/policies.html#comms_disclaimer 
<http://www.hisp.org/policies.html#comms_disclaimer>.  Please read the 
disclaimer before opening any attachment or taking any other action in 
terms of this electronic transmission.  If you cannot access the 
disclaimer, kindly send an email to disclaimer@xxxxxxxx 
<disclaimer@xxxxxxxx> and a copy will be provided to you. By replying to 
this e-mail or opening any attachment you agree to be bound by the 
provisions of the disclaimer.*