Script to Find Earliest/Latest Date entry from a set of records

[Erik Nelson]

Hello!

I've been tasked with finding a way to coalesce reporting data in a custom app and am not quite sure how to surface the earliest and latest dates associated with a record.

The inputs are from a report generated in a Custom app. Essentially, 1 or more "zones" are selected and a stop and start time are entered. This spits out a report of all devices associated with the zone(s) and assigns the start and end date to them. The entries are coming from unique fields (long story) so it's possible to have not only a duplicate device but for each to have their own start and end times.

The outcome being requested is essentially to coalesce the data on each device, and assign to it the earliest start time and the latest end time for an exportable report. Example - Report gets generated based on Zone 1 and Zone 2. Device A appears in each zone, so on the report it displays twice; once each with it's respective Zones Start and End time. The coalesced report would spit the Device once with just the earliest start and latest end.

I'm using Flow designer for this and have already built out the initial lookup to return just the unique Device entries so I at least have a starting point for a "For each item", but can't seem to figure out getting the date entries. I'm presuming they'll be similar scripts and I'm assuming I'll need to do separate lookups, which is fine, just need a little help (and Now Assist isn't cutting it!)

Thanks,

Erik

[DrewW]

So when you say "would spit the Device once with just the earliest start and latest end" do you mean that the device will display in Zone 1 only with its earliest start and latest end.  Or that the device will display in Zone 1 with its earliest start and in Zone 2 with its latest end?  Something like

Device	Zone 1	Zone 2	Zone 3
DevA	Start: 1/1  End 1/4
OR this
DevA	Start: 1/1	End: 1/4

Also if this is what your data looks like for DevA

Zone	Start	End
1	1/10	1/12
2	1/11	1/14
3	1/4	1/10

what should be displayed and for what zone and why that zone?

[Erik Nelson]

Even easier. The Zone piece isn't even being used after generation of the original, non-coalesced report. So it really is just:

Device	Start	End
Device A	Earliest Start Date/Time	Latest End Date/Time

[Erik Nelson · Accepted solution]

Working with a coworker we found a way to accomplish this that will, at least for now, cover our needs. The actions break down as follows:

Trigger - Table entries created

1) Look up records in our "Device" table associated with the trigger record. This is scripted to look up and return the unique devices associated with our base report, which is generated by the trigger record.

2) For each unique device, we're setting Flow Variables and using a script to set the Earliest and Latest dates for each unique record

3) Those Flow variables then become the entries in the new coalesced report.