Generate feature report with most recent related record/max value

880
5
Jump to solution
02-06-2023 05:52 PM
JosephRhodes2
Occasional Contributor II

I have a feature layer representing electric utility structures, with a related table for inspections. I'm attempting to create a feature report with a record for each structure, along with information about the most recent inspection record in the related table as defined by a date field for the inspection date

I've successfully used the Stats method with max to get the most recent inspection date, but I can't figure out how to call that variable in a where clause to define the values for the other fields coming from the related table.

JosephRhodes2_1-1675733843106.png

Any ideas would be much appreciated.

0 Kudos
1 Solution

Accepted Solutions
BarbaraWebster1
Esri Regular Contributor

Hi @JosephRhodes2,

You could set the result record count for the inspection repeat to one and order the fields by the inspection date. Then you should still be able to add a where filter if needed. So something like this:

${#inspection_repeat| where:"1=1"|orderByFields:"inspection_date DESC" | resultRecordCount:1}

${inspector} ${defect} ${action}

${/}


- Barbara

View solution in original post

5 Replies
BarbaraWebster1
Esri Regular Contributor

Hi @JosephRhodes2,

You could set the result record count for the inspection repeat to one and order the fields by the inspection date. Then you should still be able to add a where filter if needed. So something like this:

${#inspection_repeat| where:"1=1"|orderByFields:"inspection_date DESC" | resultRecordCount:1}

${inspector} ${defect} ${action}

${/}


- Barbara

JosephRhodes2
Occasional Contributor II

Thanks so much Barbara! I was able to get that to work, but I had to pull the leftmost three columns (parent info) out of the table; otherwise, I got "Failed to parse ${FACILITYID} since FACILITYID cannot be found in the current parsing scope.".

So, I was able to do this:

JosephRhodes2_0-1675800908991.png

But I was unable to put it all in a line in a single table, like the example in my post. Is it possible to put the parent info and a single related record in a table row within a single table? I tried putting the open/close parent tags both inside the table and outside the table, and both gave the parsing scope error on the first attribute the template was attempting to pull from the parent table.

0 Kudos
BarbaraWebster1
Esri Regular Contributor

Unfortunately I believe there's a limitation that prevents parent and repeat records from being on the same line in a table when exporting multiple parent records, however you can get all the records in the same table by putting the repeat record on a line below the parent record.

Template:
BarbaraWebster1_0-1675802851768.png

Export:
BarbaraWebster1_1-1675802868672.png

 



JosephRhodes2
Occasional Contributor II

Thanks again Barbara. I think one of these approaches will meet our needs.

0 Kudos
BarbaraWebster1
Esri Regular Contributor

No problem, glad to help!

0 Kudos