Let's assume the following: I have a very simple table (below), and I want to send a report(that has one input parameter), every time a new record is created.

Table:
Sequence - Autoincrement number
email - destination email
SerialNumber - this is the input parameter to report
DateSubmitted - date record created
DateSent - 'flag' which will be set when report is sent

My question is how do I write the database queries for both the 'Report input parameter' and the 'Destination Email address'? Let me explain more...

By default, CRD only polls the database every 4 minutes. What if 5 new records were inserted since the database was last polled? Does CRD do some magic to know that the first new record has 'xxxx' as a serial number, and gets sent to person 'y', and that the second new record has a serial number of '1234' and gets sent to person 'z'? Or do I have to use the sequence number in my input parameter and email queries? If so, how do I grab the sequence number?

The same question exists for when I write the SQL for the task that runs after the report is generated. Is it as simple as 'update tablename set DateSent = getdate()', or do I have to add a where clause, so I only update the first new record, and not all of them.

I've watched all the demos, but they don't address this. Please help...
Jim

I did the exact thing last week, so I can tell you how its done

The key is to use "event-based constants" to define the parameter value and the destination email. You can see a couple of fairly new demos on how event-based constants work at:

- http://www.christiansteven.com/crd/demos/event_based_schedule/Adavced/Advanced%20-%20Event%20Based%20Schedule%20If%20a%20Database%20Record%20Exists.htm

- http://www.christiansteven.com/crd/demos/event_based_schedule/Adavced/Advanced%20Event%20Based%20-%20If%20unread%20email%20is%20present%20then%20insert%20Database%20record.htm

Using event-based constants, if 1 record is found, CRD does some magic in the background and knows which input parameter and recipient should be used based on the data in the record that triggered the schedule. If 5 records are found, CRD pulls the information from each record, processes the first record and moves to the next record and so on. So you end up with 5 different reports to 5 different recipients. Which is what you want it to do.

It seems you are still in CRD 5 mode where you had to set up a custom task to mark the record so it doesn't get processed again. In CRD 6, when setting up the event, just select "New Records Only". CRD will know if a record has already been processed and only act on new records which have been inserted since the last successful poll. I am yet to work out how it knows. (Perhaps this too is by magic!)

If you really do want to update each record as it processes it then in your custom task you will need a where clause as follows:

update tablename set DateSent = getdate() where serialnumber = [Insert the event-based constant for the serial number here]

If you had 1 record, then CRD will run the report for the one record and then update the table for the one record. If you have 5 records, CRD will do the above process 5 times, once for each record that caused the schedule to fire.

I am sooo pleased that I've been able to answer something pon this forum at last! Let me know if it helps you.