Trying to write a report to email, where to start?

[joshw]

Hello all!

I work for a pool construction company and currently I have a date field set to automatically send a email anytime the date is changed. This either says that the event is happening, or if the date is removed, the event is not happening. Since we are a construction company, this can be changed multiple times during a week, if not in any given day. This means that sometimes my builders can receive 3 or 4 emails a day of the change. This isn't immediate need information, so I want to create a email that summarizes what they have as of that day.

I want to create a "Summary" or "compiled records" email. I want it to look up the builder and sales representative, and send them a summary email (around midnight each night) of all of their current jobs. This would be a script I'll have Filemaker Server run each night. The email will look something like this:

Hello,

Your current Digs:

Customer name - 07/07/2012

Customer name - 07/08/2012

Customer name - <<>>

Customer name - 07/16/2012

Thanks,

GC

I have a Whiteboard layout that has a portal, and each record on that Whiteboard contains that builders name, and then a portal that has that builders job sites (the relationship is to the customer table through the builder field).

We have several steps of the building process, so compiling this into one email, rather than 1 email per Customer is what I'm looking for. I've tried a couple of different scripts I found on google, a loop find script, and a specific find for each one, but none compiles the information properly.

Anyone have any thoughts? I've been googling this and found a couple scripts that I thought would work, but were not what I was looking for.

[eos]

Define an unstored calculation in the Job Sites tables like statusLine ( customerNameField & " - " & GetAsText ( dateField ) ).

Then write a script which first finds the relevant builders/reps, then loops through these found records and for each one uses something along the lines of

Set Variable [ $emailBody ; "Hello,¶your current digs:¶" & List ( JobSites::statusLine ) & "¶¶Thanks,¶CG" ]

to create the body of the e-mail and send it, using $emailBody as message parameter of the Send Mail step.

[joshw]

Cool. I will try putting that together and see what I can come up with. I'm pretty new with scripts, and it seems like simple scripts are never what I need, haha.