Accessing related records in a Feature Report

[Kara_Shindle]

I know there is a post on this already that was marked as solved, but that option is not working and that is listed in the post comments here

I have set up a set of related tables and a feature service, and I am trying to access all of them in some fashion in a feature report.

I have tried multiple formats, and I have limited success running the feature report on the parent table.  However, I need to filter based on the child feature service, and that is where the feature reports do not access the related records.  It's like it is only going in one direction....

Any help would be appreciated!

Agencies (table, Parent 1:M)

  Programs (table, child to Agencies, parent to services, 1:M)

    Services (feature service with location geometry, child to programs)

The idea is that an agency can have multiple programs, and each program has multiple services with different locations and attributes associated with each.

I want to create a feature report that shows me the Programs with all of their associated services.  

However, I want to conditionally filter based on an attribute in the Services layer, called Categories.

Essentially, I want to create a report for each service category.  

Below is what is on my template:

${#HSPrograms}

${#Services}

Program: ${HSPrograms.PrName}           

Service: ${Service_Name}

${if Main_Phone}Contact: ${Main_Phone}${/}

 ${ServDescrp}

${/}${/}

[JamesTedrick]

As mentioned in the Additional Syntax section of the report query documentation, you can use a where clause in the repeat tag to limit the repeat records that are printed in a repeat section.

${#Services | where="Categories='Cat1'"} 

[Kara_Shindle]

Hi, @JamesTedrick  thank you for your response.

I've tried that and had several issues with the queries.  If I try and add the template on my services, it will tell me the related tables are undefined.  I also will then get an error that the field I am trying to use a where clause on does not support operation.

Perhaps you can tell me if my syntax is wrong?

I am trying to only show programs with services that are in category '3'.  I've built a table that shows the services listed under each program.  My template is attached.  I've been running this template on the services survey - should I be running it on one of the related tables instead?  The category field is an integer with a domain associated with it, which I thought was causing me some issues with the where clause and I was planning to test it out on a new string field.  

Edit: Error I receive when I try to use the where clause.

[JamesTedrick]

Hi @Kara_Shindle ,

The where clause should be applies at the repeat tag ${Services} , not an individual field - the where clause sub-selects features form the repeat.

[Kara_Shindle]

Thank you for your response - sorry for the delay in my reply.  I was able to apply it at the ${Services} repeat tag when I was running my report on a first child related table, but then all programs were still listed - most just blank because they didn't have a service with the matching category.  I attached the output.  I'd really love it it would then only display the programs who have related services within this category.  I tried running this template on my services related table (great-grandparent? related table) and it then says that the parent tables are undefined.  I updated my template and attached it as well.

[Kara_Shindle]

As an update, I did get the where clause to apply at the repeat tag ${Services} but I am still running into an issue where it is showing the parent records, just with no child records.  So I'll get every single parent record when I run the template, but only those that fit my conditional statement will populate with the child fields I need.  

Still working on this!