Forgive me if this is covered elsewhere. And direct me to it please.

My DB has the following:

Client_ID

Shift_date

Start_Timestamp

End_timestamp

Elapsed_time

Notes

I currently have a script that loops through and sends an email with this data (seperate email per record. What I would like to do is send one email to each client with all data that is common to that client on that shift date. Such as:

Client_ID, on Shift_date we did the following:

1. From Start_Timestamp to End_timestamp

Elapsed_time

Notes

2. From Start_Timestamp to End_timestamp

Elapsed_time

Notes

3. From Start_Timestamp to End_timestamp

Elapsed_time

Notes

Thank you

I have been driving myself nuts with this and any help would be appreciated.

The way I would do this would be to create a looped script. Set up variables to store: 1) the active client_id, and 2) the ongoing text for the email. Then loop through and concatenate text until the client_id changes

The script (in pseudo-code) does this:

Find your records

Sort by Client_ID and then Start

Set $active_id = Client_ID

Set $active_email = client_email

Set $mailbody = ""

Set $ctr = 0

Start loop

if $active_id = Client_ID

set $ctr = $ctr + 1

set $mailbody = $mailbody & $ctr & ". From " Start_Timestamp & " to " & End_Timestamp & " Elapsed time: " & Elapsed_time & Notes

else

send mail[$active_email; "Subject"; $mailbody]

// Now reset the variables

set $ctr = 1

set $mailbody = $ctr & ". From " Start_Timestamp & " to " & End_Timestamp & " Elapsed time: " & Elapsed_time & Notes

set $active_id = Client_id

end if

end loop

I'm sure I'm overlooking something basic or obvious, but this should get you moving down the path.

David

Thanks for the help. I think I am getting the idea here,

After a shift I perform a find of all the activity from all the clients, sort by client_id and use the script to build each email before moving on to the next client id.

The closest I had come so far was to build a script using "GetNthRecord", I would start with the first record and have it build the email with GetNthRecord in the middle to build all the activity times. This worked but still required a sererate find for each client id and date of activity.

I will try it and see what I can make of it and post the results.