Pull information based on a relationship

Hi everyone,

I have two tracker programs: the first program enrolls suspected TB cases and the second program registers contacts through a relationship. The problem I am facing is that I cannot view the data related suspected cases and their contacts in the event report.

I hope the community can help me find the way to pull this information by providing steps to take to achieve my desired results

Thanks in advance

1 Like

Hey there @Fernando - you are right, there is no way of showing this information across the relationship in the event report yet. There is ongoing work on this requirement, and we hope to allow what will be called “relationship analytics” in the future - but we do not know which version this will be released yet.

As of now you will have to make some kind of custom report or SQL view to achieve a line list where you can see data from the cases and contacts together.

There is one functionality that is available already that can get you some of the way. In program indicators there is a function called d2:relationshipCount - and this allows you to make a program indicator that counts the number of contacts linked to each case. This again allows you to build a line list with cases, and the number of contacts per case.

Markus

2 Likes

Hi @Markus,

Thank you for your explanation. The problem is that I have no experience in SQL scripts. Should I have to create a join SQL script to match the related fields in both programs like IDs?

I was trying to see how the d2:relationshipCount function works but I can not find any information about it.

Thanks again for your help.

Hi @Markus, I’m reviving this topic because I need something similar and the solution is not working, not knowing if it’s a bug or I’m doing something wrong. I used the

d2:relationshipCount
but it is counting all existing entities in that period. I’m sharing the same photos any help is appreciated.

Thanks in Advance

@fernando , “relationshipCount(relationshipTypeUid)” “produces the number of relationships of the given type that is connected to the enrollment or event. When no type is given, all types are counted.” Note: this is not yet supported in Android. See docs (d2) Functions to use in a program indicator expression or filter


@asacur

This is supposed to be working! Do you see any errors? See video (URL at time of exercise):

Thanks @Gassim, i managed to find where I was wrong, I had to pass the filter that I’m looking for the set equal to 1 or greater than zero!.

Filter: d2:relationshipCount('yourID') == 1

Expression: V{enrollment_count}


Analytic type : Enrollment

As always, thank you very much for your willingness to always be ready to help.

1 Like

Hi @asacur,

I’m glad you figured out! May I ask you why do you think the first one wasn’t working because according to the video instructions above we did exactly the same (except chose count instead of average as the aggregate type), right?

So that’d be >= 1? Also, the ID is supposed to be relationship type id, right? :+1:

So this will show all enrollments with relationship type ID, and you set it to ==1 because the filter needs to be valid. :grin: Thank you for sharing the info @Asacur!

I’m glad you figured out! May I ask you why do you think the first one wasn’t working because according to the video instructions above we did exactly the same (except chose count instead of average as the aggregate type), right?

Yes, i was not doing any kind of filter that is why. :frowning:

1 Like