Need Help With Survey123 Summary Report Syntax for Dates

587
3
Jump to solution
01-07-2021 11:43 AM
Labels (1)
KatieBaltzley1
New Contributor III

 I'm working on a summary report for pump station inspections.  I'm using a repeat tag to fill out the table which will insert a row for each pump station with it's summed inspection data.  I was able to get the syntax correct to sum the data for each question for each individual pump station.  But I'm struggling with how to show the most recent inspection date for each pump station.  

You'll see in my syntax I tried to use 'stats: max,REPDATE,InsDate' but that gives me the most recent inspection date of ALL the inspections.  I only want the most recent inspection date per pump station. I've tried shifting things around, but I keep getting errors and when I don't get an error I get the most recent date overall.

 

I inserted what I have so far for the report template table below.

$<$summary>

Station No.

Last Inspection Date

Pump #1 Run Hours

Pump #2 Run Hours

Total Run Hours

Bioxide Usage in/gals

Fuel Usage in/gals

Generator Run Hours

Electric Usage kWh

Water Usage gals

${#PumpStationLogs | stats:"count,FACILITYID;max,REPDATE,InsDate;sum,P1_RNHrs,SumP1;sum,P2_RNHrs,SumP2;sum,BIOXIDE,SumBiox;sum,GENERATOR,SumGen;sum, ELCTMTR_R18,Electric;sum, H2OMTR_R18,SumWater":"PSNAME": "" | where:"1=1" | orderByFields:"PSNAME ASC" }${PSNAME}

${InsDate | format:"DD/MM/YYY"}

${SumP1|round:2}

${SumP2|round:2}

${SumP1+SumP2}

${SumBiox| round:2}

 

${SumGen|round:2}

${Electric|round:2}

${SumWater|round:2}${/}

$</>

1 Solution

Accepted Solutions
BarbaraWebster1
Esri Contributor

Hi Katie,

Thanks for sending the XLS form. When I test it, it looks like the max,REPDATE,InsDate stats parameter does return the most recent date for each station number, but since the outStatisticsFieldName variable (InsDate) is automatically converted from a date format to a string, there isn't a good way to convert the value from the epoch date format to a human-readable date.

I am logging an enhancement for improving date question handling in the stats filter. If you submit this issue to Esri Support (https://support.esri.com), they will be able to provide an official enhancement number.

In the meantime, a temporary workaround might be to create a calculate field (with the bind::esri:fieldType set to esriFieldTypeInteger) that formats the date into a YYYYMMDD format. Then you could use the max statistic in the report to find the most recent date based on that field (see screen caps).

Thanks,
-Barbara

Pump_station_XLS.JPG

Pump_station_report_template.JPG

View solution in original post

0 Kudos
3 Replies
BarbaraWebster1
Esri Contributor

Hi Katie,

At first glance I'm not seeing any issues with the syntax here. The only thing I noticed is that the where filter should be before the stats filter, but I don't think that would cause the issue you're seeing. Would you be able to send your XLS form so I can test it a little more?

-Barbara

KatieBaltzley1
New Contributor III

Barbara,

I'll send you the XLS form in a direct message!

Thank you so much, I've really been struggling with this one, I'd like to have it wrapped up by Tuesday.

BarbaraWebster1
Esri Contributor

Hi Katie,

Thanks for sending the XLS form. When I test it, it looks like the max,REPDATE,InsDate stats parameter does return the most recent date for each station number, but since the outStatisticsFieldName variable (InsDate) is automatically converted from a date format to a string, there isn't a good way to convert the value from the epoch date format to a human-readable date.

I am logging an enhancement for improving date question handling in the stats filter. If you submit this issue to Esri Support (https://support.esri.com), they will be able to provide an official enhancement number.

In the meantime, a temporary workaround might be to create a calculate field (with the bind::esri:fieldType set to esriFieldTypeInteger) that formats the date into a YYYYMMDD format. Then you could use the max statistic in the report to find the most recent date based on that field (see screen caps).

Thanks,
-Barbara

Pump_station_XLS.JPG

Pump_station_report_template.JPG

View solution in original post

0 Kudos