Jump to content

Email with data from multiple records


Malagent
 Share

This topic is 5600 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

This topic is 5600 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.