How to mark child records as the last in a series, based on certain criteria?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'm looking for a way to mark certain records as the last in a series, but I can't figure out a way to do it using Max Summary or Reverse Lookups. Here's the architecture:

Parent: Services < Child: Time Cards

A given Personnel might file multiple Time Cards at a certain address (a lookup to the Services table) on a single day. That is, a Personnel might work on several Services at a single address on one day. I want to indicate which of these was the last one filed (could be the max Record ID#), for each Personnel, per day. Here's an example:

On Tuesday 11/1, Jason performs three services at 13 Main Street, and two services at 450 Central Avenue. He files 5 Time Cards, one for each of the services. I want to mark 2 of the time cards from that day, the last one from 13 Main St and the last from 450 Central Ave.

Note that a given Service record may have many such marked child Time Card records, since I only want to mark one per day per Personnel. Any ideas?

Photo of Jonathan Heuer

Jonathan Heuer

  • 400 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Could you live with selecting a particular day to put "Focus" on that day, and then seeing the max Record ID TimeCards for that day?
Photo of Jonathan Heuer

Jonathan Heuer

  • 400 Points 250 badge 2x thumb
I thought about that sort of approach Mark. Unfortunately not. I will need to see the marked Time Card records on subsequent days after they are filed.
Could you live with putting a single Employee in Focus to mark their last visit to a Site on all days on the last week or two?
Photo of Jonathan Heuer

Jonathan Heuer

  • 400 Points 250 badge 2x thumb
Well, I have multiple employees for home I need to do this, on a regular basis. I'm not sure what you're thinking Mark?
Well, this may be one situation where the only way to do this with one click is with script.

But without script, my suggestion is to make a table called Unique Employee Site Visits with a Text Field as the key field and call the Key field [Related Employee - Related Site - Date].  It will end up getting populated with the concatenation of the Related Employee and the Related Site and the Date.

On the  Details table of the Time Cards make a filed which calculates to [Related Employee - Related Site]  

List "-", ToText([Related Employee],ToText([Related Site], ToText(Date]))

Make a Summary Report of Timecards Summarizing on that field.

There is an option to copy the results ot another table, so Copy them to the Unique Employee Sites Date table.

Now you have a parent record that you can summarize your time cards to and you can identify which timecards are the Maximum record ID# for each Employee/ site date.  Then look that up down to the time cards, and flag the lucky time cards where the [Record ID#] equals the Maximum.

So that will work, but the hassle is that I don't see a way to automate copying that Summary Report to the Unique Employee Site Date Table, which make tis a bit hard to reduce this to a stupid simple clerical task.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,204 Points 20k badge 2x thumb
>Well, this may be one situation where the only way to do this with one click is with script.

This is ridiculously easy to do with script in a simple three step process:

1) gather the raw data using API_DoQuery and post-process the XML response into JSON
2) Use _.groupBy() and to group by {personnel, day} and map each group into the record with the greatest / lowest date, [Record ID#] etc within the group members
3) template the resulting JSON into HTML using Mustache or underscore's own _.template() method and place result on relevant page

In fact this general procedure solves a wide range of similar problems where you want to group results on some criteria and within each group list one of more records meting some other criteria. Easy Peasy.