Combining Multiple Records From Found Set In One Email

[TurboJokke]

Hi!

I've made a database where whenever a product is finished, it's put into the database. I have a search script that can find all finished products for the current day.

What I need is a script that can find records based on the customer, and then combine text from those records into one single email and sendt it.

What I have so far is a script that finds all records, and sends one mail per finished product, but this can result in customers getting 10+ mails in a day. Is this at all possible?

Thanks!

[dansmith65]

Loop through the found set and "build" the e-mail in a variable.

[TurboJokke]

Thanks, that worked great! Now for another problem; my found records list contains a list of finished products, often with 4-5 customers with a couple of finished products each.

Is there some script logic that can build one separate variable for each customer? I want the script to loop through the found set, and build one variable for each customer, assigning the finished products to it.

[dansmith65]

There are some ways to make a variable variable, but they are not easy to work with, and I doubt it's necessary for what you are doing.

Are you trying to send an email to each customer containing all the finished products for that customer? If so, there are a few different ways you could do that...

Find finished products, set customerID to variable, use the variable to constrain found set to only that customer: you now have a list of finished products for one customer, which you can loop through and send an email to. That whole thing would have to run in a loop until no finished products are found.

- or -

Find finished products, sort by customerID, go to first record, set customerID to variable, start loop, start building email, go to next record, if the customerID for the current record matches the $customerID, add to email body, otherwise send the email, then start building the email for the next customer's records.

(I don't think I have this one down step-for-step, it's just the basic idea)

[TurboJokke]

There are some ways to make a variable variable, but they are not easy to work with, and I doubt it's necessary for what you are doing.

Are you trying to send an email to each customer containing all the finished products for that customer? If so, there are a few different ways you could do that...

Yes, that's what I'm trying to accomplish. Your ideas seem to work in theory, but I can't figure out in practice how to first find the complete list of finished products, save all the customerID's and then do a search for each customerID individually.

[comment]

I would suggest yet a third way: define a new relationship between Customers and (another occurrence of) Products as:

Customers::CustomerID = Products 2::CustomerID

AND

Customers::cToday = Products 2::FinishedDate

where cToday is an unstored calculation field =

Get (CurrentDate)

Then run a script like the following one:

Go to Layout [ Customers ] 

#

Set Error Capture [ On ] 

Enter Find Mode []

Set Field [ Products 2::CustomerID ; "*" ]

Perform Find []

#

If [ Get (FoundCount) ] 

Send Mail [ To: Customers::eMail; Message: List ( Products 2::ProductName ) ] 

End If 

This should be run once a day, at the end of the day - otherwise you will need a mechanism to mark the products that were already reported to the customer.