My app has a situation where an "outreach discussion" form will sometimes
create a child case that ultimately is picked up in a clinic form that
records HIV test results. And sometimes that form will create a child case
that will ultimately be picked up in a different clinic form that will
record ARV treatments.
So, of course, when I export my forms, I have three different data sets.
We will later on want to do analysis that draws from all three of those
tables.
So our statistics person will want to do a "join" of those three tables to
merge their data -- so the HIV test data is fully linked to the data in the
"outreach discussion" form that created, etc, etc.
My question is this:
What is the best field in the form-export tables to use as a unique key
that will link the child case's form record to the parent case's form
record? In those complex-looking system-created fields, I had a sense that
one of those codes would uniquely point back to the form that created this
new case.
(As more background, we do create a fairly unique "UIC code" for each
client in our registration form, which is what we actually put into the
"name" field as it is the most unique thing we have and we do not collect
people's real names... and that UIC code is passed on to the child case
(along with other fields too.) But I can't trust that two different clients
actually give rise to the same UIC code. I am guessing that the system
generates a great unique code that will uniquely link the parent and child.)
In the current exports, the form points at the case and the child points at
the parent. So you should have the following relationships to work with:
create parent form id --> parent case id (this should be info caseid in
exports)
create child form id --> child case id (this should be info caseid in
exports)
child case id --> parent case id (this is under "parent cases" --> "case
id" in exports)
You can also get the creating form ID from the "case history" tab of the
case exports (it's labeled as "xform_id") and you want the one with
"action_type" = create which should be the first one listed for a
particular case id in the history.
Sorry this is so confusing! Seems like we should shore up our documentation
in this area.
Cory
···
On Fri, Jun 26, 2015 at 12:30 AM, Eric Stephan wrote:
Hello all --
My app has a situation where an "outreach discussion" form will sometimes
create a child case that ultimately is picked up in a clinic form that
records HIV test results. And sometimes that form will create a child case
that will ultimately be picked up in a different clinic form that will
record ARV treatments.
So, of course, when I export my forms, I have three different data sets.
We will later on want to do analysis that draws from all three of those
tables.
So our statistics person will want to do a "join" of those three tables to
merge their data -- so the HIV test data is fully linked to the data in the
"outreach discussion" form that created, etc, etc.
My question is this:
What is the best field in the form-export tables to use as a unique key
that will link the child case's form record to the parent case's form
record? In those complex-looking system-created fields, I had a sense that
one of those codes would uniquely point back to the form that created this
new case.
(As more background, we do create a fairly unique "UIC code" for each
client in our registration form, which is what we actually put into the
"name" field as it is the most unique thing we have and we do not collect
people's real names... and that UIC code is passed on to the child case
(along with other fields too.) But I can't trust that two different clients
actually give rise to the same UIC code. I am guessing that the system
generates a great unique code that will uniquely link the parent and child.)
Hi -- thanks! After digging in there a bit, I think I found what you were
talking about.
When I went to my form exports for the cases that were actually children, i
went to that button that reveals the extra "advanced and deleted" fields,
and in there I could find parent case ID ... and when I exported that, it
did indeed line up with the case ID of the parent ... cool!
A couple questions:
1-- On just ONE of my nine form exports, one called "A4", which reflects a
case that is indeed a child, for some strange reason, the parent stuff
doesn't appear in the same location as it did in my other child-case forms.
When editing the custom export, for some reason it sticks the parent case
case ID into a second little section called "Repeat: parents.parent." And
it's not good for me because this would cause that field to appear in a
second Excel tab... and even when I tried it, that second tab winds up
blank. Can you tell me why that one form won't treat the parent case ID in
the same way? For the record, that is a grand-child case, and also, that
form actually runs on a different APP than the form that normally handles
that case. But a not-so-different form export, which I call C1, isn't that
different, and it doesn't make that annoyuing "repeat:parents.parent"
section. Because of this, on just this one, I can't use it to link these
tables later on. Any ideas?
2-- any idea why it places these parent case ID fields into a line with a
big sign marked "deleted?" I never deleted it!
Hi -- thanks! After digging in there a bit, I think I found what you were
talking about.
When I went to my form exports for the cases that were actually children,
i went to that button that reveals the extra "advanced and deleted" fields,
and in there I could find parent case ID ... and when I exported that, it
did indeed line up with the case ID of the parent ... cool!
I wasn't actually suggesting this, but glad that it worked! The other way
to get a parent ID is from the child case export (so bypassing forms). This
does put the parent ID in a new tab which is annoying though.
A couple questions:
1-- On just ONE of my nine form exports, one called "A4", which reflects a
case that is indeed a child, for some strange reason, the parent stuff
doesn't appear in the same location as it did in my other child-case forms.
When editing the custom export, for some reason it sticks the parent case
case ID into a second little section called "Repeat: parents.parent." And
it's not good for me because this would cause that field to appear in a
second Excel tab... and even when I tried it, that second tab winds up
blank. Can you tell me why that one form won't treat the parent case ID in
the same way? For the record, that is a grand-child case, and also, that
form actually runs on a different APP than the form that normally handles
that case. But a not-so-different form export, which I call C1, isn't that
different, and it doesn't make that annoyuing "repeat:parents.parent"
section. Because of this, on just this one, I can't use it to link these
tables later on. Any ideas?
It's hard to say definitively without digging in closer, but this sounds
like somehow one form submission contained multiple parent references which
caused the export library to think it was a repeatable field (instead of
just a normal one). This is often a hard case to recover from. However it's
odd that you're also not seeing data there. If you report an issue linking
to the form/export in question someone on our support team could take a
closer look at it.
2-- any idea why it places these parent case ID fields into a line with a
big sign marked "deleted?" I never deleted it!
The export library tries to be smart about guessing whether a field is from
the app or not. It might be that we don't properly guess parent IDs. If you
make another issue for this one our dev team can see if it is indeed a bug
and follow up.
thanks,
Cory
···
>
> for the record, this is all about this project:
> https://www.commcarehq.org/a/epm-thai/data/excel_export_data/
>
> --
> You received this message because you are subscribed to the Google Groups
> "commcare-users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to commcare-users+unsubscribe@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
