Include related records in Feature Report

[SFM_TravisBott]

I have a feature service with two related tables. I have surveys that reference all three elements separately (the parent feature service, the 1:1 table and the 1:M table). I would like to be able to run feature reports for one of the related tables, but also include attributes from the related parent record. 

Can this be done directly in the syntax of the feature report template, or does this need to start from data design? In this case my parent feature layer includes jurisdictions and their basic information, and the 1:1 table is a status record. They are linked through globalID, and I didn't think to include some of the basic data already contained in the primary. Can I pull that data through the report template, or should I redesign the table to include it?

[ZhifangWang]

Hi @SFM_TravisBott 

Would you mind sharing more examples or detailed designs of your layers/stables/surveys?

For example, you have 3 tables:

• Layer A: parent layer, survey A
• Layer B: child layer (A:B -> 1:1), survey B
• Layer C: another child layer (A:C -> 1:M), survey C

In a feature report for survey C, you want to reference the parent record in layer A when iterating records in layer C.

Does the above example look like what you want?

[SFM_TravisBott]

@ZhifangWang You have it correct!

There are two iterations that I would like, in descending order of importance, as the second I can (sort of) already accomplish. Using your examples:

1. A feature report based on Survey B (1:1 child), that accesses attributes from the parent. 
   1. In my case, the parent is a jurisdiction, and the child record is a long status survey that gets updated bit by bit as they move through an inspection process. When the process is complete, I'd like to be able to produce a written report (done mostly from the child record), but also bring in attributes from the parent record, like jurisdiction name, county, personnel responsible, etc, which are all contained in the parent record. This obviously can be accomplished if I just include those attributes in the child table as well, but that seems redundant. 
2. A feature report that groups multiple records from Survey C (1:M child), while accessing information from Survey A (parent)
   1. Survey C represents inspection records, and the goal here is to produce reports based on the parent (jurisdiction) that summarize the whole inspection history. In my case this is the easiest to do, as I did not use a GUID to link the records, rather just the name of the jurisdiction, in order to avoid a URL parameter in the mobile app. So I believe I could filter by jurisdiction in the report view on Survey123 and run the report. However, it would be great to be able to work this also as a URL parameter, so field staff could click on their jurisdiction in a map (i.e., the parent record), and produce a report for the whole jurisdiction that accesses both the parent and the collection of child records. 

Can we do it? Do we have the technology?

[ZhifangWang · Accepted solution]

Hi @SFM_TravisBott ,

If I understand correctly, this should be available in the feature report, as long as all layers are in the same service. Using the above example:

• When you iterating records in a child layer (the current data context is a feature in the child layer), you can reference any attribute/geometry of its parent feature by specifying the parent layer name. For example, 
  • ${#layerB}
    This is an attribute of layerB ${field1inLayerB}
    This is an attribute of its parent feature in layerA ${layerA.field1inLayerA}
    ${/}
• When you iterating records in the parent layer (the current data context is a feature in the parent layer), you can also access all child features by opening an inner iterations. For example:
  • ${#layerA}
    This is the feature which object ID=${objectid}
    Below are child features in layerC:
    ${#layerC} (this iteration is under the context of the current parent feature)
    child feature, objectid=${oidFieldInLayerC}
    ${/}
    ${/}

Please refer to the Repeats section in https://doc.arcgis.com/en/survey123/browser/analyze-results/featurereporttemplates.htm. 

[SFM_TravisBott]

@ZhifangWang Thank you!

This works perfectly, and I was quickly able to reference those parent records in a feature report. Seems very simple and makes plenty of sense now that I see it, so thank you for getting me there. 

[SFM_TravisBott]

@ZhifangWang - Returning to this after a little bit: Is this workflow still valid?

The documentation hasn't changed, but I've gone to implement this precisely as I had it before but to no avail. I am working with the first example... I am running a report from a 1:1 child record, and trying to reference an attribute in the parent. I cannot seem to access any record in the parent layer. 

${City} is definitely a valid field in the parent layer....

Please let me know if anything has changed on Esri's end that would invalidate this workflow. 

[Kara_Shindle]

Were you able to figure this out?  I'm running into this exact issue.  I've been able to find a partial workaround because I have the following scenario:

I created individual survey forms for each of my two tables & feature service in Survey123 Connect.

Agencies (parent)

  Programs (child to Agencies, 1:M)

    Services (child to Programs, 1:M) - this is the layer with location geometry, and the other two are tables

I cannot access the parent data using a feature report template run on the Services, it gives me the same error.  However, I was able to run a similar report on the Agencies, and access the programs and services.  It didn't work exactly, because I was unable to filter conditionally using any of the child features, only the parent.  

[SFM_TravisBott]

@Kara_Shindle I did get it figured out eventually. The response for this question is now deprecated, which I mentioned above (as an aside, Esri, you need to figure out how to get old answers off of here so people don't keep finding the wrong information over and over). 

I was able to get help from Esri staff at one point - it's a sort of complicated process that involves smuggling in a new xml file to change the survey's definition in order to get it to accept calling to the related features. if you give me some time I can try to rustle up where I have it, or you can contact @JamesTedrick who provided the script and guidance. 

[Kara_Shindle]

Yes please!  I'm looking for any help I can get.  Thank you for the rec, I will reach out to @JamesTedrick as well.

[SFM_TravisBott]

@Kara_Shindle Hopefully this work. The README are the notes I took after I did this (quite a while ago) and I've converted their python notebook (that I had adjusted a little) to a pdf so hopefully you can follow along.